Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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