[dba-SQLServer]MSDE User Throttle

Thanks for the contribution, Marty! I will start a suite based on your
code.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
MartyConnelly
Sent: Tuesday, December 23, 2003 9:45 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]MSDE User Throttle


You can write your own benchmark  ;).
You can track the amount of throttling going on through DBCC and logs I
once tracked this and kept a log in an access table.

There is more on url below or SQL BOL search on ( DBCC 
CONCURRENCYVIOLATION )

Watch out below for yellow on white tips (kind of hard to read in IE 
click on white area to see.

http://www.betav.com/Files/Content/Whitepapers/msde_files/msde.htm


To track the throttling you can use something like SP below with DBCC or
turn it on in the SQL log, it gives you the number of times you have
exceed the 5 connection limit. This dumps the following  records to 
database table.

Concurrency violations since 2003-01-21 12:20:43.747
1     2     3     4     5     6     7     8     9  10-100  >100
0     0     0     0     0     0     0     0     0       0     0
Concurrency violations will be written to the SQL Server error log.

ALTER PROCEDURE StoredProcedureDBCCworks
/*
(
 @parameter1 datatype = default value,
 @parameter2 datatype OUTPUT
)
*/
AS
SET NOCOUNT ON
-- Create the table to accept the results or use #tracestatus #indicates

temptable
If NOT EXISTS
(SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('DBCCtracestatus ') AND
sysstat & 0xf=3) DROP TABLE DBCCtracestatus BEGIN CREATE TABLE
DBCCtracestatus (
  maint varchar(4096),
  TraceFlag INT,
  Status INT
  )
END
I lifted parts of it I think from Betav.com( no longer there), but is 
pretty basic calls.
The only thing unusual is the select statement.
I just put it together for a client group that was interested; can't 
find my final code
I would rewrite but need a network to test on ; or run a dozen or so 
access mssql clients
from my machine to check. You can do this by hand starting the dbcctrace

to sql error logging
from OSQL then sifting through the error log. Susan Harkin wanted to 
write an  article on
this recently but I don't have a network handy to test on or all the 
final code.


Arthur Fuller wrote:

>Information on this topic seems to be particularly mushy. IIRC, MSDE is

>supposed to work exactly the same way as SQL 2K up to a certain number 
>of users, which I believe was specified as 5. I have personally seen it

>work just fine in test circumstances with 10 users. I recall reading a 
>post either here or maybe on one of the other SQL lists I belong to 
>that a developer had a working MSDE install with 50+ users, and no 
>performance degradation.
>
>Has anyone here got any real-world experience with going beyond the 
>alleged user limit in a production system, or even in a test system?
>
>Another question: how would ADO.NET affect the throttle? For those who 
>don't know, ADO.NET is a significant departure from standard ADO, 
>notably in its use of disconnected recordsets.... The implication being

>that your period of connection is very brief. So would 50 users using 
>ADO.NET cause the throttle to kick in?
>
>Does anyone know of any actual benchmark tests that demonstrate the 
>MSDE throttle's effect? I am no longer in a position to set up such a 
>test using 25-50 computers; would a test using 5 computers with several

>instances of some app or other (Access, QA, whatever) be valid?
>
>Arthur
>
>  
>
-- 
Marty Connelly
Victoria, B.C.
Canada



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com