Tuesday, March 8, 2011

Finding Transactions / Second

The Performance Counters dmv can be used to determine Transactions per second.
Transactions is a cummulative counter however, so here is what I do to interpret it.

Use the script below, replacing 'database_name' as appropriate.
You may wish to change the delay (10s used here) to a more appropriate value (remembering to change the calculation that follows too).

SELECT cntr_value, *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'database_name'
  
WAITFOR DELAY '00:00:10'
  
SELECT cntr_value, *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'transactions/sec'
AND OBJECT_NAME = 'SQLServer:Databases'
AND instance_name = 'database_name'


The queries returned 581820652 and 581821012 respectively.

581821012 - 581820652 = 360

360 Transactions in a 10 second period

Therefore, 360 /10 = 36

36 Transactions per second.

No comments: