Check Database Server Workload - Batches/ Sec

Today's post is about a simple but very useful query.

One of the basic things one needs to check is the amount of workload on the database server. Amount of workload or finding how busy the server is, can be done by "Batch Requests/sec" performance counter ( I guess most of my well informed readers should know that :) )  "Batch Requests/sec" actually tells us number of batches ( batch of query requests ) that the SQL Server is processing per second

One can check the same by the query below.

SELECT object_name,counter_name,cntr_Value

FROM sys.dm_os_performance_Counters

WHERE counter_name like 'Batch Requests/sec%'

However, the above query actually returns a huge number which makes you wonder if the server is actually processing so many queries a second. The huge number is because of the fact that "sys.dm_os_performance_Counters" actually returns a cumilative value of 'Batch Requests/sec' since last restart. So, the correct way of finding average "batches/sec" or average workload on the server would be

Average Workload Query:

SELECT object_name,counter_name,

cntr_Value / datediff(ss, ( SELECT create_Date FROM

                            sys.databases

                            WHERE name like 'tempdb'),getdate())

                            as Avg_Batches_Per_Sec

FROM sys.dm_os_performance_Counters 
WHERE counter_name like 'Batch Requests/sec%'   

The above query gives us the average workload since last restart. If you are interested just checking the current workload, instead of the average since restart, then

Current Workload Query:

DECLARE @BatchRequests Bigint

SELECT @BatchRequests = cntr_Value

FROM sys.dm_os_performance_Counters

WHERE counter_name like 'Batch Requests/sec%'

WaitFor Delay '00:00:01'

SELECT object_name,counter_name,cntr_Value - @BatchRequests as [Batches/Sec]

FROM sys.dm_os_performance_Counters

WHERE counter_name like 'Batch Requests/sec%'

Though there are no good or bad numbers for Batches/sec counter, a busy server would be expected to have about few thousand batches per second with extremely busy ones reaching 5 digit workloads.