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