sql-serverperformance-testingperfmon

What will be equivalent PerfMon counters for SQL Response Time?


What will be equivalent PerfMon counters for SQL Response Time? Please check image for more clarification.

I have posted the counter from Resource Monitor. I want equivalent PerfMon counters for SQL Response Time (last coulmn) to show in Grafana.

Resource Monitor


Solution

  • The DMV query below will report the IO stats for each file since the SQL instances was last started, with the average total, read, and write response times calculated.

    WITH io_stats AS (
        SELECT
              mf.physical_name
            , vfs.NumberReads + vfs.NumberWrites AS NumberTotal
            , vfs.IoStallReadMS + vfs.IoStallWriteMS AS IoStallTotalMS
            , vfs.NumberReads
            , vfs.IoStallReadMS
            , vfs.NumberWrites
            , vfs.IoStallWriteMS
        FROM sys.master_files AS mf
        CROSS APPLY sys.fn_virtualfilestats(database_id, file_id) AS vfs
    )
    SELECT
          physical_name
        , NumberTotal
        , IoStallTotalMS
        , IoStallTotalMS / CASE WHEN NumberTotal = 0 THEN NULL ELSE NumberTotal END AS ResponseTime
        , NumberReads
        , IoStallReadMS
        , IoStallReadMS / CASE WHEN NumberReads = 0 THEN NULL ELSE NumberReads END AS ResponseTimeRead
        , NumberWrites
        , IoStallWriteMS
        , IoStallWriteMS / CASE WHEN NumberWrites = 0 THEN NULL ELSE NumberWrites END AS ResponseTimeWrite
    FROM io_stats;