4

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
12

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.

| improve this answer | |
1

I may have found part of this from stackoverflow, but I like to use select sqlserver_start_time from sys.dm_os_sys_info for this. A lot of the data in sys.dm_os_performance_counters is cumulative. For quieter servers you may have to convert to decimal data types.

I have several versions of the query below for different perf metrics.

 with perf_data as
(
select @@servername as Server_name, instance_name as Database_Name, 
counter_name,  cntr_value as Total_Tran_Since_Startup
,SQL_Start_Time = (select sqlserver_start_time from sys.dm_os_sys_info)
--,Time_now = getdate()
--,Diff_Seconds = (SELECT DATEDIFF(ss, (select sqlserver_start_time from 
sys.dm_os_sys_info), getdate()))
,Tran_per_Second = (cast(cntr_value as decimal)/(SELECT DATEDIFF(ss, (select 
sqlserver_start_time from sys.dm_os_sys_info), getdate())))
from sys.dm_os_performance_counters
where counter_name in ('Transactions/sec', 'Redone Bytes/sec')
and instance_name not in ('master', 'msdb', 'model', 'mssqlsystemresource', 
'TESTAG', '_Total', 'TESTTG'                                                                                                                         
,'targettest')
--group by instance_name, counter_name, cntr_value
--and Server_name in ('server1', 'server2','server3')
--order by counter_name, instance_name)
)
select * from perf_data
--where Server_name in ('server1', 'server2','server3', 'server4' )
order by counter_name, tran_per_second desc
| improve this answer | |
1
DECLARE @DATEDEMARRAGE datetime

SELECT @DATEDEMARRAGE = create_date FROM sys.databases
WHERE name ='tempdb'

SELECT cntr_value / datediff (second,@DATEDEMARRAGE,getdate()) as [Batch Requests/sec]
FROM sys.dm_os_performance_counters
WHERE counter_name  like '%Batch Requests/se%'
| improve this answer | |
  • 2
    Do you have anything to say to the actual question ('Any idea what might be causing this gross discrepancy?'), or an explanation about what this piece of code does? – dezso May 15 '18 at 15:32
  • It was mentioned earlier. Some of the data in perfmon counters is cumulative since the last time SQL was started. If you want the per second or per minute data then you need to divide by the number of seconds or minutes since SQL Server has been started. – Alen May 15 '18 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.