如何通过SQL Server查询获取与任务管理器一致的系统CPU使用率？

因为无法通过WMI访问目标服务器，可借助SQL Server的动态管理视图(DMV)来获取匹配任务管理器的CPU使用率数据，以下是具体实现方案：

1. 系统级CPU使用率查询（和任务管理器总体CPU数据一致） #

通过读取SQL Server环形缓冲区的系统健康数据，计算实时CPU使用率：

WITH CPUUsage AS (
    SELECT 
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_idle,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserTime)[1]', 'int') AS user_time,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelTime)[1]', 'int') AS kernel_time,
        DATEADD(ms, -1 * record.value('(./Record/@time)[1]', 'bigint'), GETDATE()) AS event_time
    FROM (
        SELECT CONVERT(xml, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
        AND record LIKE N'%<SystemHealth>%'
    ) AS x
)
SELECT 
    TOP 1
    100 - (system_idle * 100.0 / (user_time + kernel_time + system_idle)) AS cpu_usage_percent,
    event_time
FROM CPUUsage
ORDER BY event_time DESC;

2. SQL Server进程自身CPU使用率查询 #

如果只需要获取SQL Server服务占用的CPU比例，可使用以下语句：

SELECT 
    (cpu_time / 1000.0 / DATEDIFF(ms, start_time, GETDATE())) * 100 AS sql_server_cpu_usage_percent
FROM sys.dm_os_process_memory
CROSS JOIN sys.dm_os_sys_info;

3. C#中调用示例 #

通过ADO.NET执行上述查询并获取结果：

using System.Data.SqlClient;

public static decimal GetServerCpuUsage(string connectionString)
{
    string query = @"WITH CPUUsage AS (
        SELECT 
            record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_idle,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserTime)[1]', 'int') AS user_time,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelTime)[1]', 'int') AS kernel_time,
            DATEADD(ms, -1 * record.value('(./Record/@time)[1]', 'bigint'), GETDATE()) AS event_time
        FROM (
            SELECT CONVERT(xml, record) AS record
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE N'%<SystemHealth>%'
        ) AS x
    )
    SELECT TOP 1 100 - (system_idle * 100.0 / (user_time + kernel_time + system_idle)) AS cpu_usage_percent
    FROM CPUUsage
    ORDER BY event_time DESC;";

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            object result = cmd.ExecuteScalar();
            return result != DBNull.Value ? Convert.ToDecimal(result) : 0;
        }
    }
}

注意事项 #

• 执行查询的SQL账号需要拥有VIEW SERVER STATE权限，否则无法访问动态管理视图。
• 环形缓冲区数据会定期覆盖，查询结果为最近的CPU使用率，若需持续监控，需定期执行查询并留存数据。

内容的提问来源于stack exchange，提问作者Kourosh