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.
Current Workload 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%'
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.
