sql-server - What will be equivalent PerfMon counters for SQL Response Time?

Question

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.

标签： sql-serverperformance-testingperfmon

Answer 1

下面的 DMV 查询将报告自 SQL 实例上次启动以来每个文件的 IO 统计信息，并计算平均总、读取和写入响应时间。

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;