I have verified through SQL Server Profiler that my request per-second are around 30 request/s as is corroborated by SSMS Activity Monitor but sys.dm_os_performance_counters
is reporting hundreds of millions/s.
Any idea what might be causing this gross discrepancy?
Query:
SELECT
RTrim(LTrim(object_name)) as object_name,
RTrim(LTrim(counter_name)) as counter_name,
cntr_value
FROM
sys.dm_os_performance_counters
WHERE
instance_name IN ('', '_Total')
and counter_name IN (
N'Batch Requests/sec'
, N'SQL Compilations/sec'
, N'SQL Re-Compilations/sec'
, N'Transactions/sec')
Results:
object_name counter_name cntr_value
SQLServer:Databases Transactions/sec 191721399
SQLServer:SQL Statistics Batch Requests/sec 242955426
SQLServer:SQL Statistics SQL Compilations/sec 42048371
SQLServer:SQL Statistics SQL Re-Compilations/sec 1200947
Best Answer
From the documentation:
If you'd like something that already does interval sampling, sp_BlitzFirst is pretty neat, and free, and full disclosure: I am a contributor, etc.