Using sys.dm_os_performance_counters and DIFF to display logins per second

Posted on

Question :

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

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *