patternsqlModerate
Batch Requests/sec reported by DMV millions of times larger than Activity Monitor
Viewed 0 times
monitorlargermillionsthanbatchdmvsecreportedactivityrequests
Problem
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
Any idea what might be causing this gross discrepancy?
Query:
Results:
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 1200947Solution
From the documentation:
Note: For per-second counters, this value is cumulative. The rate
value must be calculated by sampling the value at discrete time
intervals. The difference between any two successive sample values is
equal to the rate for the time interval used.
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.
Note: For per-second counters, this value is cumulative. The rate
value must be calculated by sampling the value at discrete time
intervals. The difference between any two successive sample values is
equal to the rate for the time interval used.
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.
Context
StackExchange Database Administrators Q#163270, answer score: 12
Revisions (0)
No revisions yet.