How can we help you today? How can we help you today?

How does Redgate SQL Monitor capture and calculate the "SQL Server: Processor Time" counter?

I'm trying to understand how Redgate SQL Monitor captures and calculates the "SQL Server: Processor Time" metric. According to the documentation, this metric represents the percentage of processor time used by the SQL Server service (excluding other related processes like SQL Server Agent, SSIS, etc.). It is based on the Performance Monitor counter Process(sqlservr): % Processor Time.

I would like to know:

Where exactly does SQL Monitor retrieve this data from (PerfMon, WMI, etc.)?
What formula does SQL Monitor use to calculate and display this value in its dashboard?
How does SQL Monitor store this metric for later analysis (e.g., intervals, timestamps)?
Is this value calculated directly from Windows Performance counters, or does it involve additional processing?
Finally, if possible, could you provide me with a SQL query or PowerShell script that extracts the same "SQL Server: Processor Time" metric in a way that matches what Redgate SQL Monitor displays?

Thanks in advance!
mOhAmAdreZa0011
0

Comments

1 comment

Sort by
  • Alex B
    Hi @mOhAmAdreZa0011,

    Redgate Monitor collects this query from the WMI performance counter class Win32_PerfRawData_PerfProc_Process, looking at the various SQL Server processes and getting the PercentProcessorTime property for that specific metric.

    The counter is a cumulative counter, and so the presented data is calculated from the sampled data by dx(PercentProcessorTime)/dt(SampleTime).

    The sample is stored in the repository as a point in time value of the cumulative counter with the sample time in ticks.

    It's a more complicated query than WMI can handle, I think, to get the value that is displayed, so you would need to do something like:
    SELECT PercentProcessorTime FROM Win32_PerfRawData_PerfProc_Process WHERE Name = 'sqlservr'
    And then run it again 15 seconds later and then calculate taking the difference of (second sample - first sample) / (second sample time - first sample time).

    And/or you can look at the equivalent perfmon counter and at the value every 15 seconds from when the Base Monitor service started to see if it matches up.

    Kind regards,
    Alex
    Alex B
    0

Add comment

Please sign in to leave a comment.