SQLServer 资源使用分析

资源使用分析概述

SQL Server 数据库性能很大程度上取决于系统资源的使用情况。监控和分析资源使用情况是 DBA 日常工作的重要组成部分，可以帮助识别性能瓶颈、优化资源配置和预防潜在问题。

SQL Server 使用的主要资源包括 CPU、内存、磁盘和网络。这些资源的使用情况相互关联，一个资源的瓶颈可能会影响其他资源的性能。因此，全面分析资源使用情况对于优化数据库性能至关重要。

资源类型与监控指标

CPU 资源

CPU 是 SQL Server 处理查询和执行操作的核心资源。CPU 瓶颈会导致查询延迟、系统响应缓慢和用户体验下降。

关键监控指标：

CPU 使用率（总体和每个 CPU 核心）
SQL Server 进程 CPU 使用率
等待类型：SOS_SCHEDULER_YIELD、CPU 相关的信号等待
工作线程数
并行查询执行情况

生产场景示例：

某电商网站在促销活动期间，CPU 使用率持续超过 90%，导致系统响应缓慢
分析：大量并发查询导致 CPU 资源耗尽
解决方案：优化查询、调整 MAXDOP 设置、增加 CPU 资源

实用监控脚本：

sql

-- 查看 CPU 基本信息和内存配置
SELECT 
    cpu_count AS [逻辑 CPU 数量],
    hyperthread_ratio AS [超线程比例],
    cpu_count/hyperthread_ratio AS [物理 CPU 数量],
    physical_memory_kb/1024 AS [物理内存 (MB)],
    committed_kb/1024 AS [已提交内存 (MB)],
    committed_target_kb/1024 AS [目标提交内存 (MB)],
    page_reads_per_sec AS [每秒页读取数],
    page_writes_per_sec AS [每秒页写入数],
    page_life_expectancy AS [页面预期寿命 (秒)]
FROM 
    sys.dm_os_sys_info;

-- 查看 SQL Server 进程 CPU 使用率历史
SELECT 
    record_id,
    DATEADD(ms, -1 * (os_sys_info.cpu_ticks / CONVERT(FLOAT, (os_sys_info.cpu_ticks / os_sys_info.ms_ticks)), 
        DATEADD(ms, record.value('(./Record/@id)[1]', 'int') * 1000, GETDATE())) AS [事件时间],
    100 - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') - 
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [其他进程 CPU 使用率 (%)],
    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQL Server CPU 使用率 (%)]
FROM 
    (SELECT 
        record.value('(./Record/@id)[1]', 'int') AS record_id,
        record 
    FROM 
        (SELECT 
            CAST(target_data AS XML) AS target_data 
        FROM 
            sys.dm_xe_session_targets st 
        JOIN 
            sys.dm_xe_sessions s ON s.address = st.event_session_address 
        WHERE 
            s.name = 'system_health') AS Data 
    CROSS APPLY 
        target_data.nodes('//Record') AS T(record)) AS x
JOIN 
    sys.dm_os_sys_info AS os_sys_info
ORDER BY 
    record_id DESC;

-- 查看高 CPU 消耗的查询
SELECT TOP 10
    total_worker_time/execution_count AS [平均 CPU 时间 (微秒)],
    total_worker_time AS [总 CPU 时间 (微秒)],
    execution_count AS [执行次数],
    total_elapsed_time/execution_count AS [平均执行时间 (微秒)],
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(max), text)) * 2 
            ELSE statement_end_offset 
        END - statement_start_offset)/2) 
     FROM sys.dm_exec_sql_text(sql_handle)) AS [查询文本]
FROM 
    sys.dm_exec_query_stats
ORDER BY 
    [平均 CPU 时间 (微秒)] DESC;

内存资源

内存是 SQL Server 最重要的资源之一，用于存储数据缓冲区、执行计划、锁信息和其他系统数据。内存不足会导致频繁的磁盘 I/O，严重影响性能。

关键监控指标：

内存使用率
缓冲区高速缓存命中率
页面预期寿命 (Page Life Expectancy, PLE)
内存授予等待
最小服务器内存和最大服务器内存配置
内存压力指示

生产场景示例：

某数据仓库系统的 PLE 持续低于 300 秒，导致磁盘 I/O 增加
分析：SQL Server 内存配置不足，无法缓存足够的数据
解决方案：增加 SQL Server 最大内存配置，优化查询减少内存使用

实用监控脚本：

sql

-- 查看内存使用情况
SELECT 
    type AS [内存 Clerk 类型],
    name AS [Clerk 名称],
    SUM(pages_kb) AS [使用大小 (KB)],
    SUM(pages_kb) / 1024 AS [使用大小 (MB)],
    SUM(pages_kb) / 1024 / 1024 AS [使用大小 (GB)]
FROM 
    sys.dm_os_memory_clerks
GROUP BY 
    type, name
ORDER BY 
    [使用大小 (KB)] DESC;

-- 查看缓冲池使用情况
SELECT 
    database_id,
    DB_NAME(database_id) AS [数据库名称],
    COUNT(*) * 8 / 1024 AS [缓冲池大小 (MB)],
    SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8 / 1024 AS [脏页大小 (MB)],
    SUM(CASE WHEN is_modified = 0 THEN 1 ELSE 0 END) * 8 / 1024 AS [干净页大小 (MB)]
FROM 
    sys.dm_os_buffer_descriptors
WHERE 
    database_id > 4 -- 排除系统数据库
GROUP BY 
    database_id
ORDER BY 
    [缓冲池大小 (MB)] DESC;

-- 查看内存授予等待
SELECT 
    session_id AS [会话 ID],
    request_id AS [请求 ID],
    wait_type AS [等待类型],
    wait_time_ms AS [等待时间 (毫秒)],
    granted_memory_kb/1024 AS [已授予内存 (MB)],
    required_memory_kb/1024 AS [所需内存 (MB)],
    used_memory_kb/1024 AS [已使用内存 (MB)],
    text AS [查询文本]
FROM 
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle)
WHERE 
    wait_type LIKE '%MEMORY_GRANT%'
ORDER BY 
    wait_time_ms DESC;

磁盘资源

磁盘 I/O 是 SQL Server 性能的常见瓶颈，特别是对于读写密集型工作负载。监控磁盘 I/O 可以帮助识别慢速查询、优化存储配置和调整数据库设计。

关键监控指标：

磁盘 I/O 利用率
平均磁盘秒/读和平均磁盘秒/写
磁盘队列长度
SQL Server 磁盘读写统计
数据库文件 I/O 统计
事务日志 I/O 统计

生产场景示例：

某 OLTP 系统的事务日志写入延迟持续超过 20ms，导致事务提交延迟
分析：存储系统无法满足事务日志的写入需求
解决方案：使用更快的存储设备、优化事务设计、调整日志文件大小

实用监控脚本：

sql

-- 查看数据库文件 I/O 统计
SELECT 
    DB_NAME(fs.database_id) AS [数据库名称],
    mf.physical_name AS [文件路径],
    io_stall_read_ms AS [读延迟 (毫秒)],
    num_of_reads AS [读次数],
    CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10,1)) AS [平均读延迟 (毫秒)],
    io_stall_write_ms AS [写延迟 (毫秒)],
    num_of_writes AS [写次数],
    CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10,1)) AS [平均写延迟 (毫秒)],
    io_stall_read_ms + io_stall_write_ms AS [总延迟 (毫秒)],
    num_of_reads + num_of_writes AS [总 I/O 次数],
    CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [平均 I/O 延迟 (毫秒)]
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
JOIN 
    sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
ORDER BY 
    [平均 I/O 延迟 (毫秒)] DESC;

-- 查看 TempDB I/O 统计
SELECT 
    fs.database_id,
    DB_NAME(fs.database_id) AS [数据库名称],
    mf.physical_name AS [文件路径],
    io_stall_read_ms AS [读延迟 (毫秒)],
    num_of_reads AS [读次数],
    io_stall_write_ms AS [写延迟 (毫秒)],
    num_of_writes AS [写次数],
    io_stall_read_ms + io_stall_write_ms AS [总延迟 (毫秒)]
FROM 
    sys.dm_io_virtual_file_stats(2, NULL) AS fs -- 2 是 TempDB 的数据库 ID
JOIN 
    sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.file_id = mf.file_id
ORDER BY 
    [总延迟 (毫秒)] DESC;

-- 查看 TempDB 使用情况
SELECT 
    name AS [文件名称],
    size/128.0 AS [总大小 (MB)],
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已使用 (MB)],
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS [可用空间 (MB)],
    physical_name AS [文件路径]
FROM 
    tempdb.sys.database_files;

网络资源

网络资源对于分布式数据库系统和客户端-服务器架构至关重要。网络延迟和带宽限制会影响 SQL Server 与应用程序之间的数据传输，导致查询响应缓慢。

关键监控指标：

网络带宽使用率
网络延迟
网络错误
SQL Server 网络统计
客户端连接数

生产场景示例：

某分布式系统中，客户端与数据库服务器之间的网络延迟超过 50ms，导致应用响应缓慢
分析：网络带宽不足或网络设备性能问题
解决方案：优化查询减少数据传输量、增加网络带宽、优化网络设备配置

实用监控脚本：

sql

-- 查看网络连接情况
SELECT 
    session_id AS [会话 ID],
    net_transport AS [网络传输协议],
    protocol_type AS [协议类型],
    encrypt_option AS [加密选项],
    auth_scheme AS [身份验证方案],
    client_net_address AS [客户端 IP 地址],
    client_tcp_port AS [客户端 TCP 端口],
    local_net_address AS [本地 IP 地址],
    local_tcp_port AS [本地 TCP 端口],
    connection_id AS [连接 ID],
    last_read AS [最后读取时间],
    last_write AS [最后写入时间],
    num_reads AS [读次数],
    num_writes AS [写次数]
FROM 
    sys.dm_exec_connections
ORDER BY 
    last_write DESC;

-- 查看等待网络相关的请求
SELECT 
    session_id AS [会话 ID],
    wait_type AS [等待类型],
    wait_time_ms AS [等待时间 (毫秒)],
    wait_resource AS [等待资源],
    text AS [查询文本]
FROM 
    sys.dm_exec_requests r
CROSS APPLY 
    sys.dm_exec_sql_text(r.sql_handle)
WHERE 
    wait_type LIKE '%NETWORK%' OR wait_type = 'ASYNC_NETWORK_IO'
ORDER BY 
    wait_time_ms DESC;

资源使用分析方法

综合分析方法

生产场景示例：

某企业数据库系统性能下降，需要确定主要瓶颈资源
分析步骤：
1. 查看等待统计信息，确定主要等待类型
2. 监控 CPU、内存、磁盘和网络资源使用情况
3. 分析查询执行计划，识别资源密集型查询
4. 综合分析结果，确定主要瓶颈和解决方案

基线比较：建立正常运行时的资源使用基线，将当前使用情况与基线进行比较，识别异常变化
趋势分析：监控资源使用的长期趋势，预测未来资源需求
瓶颈定位：使用等待类型分析确定主要瓶颈资源
关联分析：分析不同资源之间的关联关系，找出根本原因

使用 SQL Server 工具

SQL Server Management Studio (SSMS)：

活动监视器：实时查看 CPU、内存、磁盘和网络使用情况
性能仪表板：提供数据库性能的综合视图
查询存储：分析查询性能和资源使用情况

Extended Events：

轻量级事件捕获系统，比 Profiler 性能影响小
可自定义事件会话，捕获特定资源相关事件

Performance Monitor：

监控系统和 SQL Server 性能计数器
创建数据收集器集，长期监控资源使用情况

实用工具脚本：

sql

-- 创建 Extended Events 会话监控资源使用
CREATE EVENT SESSION [ResourceUsageMonitoring] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([duration],(1000000)) -- 只捕获超过1秒的查询
        AND [sqlserver].[database_name]<>'master' 
        AND [sqlserver].[database_name]<>'msdb')),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
    WHERE ([package0].[greater_than_uint64]([duration],(500000)) -- 只捕获超过500毫秒的存储过程语句
        AND [sqlserver].[database_name]<>'master' 
        AND [sqlserver].[database_name]<>'msdb'))
ADD TARGET package0.event_file(SET filename=N'D:\XE\ResourceUsageMonitoring.xel',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);

-- 启动事件会话
ALTER EVENT SESSION [ResourceUsageMonitoring] ON SERVER STATE = START;

高级分析技术

等待统计分析：

sql

-- 分析等待统计
WITH WaitStats AS (
    SELECT 
        wait_type AS [等待类型],
        wait_time_ms / 1000.0 AS [等待时间 (秒)],
        signal_wait_time_ms / 1000.0 AS [信号等待时间 (秒)],
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS [资源等待时间 (秒)],
        waiting_tasks_count AS [等待任务数],
        100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS [等待时间占比 (%)]
    FROM 
        sys.dm_os_wait_stats
    WHERE 
        wait_type NOT IN (
            'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
            'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',
            'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT',
            'DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
        )
)
SELECT 
    [等待类型],
    [等待时间 (秒)],
    [信号等待时间 (秒)],
    [资源等待时间 (秒)],
    [等待任务数],
    [等待时间占比 (%)],
    ROW_NUMBER() OVER (ORDER BY [等待时间 (秒)] DESC) AS [排名]
FROM 
    WaitStats
ORDER BY 
    [等待时间 (秒)] DESC;

资源优化策略

CPU 优化

生产场景示例：

某报表系统的 CPU 使用率持续高位，影响其他业务系统
优化措施：
1. 识别并优化高 CPU 消耗的查询
2. 调整 MAXDOP 设置，限制并行查询的 CPU 使用
3. 使用资源调控器，限制报表查询的 CPU 使用率
4. 考虑在业务低峰期运行报表

实用优化脚本：

sql

-- 查看当前 MAXDOP 设置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism';

-- 设置 MAXDOP
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

-- 使用资源调控器创建工作负载组
-- 创建资源池
CREATE RESOURCE POOL ReportPool
WITH (
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 30,
    CAP_CPU_PERCENT = 40,
    AFFINITY SCHEDULER = AUTO
);

-- 创建工作负载组
CREATE WORKLOAD GROUP ReportWorkload
WITH (
    IMPORTANCE = MEDIUM,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 600
)
USING ReportPool;

-- 创建分类器函数
CREATE FUNCTION dbo.ReportClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @WorkloadGroup SYSNAME;
    
    IF SUSER_SNAME() = 'ReportUser' -- 报表用户
        SET @WorkloadGroup = 'ReportWorkload';
    ELSE
        SET @WorkloadGroup = 'default';
    
    RETURN @WorkloadGroup;
END;

-- 注册分类器函数
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ReportClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;

内存优化

生产场景示例：

某 OLTP 系统的 PLE 持续低于 300 秒，导致磁盘 I/O 增加
优化措施：
1. 增加 SQL Server 最大内存配置
2. 优化查询，减少内存使用
3. 调整索引，减少扫描操作
4. 考虑使用内存优化表

实用优化脚本：

sql

-- 查看当前内存配置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'min server memory';
EXEC sp_configure 'max server memory';

-- 设置最大内存
EXEC sp_configure 'max server memory', 32768; -- 32GB
RECONFIGURE;

-- 查看内存优化表使用情况
SELECT 
    DB_NAME(database_id) AS [数据库名称],
    object_id AS [对象 ID],
    OBJECT_NAME(object_id, database_id) AS [表名称],
    memory_allocated_for_table_kb AS [表内存分配 (KB)],
    memory_allocated_for_indexes_kb AS [索引内存分配 (KB)],
    memory_allocated_for_table_kb + memory_allocated_for_indexes_kb AS [总内存分配 (KB)]
FROM 
    sys.dm_db_xtp_table_memory_stats
ORDER BY 
    [总内存分配 (KB)] DESC;

磁盘优化

生产场景示例：

某数据仓库系统的查询性能下降，磁盘 I/O 利用率持续超过 80%
优化措施：
1. 优化查询，减少磁盘 I/O
2. 增加索引，减少全表扫描
3. 使用分区表，提高查询性能
4. 考虑使用 SSD 存储

实用优化脚本：

sql

-- 查看索引使用情况，识别未使用的索引
SELECT 
    DB_NAME(database_id) AS [数据库名称],
    OBJECT_NAME(object_id, database_id) AS [表名称],
    name AS [索引名称],
    user_seeks + user_scans + user_lookups AS [用户访问次数],
    user_updates AS [用户更新次数],
    last_user_seek AS [最后用户查找时间],
    last_user_scan AS [最后用户扫描时间],
    last_user_lookup AS [最后用户查找时间],
    last_user_update AS [最后用户更新时间]
FROM 
    sys.dm_db_index_usage_stats
WHERE 
    database_id > 4 -- 排除系统数据库
    AND object_id > 0
    AND index_id > 0
ORDER BY 
    [用户访问次数] ASC;

-- 查看 TempDB 文件配置
SELECT 
    name AS [文件名称],
    size/128.0 AS [大小 (MB)],
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [已使用 (MB)],
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS [可用空间 (MB)],
    physical_name AS [文件路径]
FROM 
    tempdb.sys.database_files;

-- 添加 TempDB 文件
ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdb_data2,
    FILENAME = 'D:\SQLData\tempdb_data2.ndf',
    SIZE = 8192MB,
    FILEGROWTH = 1024MB
);

网络优化

生产场景示例：

某 Web 应用的数据库查询响应缓慢，分析发现是由于大量数据传输导致的网络延迟
优化措施：
1. 优化查询，只返回必要的列
2. 增加 WHERE 条件，减少返回的行数
3. 使用分页查询，减少单次数据传输量
4. 考虑在应用层添加缓存

实用优化建议：

优化查询，减少数据传输量：

sql

-- 不好的写法：返回所有列
SELECT * FROM Orders WHERE CustomerID = 123;

-- 好的写法：只返回必要的列
SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE CustomerID = 123;

使用分页查询：

sql

-- 使用 OFFSET FETCH 进行分页
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = 123
ORDER BY OrderDate DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

启用连接池：在应用程序连接字符串中添加 Pooling=true;Max Pool Size=100;

版本差异

SQL Server 2012

引入了内存优化表和原生编译存储过程
增强了资源调控器功能
引入了列存储索引，减少 I/O 操作

SQL Server 2014

增强了内存优化表功能
引入了缓冲池扩展，允许使用 SSD 扩展缓冲池
改进了查询优化器，减少内存使用

SQL Server 2016

引入了 Query Store，便于查询性能分析
增强了列存储索引功能，支持实时操作
改进了内存管理，减少内存压力

SQL Server 2019

引入了智能查询处理，自动优化查询
增强了内存中 OLTP 功能
引入了持久性内存支持
改进了 TempDB 性能

SQL Server 2022

增强了智能查询处理功能
引入了 Azure Synapse Link，支持实时分析
改进了存储引擎，提高 I/O 性能
增强了安全功能，同时优化了性能

常见问题（FAQ）

如何确定当前的主要资源瓶颈？

问题：生产环境中系统响应缓慢，如何快速确定主要资源瓶颈？

解答：

使用活动监视器查看实时资源使用情况
分析等待统计信息，确定主要等待类型
检查 CPU、内存、磁盘和网络的关键性能指标
识别资源密集型查询

实用脚本：

sql

-- 快速检查资源使用情况
SELECT 
    (SELECT TOP 1 CAST(100 - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') - 
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS FLOAT) 
     FROM (SELECT CAST(target_data AS XML) AS target_data 
           FROM sys.dm_xe_session_targets st 
           JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address 
           WHERE s.name = 'system_health') AS Data 
     CROSS APPLY target_data.nodes('//Record') AS T(record) 
     ORDER BY record.value('(./Record/@id)[1]', 'int') DESC) AS [其他进程 CPU 使用率 (%)],
    
    (SELECT TOP 1 CAST(record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS FLOAT) 
     FROM (SELECT CAST(target_data AS XML) AS target_data 
           FROM sys.dm_xe_session_targets st 
           JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address 
           WHERE s.name = 'system_health') AS Data 
     CROSS APPLY target_data.nodes('//Record') AS T(record) 
     ORDER BY record.value('(./Record/@id)[1]', 'int') DESC) AS [SQL Server CPU 使用率 (%)],
    
    (SELECT page_life_expectancy FROM sys.dm_os_performance_counters WHERE counter_name = 'Page life expectancy' AND object_name LIKE '%Buffer Manager%') AS [页面预期寿命 (秒)],
    
    (SELECT AVG(io_stall_read_ms / (1.0 + num_of_reads)) 
     FROM sys.dm_io_virtual_file_stats(NULL, NULL)) AS [平均读延迟 (毫秒)],
    
    (SELECT AVG(io_stall_write_ms / (1.0 + num_of_writes)) 
     FROM sys.dm_io_virtual_file_stats(NULL, NULL)) AS [平均写延迟 (毫秒)],
    
    (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1) AS [用户连接数];

如何优化高 CPU 消耗的查询？

问题：如何识别和优化高 CPU 消耗的查询？

解答：

使用 DMVs 查询识别高 CPU 查询
分析查询执行计划，识别瓶颈
优化查询逻辑，减少不必要的计算
添加或调整索引，减少扫描操作
考虑使用分区表或内存优化表

实用脚本：

sql

-- 识别高 CPU 查询
SELECT TOP 10
    total_worker_time/execution_count AS [平均 CPU 时间 (微秒)],
    total_worker_time AS [总 CPU 时间 (微秒)],
    execution_count AS [执行次数],
    total_elapsed_time/execution_count AS [平均执行时间 (微秒)],
    query_hash AS [查询哈希],
    query_plan_hash AS [执行计划哈希],
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1 
            THEN LEN(CONVERT(nvarchar(max), text)) * 2 
            ELSE statement_end_offset 
        END - statement_start_offset)/2) 
     FROM sys.dm_exec_sql_text(sql_handle)) AS [查询文本],
    (SELECT query_plan FROM sys.dm_exec_query_plan(plan_handle)) AS [执行计划 XML]
FROM 
    sys.dm_exec_query_stats
ORDER BY 
    [平均 CPU 时间 (微秒)] DESC;

如何优化 TempDB 性能？

问题：如何优化 TempDB 性能，减少 TempDB 相关的等待？

解答：

配置多个 TempDB 文件，数量等于或略少于 CPU 核心数
确保所有 TempDB 文件大小相同，避免自动增长
将 TempDB 放在快速存储设备上
优化查询，减少 TempDB 使用
监控 TempDB 使用情况

实用优化脚本：

sql

-- 检查 TempDB 文件配置
SELECT 
    name AS [文件名称],
    size/128.0 AS [大小 (MB)],
    growth/128.0 AS [自动增长 (MB)],
    physical_name AS [文件路径]
FROM 
    tempdb.sys.database_files;

-- 查看 TempDB 等待情况
SELECT 
    wait_type AS [等待类型],
    wait_time_ms / 1000.0 AS [等待时间 (秒)],
    waiting_tasks_count AS [等待任务数],
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS [等待时间占比 (%)]
FROM 
    sys.dm_os_wait_stats
WHERE 
    wait_type LIKE '%TEMPDB%'
ORDER BY 
    [等待时间 (秒)] DESC;

最佳实践

建立监控基线

生产场景示例：

某企业数据库系统建立了完善的资源监控基线，包括：
- CPU 使用率：正常 20-40%，峰值 70%
- 内存使用率：正常 70-80%
- 磁盘 I/O 延迟：读 < 5ms，写 < 10ms
- PLE：> 300 秒
当监控指标超过基线时，自动发送告警通知 DBA

定期收集资源使用数据，建立正常运行时的基线
比较当前使用情况与基线，识别异常变化
基于基线设置告警阈值，及时发现问题

持续监控和优化

实施常态化的资源监控机制
定期分析资源使用情况，识别优化机会
持续优化查询、索引和配置
定期审查和调整资源配置

采用分层监控策略

实时监控：使用活动监视器和实时查询监控
短期监控：使用 Extended Events 或 Profiler 捕获特定事件
长期监控：使用性能计数器和数据收集器集
历史分析：定期分析历史数据，识别趋势和模式

优化工作负载

识别并优化资源密集型工作负载
合理安排维护任务，避免业务高峰期
使用资源调控器管理不同工作负载
考虑工作负载隔离，使用不同的实例或数据库

总结

资源使用分析是 SQL Server 性能优化的重要组成部分。通过全面监控和分析 CPU、内存、磁盘和网络资源的使用情况，DBA 可以识别性能瓶颈、优化资源配置和预防潜在问题。

本文介绍了 SQL Server 资源使用的监控方法、分析技巧和优化策略，包括关键性能指标、DMVs 查询、性能计数器和最佳实践。DBA 应该建立完善的资源监控机制，持续优化资源使用，确保数据库系统的高性能和高可用性。

通过合理的资源管理和优化，企业可以提高数据库性能，提升用户体验，降低硬件成本，并确保业务系统的稳定运行。

SQLServer 资源使用分析 ​

资源使用分析概述 ​

资源类型与监控指标 ​

CPU 资源 ​

内存资源 ​

磁盘资源 ​

网络资源 ​

资源使用分析方法 ​

综合分析方法 ​

使用 SQL Server 工具 ​

高级分析技术 ​

资源优化策略 ​

CPU 优化 ​

内存优化 ​

磁盘优化 ​

网络优化 ​

版本差异 ​

SQL Server 2012 ​

SQL Server 2014 ​

SQL Server 2016 ​

SQL Server 2019 ​

SQL Server 2022 ​

常见问题（FAQ） ​

如何确定当前的主要资源瓶颈？ ​

如何优化高 CPU 消耗的查询？ ​

如何优化 TempDB 性能？ ​

最佳实践 ​

建立监控基线 ​

持续监控和优化 ​

采用分层监控策略 ​

优化工作负载 ​

总结 ​

SQLServer 资源使用分析

资源使用分析概述

资源类型与监控指标

CPU 资源

内存资源

磁盘资源

网络资源

资源使用分析方法

综合分析方法

使用 SQL Server 工具

高级分析技术

资源优化策略

CPU 优化

内存优化

磁盘优化

网络优化

版本差异

SQL Server 2012

SQL Server 2014

SQL Server 2016

SQL Server 2019

SQL Server 2022

常见问题（FAQ）

如何确定当前的主要资源瓶颈？

如何优化高 CPU 消耗的查询？

如何优化 TempDB 性能？

最佳实践

建立监控基线

持续监控和优化

采用分层监控策略

优化工作负载

总结