Concurrent User limitation?

Hi folks -
I have an application that runs a SQL database. I am using the MSDE to
access it. Client machine has the proprietary software and MDAC v 2.8
installed on a Win2KPro workstation that is fully patched. The support team
for my proprietary application is telling me that using MSDE instead of SQL
Server will give me a concurrent user limit of around 10 or 11.
I've been running this application for almost a year and have not run into
this until I recently instituted Group Policy settings (although I don't see
where the changes to Group Policy would have a bearing).
Sometimes the workstations can access the application, other times within
the same day, they receive an error stating that "the server does not exist
or access is denied", yet they can restart and get right in.
Does anyone know if there IS a concurrent user limitation using MSDE to
access a SQL application?
Thanks.
Deb Landrum, AAM, AIT
Insurance Alliance, Houston TX
HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
On Tue, 6 Sep 2005 14:29:13 -0500, Deb Landrum wrote:
(snip)
>Does anyone know if there IS a concurrent user limitation using MSDE to
>access a SQL application?
Hi Deb,
Well - sort of.
There is no real limit to the concurrent user connections, but there is
a limit to the number of concurrent workloads. If a connected user
spends most time entering data on the form, and only a fraction of the
total time actually executing queries, you can serve quite a few users
without ever running into problems. On the other hand, one single user
can spawn multiple concurrent workloads at once (though it's not
trivial).
The limit on concurrent workloads is set to 8 workloads, but since SQL
Server needs 3 workloads for it's internal processes, there are 5 left
for the users. Once you exceed the workload limit, you won;t get any
errors, but the overall performance of all workloads will be reduced.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
Deb Landrum wrote:
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE
> to access it. Client machine has the proprietary software and MDAC v
> 2.8 installed on a Win2KPro workstation that is fully patched. The
> support team for my proprietary application is telling me that using
> MSDE instead of SQL Server will give me a concurrent user limit of
> around 10 or 11.
> I've been running this application for almost a year and have not run
> into this until I recently instituted Group Policy settings (although
> I don't see where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times
> within the same day, they receive an error stating that "the server
> does not exist or access is denied", yet they can restart and get
> right in.
> Does anyone know if there IS a concurrent user limitation using MSDE
> to access a SQL application?
there's no upper limit but 32767 connections to an MSDE instance but, a
built-in Workload Governor will kick in when 8 concurrent workloads of a
certain type are running... this will result in a linear delay applied to
all concurrent batches in the I/O activities.. when the number decreases
under the 8 limit, the governor will go sleeping again, waiting for next
"overflow" to kick in again... that does not mean 8 concurrent users is the
maximum allowed without the governor to kick in, as usually lot of
connections will be "sleeping", just waiting for user intervention to do
something... Microsoft released a paper "estimating" a magic number of 25
concurrent users before the overhead will be noticeable to end users, but
this just is a "magic number"... it really depend on the application type,
access mode, activities against the data, data load and so on... as you may
understand, there's no actual documentation about this governor but the
papers as
http://msdn.microsoft.com/library/?u...asp?frame=true
...
but I think this is another story, as the exception you get is not related
to all this matter...
this error is a generic MDAC related exception, as you can see reading
http://support.microsoft.com/default...06&Product=sql ,
and probably depending on problem in your LAN...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
To add what Hugo and Andrea have commented on, there is a limitation at OS
level. You are running w2k Professional which is a workstation product.
Currently the workstation products have a limitation of 10 simultaneous
connections from other machines. In effect, on your platform, you are
limited to 10 connections to MSDE. This limitation is in place to ensure
that workstation products, which are not optimized to be application or
file share servers, are not used as servers.
If you install MSDE on a server product, this limitation goes away.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
> team
> for my proprietary application is telling me that using MSDE instead of
> SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
> see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
> exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||Thanks to all for your input - the MSDE is on a server product, and, the
resolution was actually an issue within the SQL application that we are
running. For whatever reason, although the server name was properly and
correctly shown for the workstations to access it, the problematic ones were
receiving the error. However, by substituting the IP address of the server
for the actual server name, those same workstations were once again able to
access the application.
I don't know if this would be a particular weakness in the proprietary
application that we're using or not, but, again, Thank You to all for
replying.
Deb
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
team
> for my proprietary application is telling me that using MSDE instead of
SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||hi Deb,
Deb Landrum wrote:
> Thanks to all for your input - the MSDE is on a server product, and,
> the resolution was actually an issue within the SQL application that
> we are running. For whatever reason, although the server name was
> properly and correctly shown for the workstations to access it, the
> problematic ones were receiving the error. However, by substituting
> the IP address of the server for the actual server name, those same
> workstations were once again able to access the application.
> I don't know if this would be a particular weakness in the proprietary
> application that we're using or not, but, again, Thank You to all for
> replying.
you probably have a Name resolution problem in your LAN... let your Net
admin gain his money :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Concurency: Oracle vs. MSSQLServer 2K

Hello Gurus,
Im currently reading a Software Engineering for Web Applications Course
which is published online at the MIT (Open CourseWare Site) and here it says
at least it suggests that concurrency behaviour is best in Oracle than in
MSSQLServer. Im SQL Programmer and essentially I love its ease of use and
Id like you guys to comment this transcript. Is it possible to get the same
results in SQLServer by fine tuning the DBMS?
"
Concurrency is Oracle's strongest suit relative to its commercial
competitors. In Oracle, readers never wait for writers and writers never
wait for readers. Suppose the publisher at a large site starts a query at
12:00 PM summarizing usage by user. Oracle might have to spend an hour
sifting through 200 GB of tracking data. The disk drives grind and one CPU
is completely used up until 1:30 PM. Further suppose that User #356712 comes
in at 12:30 PM and changes his email address, thus updating a row in the
users table. If the usage tracking query arrives at this row at 12:45 PM,
Oracle will notice that the row was last modified after the query started.
Under the "I" in ACID, Oracle is required to isolate the publisher from the
user's update. Oracle does this by reaching into the rollback segment and
producing data from user row #356712 as it was at 12:00 PM when the query
started. Here's the scenario in a table:
Time Publisher Public Web Application
12:00 PM Starts a 90-minute query summarizing usage for preceding
year --
12:30 PM usage summary continues to chug away User #356712 updates
email address from "joe@.foobar.com" to "joe@.yahoo.com"
12:45 PM usage summary arrives at User #356712; Oracle reaches into
rollback segment and pulls out "joe@.foobar.com" for the report, since that's
what the value was at 12:30 PM --
1:30 PM usage summary report completes --
How would this play out in Microsoft SQL Server? When you're reading, you
take read locks on the information that you're about to read. Nobody can
write until you release them. When you're writing, you take write locks on
the information that you're about to update. Nobody can read or write until
you release the locks. In the preceding example, User #356712 would submit
his request for the address change at 12:30 PM. The thread on the Web server
would be blocked waiting for the read locks to clear. How long would it
wait? A full hour with a spinning/waving "browser still receiving
information" icon in the upper right corner of the browser window. If you're
thoughtful, you can program around this locking architecture in SQL Server,
but most Internet service operators would rather just install Oracle than
train their programmers to think more carefully about concurrency.
"
--
Jorge Luzarraga C
"I can do it quick. I can do it cheap. I can do it well. Pick any two."SQL Server 2005 contains a feature called "snapshot isolation" that can help
you with this issue.
You really should evaluate the concurrency and data freshness needs of your
application to determine which model you want to use. I personally have
seen cases where the before image was not what the application really
needed. I've also seen cases where it was fine. So, please make sure you
pick what works for your application requirements.
Thanks,
Conor
"Jorge Luzarraga Castro" <jluzarraga@.fidens.cl> wrote in message
news:ubX1kzVFGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hello Gurus,
> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
> "
> Concurrency is Oracle's strongest suit relative to its commercial
> competitors. In Oracle, readers never wait for writers and writers never
> wait for readers. Suppose the publisher at a large site starts a query at
> 12:00 PM summarizing usage by user. Oracle might have to spend an hour
> sifting through 200 GB of tracking data. The disk drives grind and one CPU
> is completely used up until 1:30 PM. Further suppose that User #356712
> comes in at 12:30 PM and changes his email address, thus updating a row in
> the users table. If the usage tracking query arrives at this row at 12:45
> PM, Oracle will notice that the row was last modified after the query
> started. Under the "I" in ACID, Oracle is required to isolate the
> publisher from the user's update. Oracle does this by reaching into the
> rollback segment and producing data from user row #356712 as it was at
> 12:00 PM when the query started. Here's the scenario in a table:
> Time Publisher Public Web Application
> 12:00 PM Starts a 90-minute query summarizing usage for preceding
> year --
> 12:30 PM usage summary continues to chug away User #356712
> updates email address from "joe@.foobar.com" to "joe@.yahoo.com"
> 12:45 PM usage summary arrives at User #356712; Oracle reaches
> into rollback segment and pulls out "joe@.foobar.com" for the report, since
> that's what the value was at 12:30 PM --
> 1:30 PM usage summary report completes --
> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them. When you're writing, you take write locks on
> the information that you're about to update. Nobody can read or write
> until you release the locks. In the preceding example, User #356712 would
> submit his request for the address change at 12:30 PM. The thread on the
> Web server would be blocked waiting for the read locks to clear. How long
> would it wait? A full hour with a spinning/waving "browser still receiving
> information" icon in the upper right corner of the browser window. If
> you're thoughtful, you can program around this locking architecture in SQL
> Server, but most Internet service operators would rather just install
> Oracle than train their programmers to think more carefully about
> concurrency.
> "
> --
> Jorge Luzarraga C
> "I can do it quick. I can do it cheap. I can do it well. Pick any two."
>|||> Im currently reading a Software Engineering for Web Applications Course
> which is published online at the MIT (Open CourseWare Site) and here it
> says at least it suggests that concurrency behaviour is best in Oracle
> than in MSSQLServer. Im SQL Programmer and essentially I love its ease of
> use and Id like you guys to comment this transcript. Is it possible to
> get the same results in SQLServer by fine tuning the DBMS?
Well, Oracle and SQL Server both appear on this list three times.
http://www.tpc.org/tpcc/results/tpc...=5¤cyID=0
Yes, Oracle appears above Microsoft, but two notes:
(a) Oracle's most recent data is from April 2005
(b) Oracle' average cost is almost 19% higher than SQL Server (avg $7.31 vs.
$6.15), so yes, you can get a bit better performance (even if you believe
the data is valid after that much time) but it's going to cost you a lot
more up front to build the system.
DB2 is at the top of the list, so why does this article choose to only
compare Oracle and SQL Server? Wait, let me guess, it was written by
someone on Oracle's board, or Oracle has made huge investments in MIT's
labs, or...

> How would this play out in Microsoft SQL Server? When you're reading, you
> take read locks on the information that you're about to read. Nobody can
> write until you release them.
That's only with certain isolation levels. You can certainly do a lot of
reading without blocking anyone, either with dirty reads (a.k.a. NOLOCK) or
with the new snapshot isolation in 2005.

> but most Internet service operators would rather just install Oracle than
> train their programmers to think more carefully about concurrency.
Is there any evidence to back this up? How many service providers do you
know of, that provide Oracle as the back end and say, go ahead, build your
database and application around it? I've never heard of one.
There are plenty of ways to design a well-performing application, and while
database vendor choice is certainly a significant factor, it's not the only
one, and it's not the one you end up spending the most time on.
A

Concatenating Numbers/Converting Dates to Integers

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:

SET @.Macola = Cast(Datepart(yy,@.MacolaDate) as varchar) + Cast(Datepart(mm,@.MacolaDate) as varchar) + Cast(Datepart(dd,@.MacolaDate) as varchar)

However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.just to be sure
what is the type of your variable @.Macola

doeas it HAVE to be integer ?|||Well, I am working with a database design that I cannot modify and it stores the dates in an int column with the format 20040101 and any conversions to the 100,000 values in the table takes too long, so I want to convert my date to an integer.|||i'm not sure if this is what you want

but create this user define function

CREATE function dbo.Date(@.Date DateTime)
returns Varchar(10) as
begin
return(
Cast(Datepart(yy,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(mm,@.Date) as varchar)))+Cast(Datepart(mm,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(dd,@.Date) as varchar)))+Cast(Datepart(dd,@.Date) as varchar)
)
end

then you'll be able to get easily

set @.Macola=dbo.date('1/1/2004')
set @.Macola=dbo.date(@.MacolaDate)

the Replicate+Len functions adds the 0 if necessary|||Thank you!|||is it what you needed ('cause i'm going offline)|||Yes it is, I appreciate your help.|||Cross post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32771

Anyway, what does your base data look like

Since you say you want it to be 20040101, are you assuming that that's the int value?

Where do you get 1/1/2004 from?

Some ddl might help

I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields,

That part confused me...|||Sorry for any confusion. Let me try this again.

I need to pass StartDate and EndDate parameters to the query I use as the record-source for several reports.

My database stores dates as integers in the format 20040101, but I don't want my end users to have to enter dates in that integer format, also, I figured it would be easier to convert the integer dates to datetimes in the query so that I wouldn't have to make this conversion on every report, it would already be done for me.

I wrote a T-SQL scalar function that converts from the integer format to a datetime format, and I just used that 'fnIntToDate(doc_date)' as the column returned in the header for the 3 date columns I have. This function looks like this, and works well:

Convert(Datetime,Left(Right(@.MacolaDate,4),2) + '/' + Right(@.MacolaDate,2) + '/' + Left(@.MacolaDate,4))

I do my development on a database with 1,000 rows, but our production database has over 100,000, and this date conversion (along with a few other things I have since fixed) caused the query to time-out. And according the the query analyzer, I can save 3-4 seconds PER COLUMN if I just leave the integers as integers and convert my date parameters to integers instead.

So I'm asking for help on a function that does the opposite of what my function does. I want to pass it a datetime value and have it return a date in the format 20040101. I had gotten all of it except the leading 0s, but I think the previous poster answered that for me, so i'm goign to go try that now.

Thank you.|||And what he told me worked great :). I just converted it to an integer and I'm good to go.