性能监控与查询调优

9.1 等待统计分析（Wait Statistics）

等待统计是 SQL Server 性能分析的第一入口。数据库引擎中的每一个等待都说明某个资源暂时不可用，分析等待类型可以精准定位瓶颈。

9.1.1 核心等待类型分类

-- 等待统计分析（自实例启动以来的累积数据）
-- 过滤掉良性空闲等待后按影响排序
SELECT TOP 30
    wait_type,
    waiting_tasks_count,
    CAST(wait_time_ms / 1000.0 AS DECIMAL(12,2))         AS TotalWaitSec,
    CAST(wait_time_ms / 1000.0 /
         NULLIF(waiting_tasks_count, 0) AS DECIMAL(10,3)) AS AvgWaitMsPerTask,
    CAST(signal_wait_time_ms * 100.0 /
         NULLIF(wait_time_ms, 0) AS DECIMAL(5,2))         AS SignalWaitPct,  -- CPU竞争占比
    CAST(wait_time_ms * 100.0 /
         SUM(wait_time_ms) OVER() AS DECIMAL(5,2))        AS PctOfTotal
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'HADR_WORK_QUEUE','LAZYWRITER_SLEEP','LOGMGR_QUEUE',
    'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE',
    'SERVER_IDLE_CHECK','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY',
    'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP',
    'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT',
    'SP_SERVER_DIAGNOSTICS_SLEEP','SQLAGENT_ALERT_SET','SQLAGENT_LOCK_MONITOR',
    'SQLAGENT_NOTIFY','SQLAGENT_WAIT_ON_OPER','WAITFOR','XE_DISPATCHER_WAIT',
    'XE_TIMER_EVENT','BROKER_EVENTHANDLER','CHECKPOINT_QUEUE',
    'DBMIRROR_EVENTS_QUEUE','TRACEWRITE','XE_DISPATCHER_JOIN',
    'WAIT_XTP_OFFLINE_CKPT_NEW_LOG','SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
    'SQLTRACE_BUFFER_FLUSH'
)
ORDER BY wait_time_ms DESC;

-- 重置等待统计（测试前清零）
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

9.2 动态管理视图（DMV）实战

9.2.1 最消耗资源的查询

-- TOP 消耗 CPU 的查询
SELECT TOP 20
    qs.total_worker_time / qs.execution_count / 1000    AS AvgCPUMs,
    qs.total_worker_time / 1000                          AS TotalCPUMs,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count          AS AvgLogicalReads,
    qs.total_elapsed_time / qs.execution_count / 1000   AS AvgElapsedMs,
    qs.creation_time                                     AS PlanCreationTime,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS QueryText,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

-- TOP 消耗 IO 的查询（逻辑读）
SELECT TOP 20
    qs.total_logical_reads / qs.execution_count AS AvgLogicalReads,
    qs.total_logical_reads,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS QueryText
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_logical_reads DESC;

-- 当前正在执行的请求
SELECT
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0         AS WaitSec,
    r.cpu_time / 1000.0          AS CPUSec,
    r.total_elapsed_time / 1000.0 AS ElapsedSec,
    r.logical_reads,
    r.reads,
    r.writes,
    r.percent_complete,
    s.login_name,
    s.host_name,
    s.program_name,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1, 200) AS CurrentSQL
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

9.2.2 Buffer Pool 分析

-- 查看哪些数据库的数据页占用了最多内存
SELECT
    CASE WHEN database_id = 32767 THEN 'Resource DB'
         ELSE DB_NAME(database_id)
    END AS DatabaseName,
    COUNT(*) * 8.0 / 1024 AS BufferPoolMB
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY COUNT(*) DESC;

-- 查看哪些表/索引占用了最多 Buffer Pool
SELECT TOP 20
    DB_NAME(bd.database_id)      AS DatabaseName,
    OBJECT_NAME(i.object_id)    AS TableName,
    i.name                       AS IndexName,
    i.type_desc                  AS IndexType,
    COUNT(*) * 8.0 / 1024       AS BufferMB,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sys.dm_os_buffer_descriptors
                        WHERE database_id = bd.database_id) AS PctOfDB
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON (au.container_id = p.hobt_id)
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
  AND bd.page_type IN ('DATA_PAGE', 'INDEX_PAGE')
GROUP BY bd.database_id, i.object_id, i.name, i.type_desc
ORDER BY COUNT(*) DESC;

-- 内存总览
SELECT
    physical_memory_in_use_kb / 1024.0   AS SQLMemoryUsedMB,
    locked_page_allocations_kb / 1024.0  AS LockedPagesMB,
    virtual_address_space_committed_kb / 1024.0 AS VASCommittedMB,
    memory_utilization_percentage
FROM sys.dm_os_process_memory;

9.3 查询存储（Query Store）

Query Store 是 SQL Server 2016+ 引入的查询历史数据库，可以追踪查询的执行计划变化和性能回归，是参数嗅探和计划回归问题的终极解决工具。

9.3.1 启用与配置 Query Store

-- 启用 Query Store
ALTER DATABASE [YourDB] SET QUERY_STORE = ON;

-- 详细配置
ALTER DATABASE [YourDB] SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,        -- READ_WRITE 收集；READ_ONLY 只读；OFF 关闭
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),  -- 保留30天
    DATA_FLUSH_INTERVAL_SECONDS = 900,  -- 每15分钟持久化到磁盘
    INTERVAL_LENGTH_MINUTES = 60,       -- 聚合间隔1小时
    MAX_STORAGE_SIZE_MB = 1024,         -- Query Store 最大占用1GB
    QUERY_CAPTURE_MODE = AUTO,          -- 自动：只捕获重要查询（推荐）
    SIZE_BASED_CLEANUP_MODE = AUTO,     -- 空间将满时自动清理旧数据
    MAX_PLANS_PER_QUERY = 200,          -- 每个查询保留最多200个计划
    WAIT_STATS_CAPTURE_MODE = ON        -- 同时收集等待统计（SQL 2017+）
);

-- 查看 Query Store 状态
SELECT
    actual_state_desc,
    readonly_reason,
    current_storage_size_mb,
    max_storage_size_mb,
    flush_interval_seconds,
    interval_length_minutes,
    stale_query_threshold_days,
    query_capture_mode_desc
FROM sys.database_query_store_options;

9.3.2 用 Query Store 识别和修复回归查询

-- 找出最近执行计划发生变化且性能下降的查询
WITH PlanStats AS (
    SELECT
        q.query_id,
        p.plan_id,
        p.last_force_failure_reason_desc,
        rs.avg_duration / 1000.0   AS AvgDurationMs,
        rs.avg_cpu_time / 1000.0   AS AvgCPUMs,
        rs.avg_logical_io_reads    AS AvgLogicalReads,
        rs.count_executions,
        rs.first_execution_time,
        rs.last_execution_time,
        ROW_NUMBER() OVER (PARTITION BY q.query_id ORDER BY rs.avg_duration DESC) AS RN
    FROM sys.query_store_query q
    JOIN sys.query_store_plan p ON q.query_id = p.query_id
    JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
    WHERE rs.last_execution_time > DATEADD(DAY, -7, GETDATE())
)
SELECT
    qt.query_sql_text,
    ps.query_id,
    ps.plan_id,
    ps.AvgDurationMs,
    ps.AvgCPUMs,
    ps.AvgLogicalReads,
    ps.count_executions
FROM PlanStats ps
JOIN sys.query_store_query q ON ps.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE ps.RN = 1
ORDER BY ps.AvgDurationMs DESC;

-- 强制使用特定执行计划（修复参数嗅探/计划回归）
-- 1. 先找到好的计划 ID（从 Query Store 的报表或上面的查询获取）
-- 2. 强制该计划
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

-- 取消强制计划（恢复优化器自动选择）
EXEC sys.sp_query_store_unforce_plan @query_id = 42, @plan_id = 7;

-- 清理 Query Store 数据
EXEC sys.sp_query_store_flush_db;       -- 将内存中的数据刷到磁盘
EXEC sys.sp_query_store_remove_plan (7); -- 删除特定计划
EXEC sys.sp_query_store_remove_query (42); -- 删除特定查询
ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;  -- 清空所有数据（慎用）

9.4 扩展事件（Extended Events）

扩展事件（XEvents）是 SQL Server Profiler 的替代品，开销更低，功能更强大。

9.4.1 创建 XEvents 会话

-- 示例：捕获执行时间超过 1 秒的查询
CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.username,
        sqlserver.client_hostname,
        sqlserver.plan_handle
    )
    WHERE sqlserver.duration >= 1000000   -- 单位：微秒，1000000 = 1秒
      AND sqlserver.database_id <> 32767  -- 排除 Resource DB
)
ADD TARGET package0.ring_buffer (
    SET max_memory = 51200,             -- 50MB 环形缓冲区
    max_events_limit = 1000
)
ADD TARGET package0.event_file (
    SET filename = 'D:\XEvents\SlowQueries.xel',
    max_file_size = 100,                -- 每个文件最大100MB
    max_rollover_files = 5              -- 最多保留5个文件
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    TRACK_CAUSALITY = ON
);

-- 启动/停止会话
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = STOP;

-- 读取 Ring Buffer 中的数据
SELECT
    event_data.value('(event/@name)[1]', 'NVARCHAR(50)')      AS EventName,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2')    AS EventTime,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000 AS DurationMs,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS SQLText,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'NVARCHAR(100)') AS DBName,
    event_data.value('(event/action[@name="username"]/value)[1]', 'NVARCHAR(100)') AS UserName
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
    WHERE s.name = 'SlowQueries' AND t.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData(event_data)
ORDER BY EventTime DESC;

-- 读取 XEL 文件
SELECT
    event_data.value('(event/@name)[1]', 'NVARCHAR(50)')       AS EventName,
    event_data.value('(event/@timestamp)[1]', 'DATETIME2')     AS EventTime,
    event_data.value('(event/data[@name="duration"]/value)[1]','BIGINT') / 1000 AS DurationMs,
    event_data.value('(event/action[@name="sql_text"]/value)[1]','NVARCHAR(MAX)') AS SQLText
FROM sys.fn_xe_file_target_read_file('D:\XEvents\SlowQueries*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML)) AS XData(event_data)
ORDER BY EventTime DESC;

9.5 内存优化表（In-Memory OLTP）

In-Memory OLTP（代号 Hekaton，SQL Server 2014+）将数据完全存储在内存中，使用乐观并发控制，可将 OLTP 性能提升 30~100 倍。

9.5.1 创建内存优化表

-- 1. 数据库需要有内存优化文件组
ALTER DATABASE [YourDB] ADD FILEGROUP IMOLTPFileGroup CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE [YourDB] ADD FILE (
    NAME = 'IMOLTPData',
    FILENAME = 'D:\Data\IMOLTPData'
) TO FILEGROUP IMOLTPFileGroup;

-- 2. 创建内存优化表（DURABILITY 决定是否持久化到磁盘）
CREATE TABLE dbo.SessionCache (
    SessionID    NVARCHAR(50)   NOT NULL,
    UserID       INT            NOT NULL,
    Data         NVARCHAR(2000) NULL,
    CreatedAt    DATETIME2      NOT NULL,
    ExpiresAt    DATETIME2      NOT NULL,
    CONSTRAINT PK_SessionCache PRIMARY KEY NONCLUSTERED HASH (SessionID)
        WITH (BUCKET_COUNT = 131072)  -- 哈希索引桶数，设为预期行数的2倍（取2的幂）
) WITH (
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA    -- 持久化架构和数据（重启后不丢失）
    -- DURABILITY = SCHEMA_ONLY      -- 仅持久化架构（适合临时数据，重启后数据丢失）
);

-- 内存优化表的索引（只支持 HASH 和 RANGE 两种）
ALTER TABLE dbo.SessionCache
ADD INDEX IX_SessionCache_UserID NONCLUSTERED (UserID);

-- 3. 原生编译存储过程（Natively Compiled SP）
-- 编译为 DLL，绕过解释器，性能极致
CREATE PROCEDURE dbo.usp_SetSession
    @SessionID NVARCHAR(50),
    @UserID    INT,
    @Data      NVARCHAR(2000),
    @ExpiresAt DATETIME2
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'Simplified Chinese'
)
    -- 原生编译 SP 内只能用有限的 T-SQL 子集
    IF EXISTS (SELECT 1 FROM dbo.SessionCache WHERE SessionID = @SessionID)
    BEGIN
        UPDATE dbo.SessionCache
        SET Data = @Data, ExpiresAt = @ExpiresAt
        WHERE SessionID = @SessionID;
    END
    ELSE
    BEGIN
        INSERT INTO dbo.SessionCache (SessionID, UserID, Data, CreatedAt, ExpiresAt)
        VALUES (@SessionID, @UserID, @Data, SYSDATETIME(), @ExpiresAt);
    END;
END;
GO

9.5.2 延迟持久化（Delayed Durability）

对于可接受少量数据丢失但要求极高写入吞吐量的场景（如日志表），可启用延迟持久化。

-- 数据库级别启用延迟持久化
ALTER DATABASE [YourDB] SET DELAYED_DURABILITY = ALLOWED;  -- 允许（由查询决定）
-- ALTER DATABASE [YourDB] SET DELAYED_DURABILITY = FORCED; -- 强制所有事务延迟持久

-- 事务级别启用
BEGIN TRANSACTION;
INSERT INTO dbo.EventLog (EventType, Message, LoggedAt)
VALUES ('INFO', 'User login', GETDATE());
COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON);
-- 日志写入在内存中聚集，每隔几ms或日志缓冲区满时才真正写磁盘
-- 风险：服务器崩溃时最后几毫秒的数据可能丢失

9.6 综合性能调优 SOP

-- ========================================
-- 每日性能巡检标准流程（运行一次）
-- ========================================

-- 1. 检查等待统计（找出当日最大瓶颈）
-- → 参考 9.1 节的等待统计查询

-- 2. 找出 TOP CPU/IO 查询
-- → 参考 9.2 节的 DMV 查询

-- 3. 检查是否有长时间运行的阻塞
SELECT session_id, blocking_session_id, wait_type, wait_time/1000 AS WaitSec
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

-- 4. 检查 TempDB 使用情况
SELECT SUM(user_object_reserved_page_count) * 8 / 1024 AS UserObjMB,
       SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalObjMB,
       SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStoreMB
FROM sys.dm_db_file_space_usage;

-- 5. 检查索引碎片（每周一次即可）
-- → 参考 4.4 节的碎片检测查询

-- 6. 检查统计信息更新情况
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatName,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    CAST(sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS ModPct
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0.2 * sp.rows  -- 修改超过20%触发重建
  AND sp.rows > 1000
ORDER BY ModPct DESC;

-- 7. 检查 Query Store 的计划回归
-- → 在 SSMS 中查看数据库的 Query Store 报表
-- → "回归查询" 报表直观显示性能下降的查询

-- 8. 数据库文件剩余空间告警
SELECT
    DB_NAME(database_id) AS DBName,
    name AS FileName,
    CAST(size * 8.0 / 1024 AS INT) AS FileSizeMB,
    CAST(FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 AS INT) AS UsedMB,
    CAST((size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS INT) AS FreeMB,
    CAST((size - FILEPROPERTY(name, 'SpaceUsed')) * 100.0 / NULLIF(size, 0) AS INT) AS FreePct
FROM sys.database_files
WHERE type = 0  -- 数据文件
HAVING CAST((size - FILEPROPERTY(name, 'SpaceUsed')) * 100.0 / NULLIF(size, 0) AS INT) < 20;
-- FreePct < 20% 时告警

9.7 小结

下一章：附录：常见报错与解决方案