1

PROBLEM

Running my script below i can see the number of logins as an accumulated number to my instance:

SELECT cntr_value AS [LoginsPerSec]
    FROM sys.dm_os_performance_counters
    WHERE 
    object_name = 'SQLServer:General Statistics'
    AND counter_name = 'Logins/sec'

Running it again 1 second later returns an increased value.

The difference between the two appears to indicate the increased number of logins within that period of time (second) between the two executions.

QUESTION1

How do I execute this twice within the same script to retrun the DIFF between the two values ?

Simply need the DIFF value only as the result therefore allowing me to plot the value on a live chart.

Currently doing this for active connections using:

SELECT count(dbid) as NoOfConnections
FROM sys.sysprocesses
WHERE dbid = 5
GROUP BY dbid

QUESTION2

Can you recommend any other performance counters visable via sys.dm_os_performance_counters that would allow me to monitor where my CPU resource is being used by SQL server ?

Thanks for any help

Scott

2

Sorry, think this should work.

DECLARE @CurrentLoginUser int
DECLARE @CurrentLoginUser1 int


SET @CurrentLoginUser = (SELECT cntr_value AS [LoginsPerSec] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'Logins/sec')
WAITFOR DELAY '000:00:01'
SET @CurrentLoginUser1 = (SELECT cntr_value AS [LoginsPerSec] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'Logins/sec')

SELECT @CurrentLoginUser1 - @CurrentLoginUser
  • For future compatibility (at least in this specific case), only filter sys.dm_os_performance_counters by counter_name. In 2008 R2 where I first tried this code, the object_name column contains the server instance name, and it returned nothing. – Jon Seigel Sep 14 '12 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.