HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Batch Requests/sec reported by DMV millions of times larger than Activity Monitor

Submitted by: @import:stackexchange-dba··
0
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 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

Solution

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.

Context

StackExchange Database Administrators Q#163270, answer score: 12

Revisions (0)

No revisions yet.