[dba-SQLServer] Monitoring concurrent users

I can barely remember what I did. It took me a couple of days to figure 
out about 2 years ago.

But what he might want to do is the simplest way is to count the number 
of concurrent usersSELECT COUNT (*) FROM SYSPROCESSES WHERE STATUS <> 
sleeping will do the trick. If this returns a value less than 14 (8 + 
6), then the delay is likely caused by other factorsnot the governor 
limit of 5 users.
as stated in the article. And just run as timer started process and log 
to a file

Ahh there was a bit missing I found the ADP, that I may have used to 
test with. It is easier to write SP's in an ADP essentially just an 
Access query


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
-- Execute the command, putting the results in the table
INSERT INTO DBCCtracestatus(maint)
EXEC master..xp_cmdshell 'OSQL -E -Q " DBCC CONCURRENCYVIOLATION WITH 
NO_INFOMSGS"'
/*
You can use xp_cmdshell to start a osql session to which you supply the 
DBCC page command. xp_cmdshell always returns rows.

EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
INSERT INTO #maint(Results) EXEC(master..xp_cmdshell OSQL -E -Q dbcc 
checkdb(master))
*/
-- Display the results
SELECT *
FROM DBCCtracestatus
GO
/* SET NOCOUNT ON */
RETURN


Jim Lawrence wrote:

>Marty; that is simply brilliant.
>
>Jim
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
>MartyConnelly
>Sent: Wednesday, October 19, 2005 12:22 PM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] Monitoring concurrent users
>
>Maybe not what you want, here are some notes I made on checking MSDE for 
>throttling violations
>above 5, maybe a start.
>
>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 here or SQL BOL search on ( DBCC CONCURRENCYVIOLATION )
>
>Watch out below for yellow on white tips (kind of hard to read in IE 
>click on 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 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. Sorry about the late reply 
>tried tracking down the code
>
>John Bartow wrote:
>
>  
>
>>Hi all,
>>I'm looking for suggestions on monitoring concurrent users for a MSDE 2000
>>based application. Over the next few months I want to see what the average
>>and maximum number of users concurrently logged into the database are.
>>
>>John B.
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>>
>> 
>>
>>    
>>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada