做SQL Server运维这些年接到最多的需求就是“数据库最近好慢帮我看看”。但说实话慢是一个结果不是原因。真正该找的是导致这个结果的上游指标——是CPU被某个会话吃满了还是锁卡住了关键查询还是磁盘延迟已经高到读一个数据页都要几十毫秒。这篇文章我把SQL Server 性能监控里最值得盯的核心指标讲透从监控思路、每项指标的含义和阈值到能直接复制执行的采集脚本。适合两类人一类是被数据库慢问题反复折磨的开发与运维另一类是刚接触SQL Server、想建立完整监控体系的DBA。文中内容我按“先跑起来、再分析”的方式组织实测下来比抱着性能监视器盯半天要高效得多。1. 性能监控的整体思路先想清楚要盯什么1.1 性能监控到底在回答什么问题很多人一提监控就想到计数器、图表、告警但本质其实是在回答三个问题资源有没有瓶颈CPU 够不够内存够不够磁盘 IO 扛不扛得住。会话在等什么一个查询卡了它到底在等 CPU、等锁、等磁盘还是在等网络。查询是怎么跑的SQL 有没有被反复重编译执行计划是不是变质了缓存有没有被浪费。这三个问题正好对应监控的三个层面资源层、等待层、行为层。资源层告诉你“哪里紧张”等待层告诉你“卡在哪一环”行为层告诉你“为什么这么跑”。实际排查的时候三个层面要结合起来看只看任何一层都容易误判。举个例子CPU 跑到 90%你以为是某个大查询把 CPU 打满但打开等待统计一看大量会话堆在 LCK_M_X 上说明根本不是 CPU 不够而是锁把查询全堵住了被阻塞的会话在空转轮询CPU 才被拉高。这就是只盯资源不看等待的典型陷阱。1.2 计数器与 DMV两个入口怎么配合SQL Server 性能监控有两套主力入口各管一头。Windows 性能计数器Performance Monitor / typeperf看宏观趋势比如 CPU 使用率、内存可用字节、磁盘队列长度。它是采样型的适合看“过去一小时/一天整体压力有多大”也适合做历史对比。DMV动态管理视图Dynamic Management Views看微观现场。比如sys.dm_exec_requests告诉你当前正在跑的每一个请求、它的状态、等待类型、CPU 和 IO 消耗。sys.dm_os_wait_stats告诉你从实例启动到现在所有会话的等待时间累计。sys.dm_exec_query_stats能按 CPU 时间、逻辑读、耗时等维度排出 TOP SQL。两套入口怎么配合简单说计数器发现趋势DMV 定位根因。计数器告诉你 CPU 过去一小时平均 80%DMV 告诉你现在到底是哪个会话、哪条 SQL 在烧 CPU。我实际排查问题基本都是这个套路先看计数器缩小范围再用 DMV 一锤定音。1.3 没有基线的监控就是碰运气再往下聊之前必须先说基线这个概念。基线就是系统在“正常状态”下的指标值。没有基线你看到任何指标都判断不了是不是异常。同一个指标在不同环境下意义完全不同。Page Life Expectancy 这个值我后面会详细讲很多老资料说“低于 700 秒就该报警”但那是物理内存几十 GB 时代的标准。现在一台机器 256GB 内存、Buffer Pool 上百 GBPLE 低于 700 秒可能只是因为缓存太大、页被正常淘汰根本不代表内存压力。反过来一套只有 16GB 内存的老服务器PLE 跌到 300 秒以下那就是实打实的危险信号。所以我的习惯是系统正常的时候先把各项指标采集一周形成参考区间。以后再出问题拿当前值和基线对比而不是拿网上搜来的“标准阈值”硬套。这也是为什么我在第三章单独留了一节讲基线记录这是监控里最容易被跳过、也最值钱的习惯。2. 核心指标逐项拆解每一项到底在说什么2.1 CPU别只盯着百分比CPU 监控最少要看三个计数器\Processor(_Total)\% Processor Time、\SQLServer:SQL Statistics\SQL Compilations/sec、\SQLServer:SQL Statistics\SQL Re-Compilations/sec。处理器时间百分比大家都懂不用多说。关键是另外两个SQL Compilations/sec 表示每秒编译批处理的次数SQL Re-Compilations/sec 表示每秒重编译的次数。这两个值一旦持续飙升就是典型的“编译风暴”——系统在不停地生成执行计划CPU 全耗在编译上了真正跑查询反而没资源。我踩过一个很典型的坑某个业务系统一到上班高峰 CPU 就 100%但抓 TOP SQL 发现单条语句的 CPU 消耗并不高磁盘和内存也正常。后来把性能监视器打开发现 SQL Re-Compilations/sec 飙到每秒几百次。根因是代码里大量使用字符串拼接动态 SQL同一个逻辑因为参数格式不同产生无数种文本计划缓存根本没法复用。这个问题的监控信号就在编译/重编译计数器里光看 CPU 百分比是永远找不到答案的。定位 CPU 消耗最高的查询用这条语句最直接SELECT TOP 10 DB_NAME(st.dbid) AS DatabaseName, OBJECT_NAME(st.objectid, st.dbid) AS ObjectName, qs.total_worker_time / 1000 AS TotalWorkerTime_ms, qs.execution_count, qs.total_worker_time / qs.execution_count / 1000 AS AvgWorkerTime_ms, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.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) st ORDER BY qs.total_worker_time DESC;total_worker_time是 CPU 时间的累计值单位微秒除以 1000 转成毫秒。先看累计最多、平均最长两个维度基本能把问题 SQL 圈出来。2.2 内存从缓存命中率到页面寿命内存这一块网上文章最爱提Buffer cache hit ratio缓冲池命中率说这个值应该高于 95%。理论上没错但实际它的参考价值有限。SQL Server 有预读机制很多页在被查询主动访问之前就被后台线程提前读进内存了这些页面算命中时并不代表查询真的从缓存拿到了要的数据。所以命中率高不代表 IO 没压力。真正有价值的几个指标是这些Page Life ExpectancyPLE页预期寿命表示数据页在缓冲池里平均存活的秒数。参考值响应前面说的基线思路在老资料里是 700 秒现代大内存环境通常应该高得多。如果 PLE 持续偏低且不断下降说明内存压力明显页面刚读进来就被挤出去了。Memory Grants Pending内存授予等待数这个值只要持续大于 0就是实打实的内存压力。它表示有些查询请求内存做排序或者哈希连接但 SQL Server 已经拿不出内存给它们了。这个计数器只要非零且保持不降基本可以判断需要加内存或者控制并发。怎么看这两个值直接用系统视图查性能计数器SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ( Page life expectancy, Memory Grants Pending );另外还要关注 Target Server Memory 和 Total Server Memory。在 SQL Server 里Target Server Memory 是根据负载动态计算的目标内存Total Server Memory 是当前真正占用的内存。正常情况下 Total 会慢慢爬到 Target 附近稳定住。如果两者长期差得很远说明内存设置或压力出了问题。很多人看任务管理器发现“SQL Server Windows NT 占用内存好几百 GB”就紧张其实 SQL Server 的设计就是尽可能把内存拿去缓存数据页这是正常的。真正要担心的是 Target Server Memory 被 max server memory 卡得太死导致 Buffer Pool 挤不出空间给并发查询。这个配置在实例属性的“内存”页里生产环境一定要根据机器物理内存和同机其他应用的需求设一个合理的上限而不是放任不管。2.3 磁盘 IO延迟比使用率更值得看磁盘这块老派监控喜欢看% Disk Time但它在 Windows 上有明显的虚高问题参考意义很有限。我更建议看两个直接值\PhysicalDisk(_Total)\Avg. Disk sec/Read和\PhysicalDisk(_Total)\Avg. Disk sec/Write。这两个计数器直接告诉你每次磁盘读/写操作平均耗时多少秒。经验阈值是这样的机械硬盘读延迟在 10ms 以内算健康10-20ms 需要关注超过 20ms 就要查了写延迟对机械盘来说 5ms 以内算正常超过 10ms 需要注意。SSD 正常应该在 1-5ms 级别NVMe 更低。做到后面你会发现数据库性能出问题最常见的原因之一就是磁盘延迟高但服务器 CPU、内存都是绿的让人误判成“数据库需要优化”。更推荐的是直接用 DMV 按数据库文件看 IO 统计这样能区分是哪个库、哪个文件在拖后腿SELECT DB_NAME(mf.database_id) AS DatabaseName, mf.name AS FileName, mf.type_desc, io.num_of_reads, io.num_of_writes, io.io_stall_read_ms, io.io_stall_write_ms, io.num_of_bytes_read / 1024.0 / 1024.0 AS Read_MB, io.num_of_bytes_written / 1024.0 / 1024.0 AS Written_MB FROM sys.dm_io_virtual_file_stats(NULL, NULL) io JOIN sys.master_files mf ON mf.database_id io.database_id AND mf.file_id io.file_id ORDER BY io.io_stall_read_ms io.io_stall_write_ms DESC;这里io_stall_read_ms是累计的读等待总毫秒数除以num_of_reads能得到单次平均读延迟。用这个数据可以快速判断哪个文件的延迟最离谱。我实际遇到过一个系统数据文件和日志文件放在同一块机械盘上日志写入频繁导致数据文件读延迟也飙到 30ms 以上把事务日志单独挪到 SSD 后整体查询延迟立刻掉了一半。2.4 并发与阻塞性能问题的另一大来源并发问题有个特点症状是“慢”根源是“等锁”。所以除了看资源指标还要看这些并发相关指标User Connections用户连接数、Processes blocked被阻塞的进程数、Lock Waits/sec每秒锁等待次数、Number of Deadlocks/sec每秒死锁次数。User Connections 并不是越低越好也不是越高越糟核心是看它的趋势和连接池是否健康。Processes blocked 大于 0 就要警惕持续大于 5 基本可以确定有阻塞链了。定位阻塞链用这条查询能直接看到谁堵了谁SELECT blocked.session_id AS BlockedSessionID, blocked.blocking_session_id AS BlockingSessionID, DB_NAME(blocked.database_id) AS DatabaseName, blocked.wait_type, blocked.wait_time, blocked_text.text AS BlockedSQL, blocking_text.text AS BlockingSQL FROM sys.dm_exec_requests blocked OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text OUTER APPLY ( SELECT text FROM sys.dm_exec_sql_text( (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id blocked.blocking_session_id) ) ) blocking_text WHERE blocked.blocking_session_id 0;执行之后BlockingSessionID就是堵住别人的会话BlockingSQL就是它正在执行的事务。大部分阻塞问题看到这里就一目了然了要么是有人在事务里改了数据不提交要么是两个事务更新顺序不一致互相等待。死锁监控方面计数器Number of Deadlocks/sec一旦有波动就要重视。光知道有死锁还不够要拿到死锁详情建议打开跟踪标志 1222 和 1204把死锁信息记录到错误日志里DBCC TRACEON(1224, -1); DBCC TRACEON(1204, -1);注意 1222 和 1204 同时开启会重复记录一般二选一就够了。我习惯只开 1222它输出的死锁图信息更完整包含每个死锁会话的 SQL 文本和锁资源。真到分析死锁图的时候重点看两个会话的资源获取顺序是不是正好相反——这就是“两个事务按不同顺序更新多张表”的典型死锁模型。2.5 查询与缓存执行计划层面的监控资源层和等待层之外的第三个层面是查询行为本身。这里有两个容易被忽略的指标Plan Cache Hit Ratio计划缓存命中率和前面提过的编译/重编译计数器。计划缓存命中率如果持续低于 95%说明大量 SQL 是临时生成的动态语句执行计划无法复用每次都要重新编译。需要触发大量重编译的因素还包括临时表上的统计数据变化、索引重建、SET 选项不一致、参数嗅探等等。从 2016 版本开始SQL Server 自带的 Query Store查询存储是监控查询性能的利器。强烈建议把生产库都开启 Query Store它会自动记录每个查询的执行计划变化、执行次数、耗时、CPU、逻辑读的统计值还能直接定位到“执行计划漂移”导致的性能回退。开启方式很简单ALTER DATABASE [YourDatabase] SET QUERY_STORE ON; ALTER DATABASE [YourDatabase] SET QUERY_STORE ( OPERATION_MODE READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS 900, INTERVAL_LENGTH_MINUTES 60, MAX_STORAGE_SIZE_MB 512 );开启后定期用这段脚本看消耗最高的查询趋势SELECT TOP 10 qsqt.query_sql_text, qsrs.count_executions, qsrs.avg_duration, qsrs.avg_cpu_time, qsrs.avg_logical_io_reads FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsqt.query_text_id qsq.query_text_id JOIN sys.query_store_plan qsp ON qsp.query_id qsq.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id qsp.plan_id ORDER BY qsrs.avg_duration DESC;很多人以为 Query Store 只是 DBA 面板里的一个花哨功能但真正排查“昨天还快今天突然慢”这类问题时它能在几分钟内帮你定位是哪条 SQL 的执行计划变了比翻历史监控图高效太多。3. 落地实操可以直接复制的监控脚本与采集清单3.1 第一步等待统计体检等待统计是 SQL Server 性能监控里最有价值的一张表。它记录的是实例启动以来所有等待事件的累计时间。思路是SQL Server 的每个线程没有事情做的时候都是在等待某个资源把这些等待时间按类型汇总就能看清整个实例的时间都花在哪里。直接查询sys.dm_os_wait_stats是可以的但里面混了大量空闲等待类型比如 WAITFOR、BROKER_RECEIVE_WAITFOR、SLEEP_TASK 这些会干扰判断。所以至少在统计时要过滤掉它们。我实际在用的简化脚本是这条它能按累计等待时间自动过滤掉常见噪音等待类型WITH Waits AS ( SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS Percentage FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( NBROKER_EVENTHANDLER, NBROKER_RECEIVE_WAITFOR, NBROKER_TASK_STOP, NBROKER_TO_FLUSH, NBROKER_TRANSMITTER, NCHECKPOINT_QUEUE, NCHKPT, NCLR_AUTO_EVENT, NCLR_MANUAL_EVENT, NCLR_SEMAPHORE, NDBMIRROR_DBM_EVENT, NDBMIRROR_EVENTS_QUEUE, NDBMIRROR_WORKER_QUEUE, NDBMIRRORING_CMD, NDIRTY_PAGE_POLL, NDISPATCHER_QUEUE_SEMAPHORE, NEXECSYNC, NFSAGENT, NFT_DOCID_CACHE, NFT_MASTER_MERGE, NFT_NEED_CRAWLS, NHADR_FILESTREAM_IOMGR_IOCOMPLETION, NHADR_LOGCAPTURE_WAIT, NHADR_NOTIFICATION_DEQUEUE, NHADR_TIMER_TASK, NHADR_WORK_QUEUE, NKSOURCE_WAKEUP, NLAZYWRITER_SLEEP, NLOGMGR_QUEUE, NMEMORY_ALLOCATION_EXT, NONDEMAND_TASK_QUEUE, NPREEMPTIVE_OS_LIBRARYOPS, NPREEMPTIVE_OS_COMOPS, NPREEMPTIVE_OS_CRYPTOPS, NPREEMPTIVE_OS_FLUSHFILE, NPREEMPTIVE_OS_GENERICOPS, NPREEMPTIVE_OS_QUEUEOP, NPREEMPTIVE_OS_QUERYREGISTRY, NPREEMPTIVE_OS_RACOP, NPREEMPTIVE_OS_RECOVERYOPS, NPREEMPTIVE_OS_SNAPSHOTOPS, NPREEMPTIVE_OS_SWCENUM, NPREEMPTIVE_OS_WRITEFILE, NPREEMPTIVE_XE_CALLBACKEXECUTE, NPREEMPTIVE_XE_DISPATCHER, NPREEMPTIVE_XE_GETTARGETSTATE, NPREEMPTIVE_XE_SESSIONCOMMIT, NPREEMPTIVE_XE_TARGETINIT, NPREEMPTIVE_XE_TARGETSHUTDOWN, NPWAIT_ALL_COMPONENTS_INITIALIZED, NQDS_PERSIST_TASK_MAIN_LOOP_SLEEP, NQDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP, NREQUEST_FOR_DEADLOCK_SEARCH, NRESOURCE_QUEUE, NSERVER_IDLE_CHECK, NSLEEP_BPOOL_MAINTENANCE, NSLEEP_DBSTARTUP, NSLEEP_DCOMSTARTUP, NSLEEP_MASTERDBREADY, NSLEEP_MASTERMDREADY, NSLEEP_MASTERUPGRADED, NSLEEP_MSDBSTARTUP, NSLEEP_SYSTEMSTARTED, NSLEEP_TASK, NSLEEP_TEMPDBSTARTUP, NSNI_HTTP_ACCEPT, NSP_SERVER_DIAGNOSTICS_SLEEP, NSQLTRACE_BUFFER_FLUSH, NSQLTRACE_INCREMENTAL_FLUSH_SLEEP, NSQLTRACE_WAIT_ENTRIES, NWAIT_FOR_RESULTS, NWAITFOR, NWAITFOR_TASKSHUTDOWN, NWAIT_XTP_RECOVERY, NWAIT_XTP_HOST_WAIT, NWAIT_XTP_OFFLINE_CKPT_NEW_LOG, NWAIT_XTP_CKPT_CLOSE, NXE_DISPATCHER_JOIN, NXE_DISPATCHER_WAIT, NXE_TIMER_EVENT ) ) SELECT wait_type AS WaitType, CAST(WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST(ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST(SignalS AS DECIMAL(14, 2)) AS Signal_S, WaitCount, CAST(Percentage AS DECIMAL(4, 2)) AS Percentage FROM Waits WHERE Percentage 0.5 ORDER BY WaitS DESC;看结果的时候重点找这几个等待类型PAGEIOLATCH_SH/PAGEIOLATCH_EX等磁盘把数据页读进内存或写进内存高则说明 IO 延迟大。LCK_M_X等系列等锁释放说明有阻塞。WRITELOG等事务日志刷盘说明日志盘写入太慢。CXPACKET并行查询的线程等待高不一定是坏事要看是否伴随 CPU 压力。ASYNC_NETWORK_IO等客户端接收数据很多情况下是应用端处理慢不一定是数据库问题。注意一点这个表是实例启动到现在的累计值。想判断最近一段时间的趋势要记录两个时间点的差值而不是直接拿一次查询结果下结论。3.2 第二步正在运行的请求与 TOP SQL等待统计告诉我们整体方向但实际动手时还要抓出“当下正在跑什么”。看活动请求用这条SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time, r.cpu_time, r.total_elapsed_time, DB_NAME(r.database_id) AS DatabaseName, SUBSTRING(t.text, (r.statement_start_offset / 2) 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) 1) AS QueryText FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id 50 ORDER BY r.total_elapsed_time DESC;total_elapsed_time是按毫秒算的已执行时长。一般把结果按它排序排在前面基本就是正在“卡”的查询。结合wait_type一眼就能看出它在等什么。如果压测或线上突然有大查询冒出来还想看历史累计的 TOP SQL就按 CPU 时间排SELECT TOP 20 qs.total_worker_time / 1000 AS TotalWorkerTime_ms, qs.total_elapsed_time / 1000 AS TotalElapsedTime_ms, qs.execution_count, qs.total_logical_reads, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.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) st ORDER BY qs.total_worker_time DESC;这里注意sys.dm_exec_query_stats存的是计划缓存里的语句级统计缓存被内存压力挤掉之后历史数据就清了。所以这条更适合在问题发生当下或问题结束后尽快查。3.3 第三步性能计数器采集清单把零散的监控点位整理成清单我日常巡检主要看下面这些全部来自 Windows 性能监视器性能对象计数器建议观测频率关注阈值与信号Processor% Processor Time1 分钟一次长期高于 80% 且有伴随等待需要深挖SQL Server SQL StatisticsCompilations/sec1 分钟一次持续高于 100 且 CPU 高疑似编译风暴SQL Server SQL StatisticsRe-Compilations/sec1 分钟一次同上SQL Server Buffer ManagerBuffer cache hit ratio5 分钟一次低于 95% 时需要结合 PLE 判断SQL Server Buffer ManagerPage life expectancy5 分钟一次与基线对比持续下降是内存压力信号SQL Server Memory ManagerMemory Grants Pending5 分钟一次持续大于 0内存分配出现等待SQL Server General StatisticsUser Connections5 分钟一次关注趋势和峰值异常的陡增陡降要查SQL Server General StatisticsProcesses blocked1 分钟一次持续大于 0 就是有阻塞SQL Server LocksLock Waits/sec1 分钟一次持续升高说明锁竞争严重SQL Server LocksNumber of Deadlocks/sec1 分钟一次任何大于 0 的持续值都要查PhysicalDiskAvg. Disk sec/Read1 分钟一次机械盘大于 20ms 有风险SSD 大于 10ms 要查PhysicalDiskAvg. Disk sec/Write1 分钟一次同上采集不一定要用 GUI 的性能监视器慢慢看命令行更省事可以用 typeperf 定时拉取到 CSV 文件typeperf \Processor(_Total)\% Processor Time \Memory\Available MBytes \PhysicalDisk(_Total)\Avg. Disk sec/Read \PhysicalDisk(_Total)\Avg. Disk sec/Write -o perf_%date:~0,4%%date:~5,2%%date:~8,2%.csv -sc 600 -f csv-sc 600表示采样 600 次默认间隔一秒也就是 10 分钟的数据量。需要按分钟级采样的话可以加-si 60指定间隔 60 秒。导出的 CSV 可以直接丢进 Excel 画趋势线。命令行输计数器名时注意用英文名称中文版 Windows 在部分版本上识别英文名没问题但如果系统语言不同建议先在性能监视器里确认一下实际名称。3.4 第四步定期巡检与基线记录监控不是出问题时才做的动作更要靠日常积累。我个人的做法是每周做一次轻量巡检每次记录以下信息关键计数器上面那张表的全部项目的峰值和平均值等待统计 TOP 5 等待类型的差值快照错误日志里 severity 大于等于 17 的错误数量SQL Agent 作业中失败的任务备份任务是否按时成功数据库文件的增长情况和下一次自动增长预估这些信息汇总到一张表里按月对比。这样系统变慢时你能立刻知道“磁盘延迟是从这周开始升高的”还是“连接数比上周同期多了三倍”直接缩小排查范围。基线记录的脚本可以很简单不需要额外买监控软件。把上面 3.1-3.3 的查询结果导出保存下来就行。关键是持之以恒最好固定在每周同一天、同一个业务低峰时段做这样数据才有可比性。4. 常见问题与排查思路实录4.1 CPU 飙高却找不到大查询重点看这两处完善的第一反应是抓 TOP SQL但有的时候 CPU 确实飙到了 90%sys.dm_exec_query_stats按 CPU 排出来的语句却看起来很平庸。这时候优先去查编译/重编译计数器的趋势。一次真实的排查经历客户说有台 SQL Server 每天十点半 CPU 准时爆表持续半小时后自动恢复。我上去抓 TOP SQL没有任何一条语句消耗能解释这么高的 CPU。后来看性能计数器Compilations/sec 在十点半突然从几十飙到几千。再结合代码审查发现当时有一个定时任务在大量拼接 SQL 并清空计划缓存导致所有后续查询全部重新编译。定位到问题后把定时任务改成参数化查询CPU 立刻回归正常。第二处容易被忽略的是统计信息更新和索引维护。如果运维自动化作业里每天固定时间点对大表做UPDATE STATISTICS或者重建索引CPU 飙高就是正常的维护窗口开销不算性能问题但一定要和业务时间错开否则会互相拖累。4.2 任务管理器里“SQL Server 占用内存”怎么解释这是 SQL Server 新手最容易误判的点。只要 max server memory 没有设置SQL Server 就会尽量把物理内存吃进 Buffer Pool 当缓存任务管理器看到的内存占用高不代表内存泄漏。真正的内存压力要结合这几个信号判断Target Server Memory 与 Total Server Memory 的长期差值。如果 Target 远大于 Total说明 SQL Server 因为外部限制max server memory或驱动问题无法扩展内存内存分配会变慢。Memory Grants Pending 持续大于 0说明有些查询在排队等内存。PLE 持续低于低位并伴随 Page Reads/sec 高企说明缓存无法容纳工作集每次查询都要走磁盘。如果看到这些信号优先检查 max server memory 配置给操作系统留出 2-4GB 或总量的 5% 内存再考虑是否要加物理内存。不要一看到任务管理器内存高就去限制 max server memory那等于把 SQL Server 的缓存能力废掉一半。4.3 磁盘延迟高但业务体验不明显可能问题在日志盘有次帮一个系统看 IO数据盘延迟在 5ms 以内非常健康但 WRITELOG 等待类型长期排在 TOP 3。点开虚拟文件统计一看事务日志文件的平均写延迟高达 25ms。业务方说“也没有特别明显的卡顿”但实际上所有写事务都在排队刷日志只是量没到临界点而已。事务日志的特点是顺序写但每次提交事务都要等日志落盘才能返回。日志盘的延迟直接决定写事务的响应时间。所以如果WRITELOG等待时间占比高优先检查日志文件所在的物理磁盘而不是去优化 SQL。很多团队习惯把所有文件都丢到一个盘上数据读 IO 和日志写 IO 互相干扰把日志文件单独放到 SSD 上通常立竿见影。同理tempdb 也非常吃 IO并发高的时候和用户库抢磁盘也会拖慢全局性能。4.4 阻塞与死锁的实战排查阻塞最典型的现场是某个后台事务开了事务更新了几行数据但没有提交也没有回滚直接挂机。后面所有要读这几行的查询全部排在LCK_M_X等待上。用 2.4 那两条阻塞查询脚本可以立刻看到BlockingSessionID和它的 SQL 文本。处理方式一般就是和业务确认后杀掉阻塞头会话同时推动代码层把事务尽量做短、统一提交顺序。死锁则不同它不会一直阻塞而是直接报错让其中一个事务回滚。如果业务侧频繁报“事务死锁”就按 2.4 说的打开 1222 跟踪标志然后去错误日志里翻死锁图。调试时多看两个细节第一两个死锁会话分别以什么顺序访问哪些对象第二死锁涉及的索引是不是同一个。前者如果是反序更新改代码统一访问顺序后者可以靠调整索引或加 with (rowlock) 等手段降低锁粒度。4.5 问题速查表最后把常见的症状、要看的指标和对应方向整理成一张速查表排查时对照着看症状优先查看的指标/视图常见指向下一步动作CPU 整体飙高% Processor Time、Compilations/sec大查询或编译风暴查 TOP SQL分析执行计划单个查询一直跑不完sys.dm_exec_requests 的 wait_type锁等待、IO 等待、并行倾斜按等待类型进入对应排查流程缓存命中率低但内存充足PLE、Memory Grants Pending内存配置受限或工作集过大检查 max server memory 和内存信号写事务都慢Avg. Disk sec/Write、WRITELOG日志盘延迟高迁移日志文件到低延迟磁盘业务偶发超时Number of Deadlocks/sec、错误日志死锁开启 1222分析死锁图连接数异常增长User Connections、sys.dm_exec_sessions连接泄漏或应用并发突增查应用连接池设置和活动会话来源之前快后来慢Query Store、执行计划缓存计划漂移/统计信息变化用 Query Store 对比新旧计划个人经验里最重要的一点监控工具的指标不能贪多。我最早恨不得把一百多个计数器全拉出来看最后什么都看不出问题。后来养成的习惯是每次只先看等待统计知道 SQL Server 到底“等”在什么地方再往下钻取对应的资源指标和 SQL 文本。在系统正常的时候先把基线记录好等出状况时才有参照物。没有基线的性能优化基本就是碰运气。
SQL Server性能监控核心指标与排查实战：CPU、内存、IO、阻塞一次讲透
做SQL Server运维这些年接到最多的需求就是“数据库最近好慢帮我看看”。但说实话慢是一个结果不是原因。真正该找的是导致这个结果的上游指标——是CPU被某个会话吃满了还是锁卡住了关键查询还是磁盘延迟已经高到读一个数据页都要几十毫秒。这篇文章我把SQL Server 性能监控里最值得盯的核心指标讲透从监控思路、每项指标的含义和阈值到能直接复制执行的采集脚本。适合两类人一类是被数据库慢问题反复折磨的开发与运维另一类是刚接触SQL Server、想建立完整监控体系的DBA。文中内容我按“先跑起来、再分析”的方式组织实测下来比抱着性能监视器盯半天要高效得多。1. 性能监控的整体思路先想清楚要盯什么1.1 性能监控到底在回答什么问题很多人一提监控就想到计数器、图表、告警但本质其实是在回答三个问题资源有没有瓶颈CPU 够不够内存够不够磁盘 IO 扛不扛得住。会话在等什么一个查询卡了它到底在等 CPU、等锁、等磁盘还是在等网络。查询是怎么跑的SQL 有没有被反复重编译执行计划是不是变质了缓存有没有被浪费。这三个问题正好对应监控的三个层面资源层、等待层、行为层。资源层告诉你“哪里紧张”等待层告诉你“卡在哪一环”行为层告诉你“为什么这么跑”。实际排查的时候三个层面要结合起来看只看任何一层都容易误判。举个例子CPU 跑到 90%你以为是某个大查询把 CPU 打满但打开等待统计一看大量会话堆在 LCK_M_X 上说明根本不是 CPU 不够而是锁把查询全堵住了被阻塞的会话在空转轮询CPU 才被拉高。这就是只盯资源不看等待的典型陷阱。1.2 计数器与 DMV两个入口怎么配合SQL Server 性能监控有两套主力入口各管一头。Windows 性能计数器Performance Monitor / typeperf看宏观趋势比如 CPU 使用率、内存可用字节、磁盘队列长度。它是采样型的适合看“过去一小时/一天整体压力有多大”也适合做历史对比。DMV动态管理视图Dynamic Management Views看微观现场。比如sys.dm_exec_requests告诉你当前正在跑的每一个请求、它的状态、等待类型、CPU 和 IO 消耗。sys.dm_os_wait_stats告诉你从实例启动到现在所有会话的等待时间累计。sys.dm_exec_query_stats能按 CPU 时间、逻辑读、耗时等维度排出 TOP SQL。两套入口怎么配合简单说计数器发现趋势DMV 定位根因。计数器告诉你 CPU 过去一小时平均 80%DMV 告诉你现在到底是哪个会话、哪条 SQL 在烧 CPU。我实际排查问题基本都是这个套路先看计数器缩小范围再用 DMV 一锤定音。1.3 没有基线的监控就是碰运气再往下聊之前必须先说基线这个概念。基线就是系统在“正常状态”下的指标值。没有基线你看到任何指标都判断不了是不是异常。同一个指标在不同环境下意义完全不同。Page Life Expectancy 这个值我后面会详细讲很多老资料说“低于 700 秒就该报警”但那是物理内存几十 GB 时代的标准。现在一台机器 256GB 内存、Buffer Pool 上百 GBPLE 低于 700 秒可能只是因为缓存太大、页被正常淘汰根本不代表内存压力。反过来一套只有 16GB 内存的老服务器PLE 跌到 300 秒以下那就是实打实的危险信号。所以我的习惯是系统正常的时候先把各项指标采集一周形成参考区间。以后再出问题拿当前值和基线对比而不是拿网上搜来的“标准阈值”硬套。这也是为什么我在第三章单独留了一节讲基线记录这是监控里最容易被跳过、也最值钱的习惯。2. 核心指标逐项拆解每一项到底在说什么2.1 CPU别只盯着百分比CPU 监控最少要看三个计数器\Processor(_Total)\% Processor Time、\SQLServer:SQL Statistics\SQL Compilations/sec、\SQLServer:SQL Statistics\SQL Re-Compilations/sec。处理器时间百分比大家都懂不用多说。关键是另外两个SQL Compilations/sec 表示每秒编译批处理的次数SQL Re-Compilations/sec 表示每秒重编译的次数。这两个值一旦持续飙升就是典型的“编译风暴”——系统在不停地生成执行计划CPU 全耗在编译上了真正跑查询反而没资源。我踩过一个很典型的坑某个业务系统一到上班高峰 CPU 就 100%但抓 TOP SQL 发现单条语句的 CPU 消耗并不高磁盘和内存也正常。后来把性能监视器打开发现 SQL Re-Compilations/sec 飙到每秒几百次。根因是代码里大量使用字符串拼接动态 SQL同一个逻辑因为参数格式不同产生无数种文本计划缓存根本没法复用。这个问题的监控信号就在编译/重编译计数器里光看 CPU 百分比是永远找不到答案的。定位 CPU 消耗最高的查询用这条语句最直接SELECT TOP 10 DB_NAME(st.dbid) AS DatabaseName, OBJECT_NAME(st.objectid, st.dbid) AS ObjectName, qs.total_worker_time / 1000 AS TotalWorkerTime_ms, qs.execution_count, qs.total_worker_time / qs.execution_count / 1000 AS AvgWorkerTime_ms, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.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) st ORDER BY qs.total_worker_time DESC;total_worker_time是 CPU 时间的累计值单位微秒除以 1000 转成毫秒。先看累计最多、平均最长两个维度基本能把问题 SQL 圈出来。2.2 内存从缓存命中率到页面寿命内存这一块网上文章最爱提Buffer cache hit ratio缓冲池命中率说这个值应该高于 95%。理论上没错但实际它的参考价值有限。SQL Server 有预读机制很多页在被查询主动访问之前就被后台线程提前读进内存了这些页面算命中时并不代表查询真的从缓存拿到了要的数据。所以命中率高不代表 IO 没压力。真正有价值的几个指标是这些Page Life ExpectancyPLE页预期寿命表示数据页在缓冲池里平均存活的秒数。参考值响应前面说的基线思路在老资料里是 700 秒现代大内存环境通常应该高得多。如果 PLE 持续偏低且不断下降说明内存压力明显页面刚读进来就被挤出去了。Memory Grants Pending内存授予等待数这个值只要持续大于 0就是实打实的内存压力。它表示有些查询请求内存做排序或者哈希连接但 SQL Server 已经拿不出内存给它们了。这个计数器只要非零且保持不降基本可以判断需要加内存或者控制并发。怎么看这两个值直接用系统视图查性能计数器SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ( Page life expectancy, Memory Grants Pending );另外还要关注 Target Server Memory 和 Total Server Memory。在 SQL Server 里Target Server Memory 是根据负载动态计算的目标内存Total Server Memory 是当前真正占用的内存。正常情况下 Total 会慢慢爬到 Target 附近稳定住。如果两者长期差得很远说明内存设置或压力出了问题。很多人看任务管理器发现“SQL Server Windows NT 占用内存好几百 GB”就紧张其实 SQL Server 的设计就是尽可能把内存拿去缓存数据页这是正常的。真正要担心的是 Target Server Memory 被 max server memory 卡得太死导致 Buffer Pool 挤不出空间给并发查询。这个配置在实例属性的“内存”页里生产环境一定要根据机器物理内存和同机其他应用的需求设一个合理的上限而不是放任不管。2.3 磁盘 IO延迟比使用率更值得看磁盘这块老派监控喜欢看% Disk Time但它在 Windows 上有明显的虚高问题参考意义很有限。我更建议看两个直接值\PhysicalDisk(_Total)\Avg. Disk sec/Read和\PhysicalDisk(_Total)\Avg. Disk sec/Write。这两个计数器直接告诉你每次磁盘读/写操作平均耗时多少秒。经验阈值是这样的机械硬盘读延迟在 10ms 以内算健康10-20ms 需要关注超过 20ms 就要查了写延迟对机械盘来说 5ms 以内算正常超过 10ms 需要注意。SSD 正常应该在 1-5ms 级别NVMe 更低。做到后面你会发现数据库性能出问题最常见的原因之一就是磁盘延迟高但服务器 CPU、内存都是绿的让人误判成“数据库需要优化”。更推荐的是直接用 DMV 按数据库文件看 IO 统计这样能区分是哪个库、哪个文件在拖后腿SELECT DB_NAME(mf.database_id) AS DatabaseName, mf.name AS FileName, mf.type_desc, io.num_of_reads, io.num_of_writes, io.io_stall_read_ms, io.io_stall_write_ms, io.num_of_bytes_read / 1024.0 / 1024.0 AS Read_MB, io.num_of_bytes_written / 1024.0 / 1024.0 AS Written_MB FROM sys.dm_io_virtual_file_stats(NULL, NULL) io JOIN sys.master_files mf ON mf.database_id io.database_id AND mf.file_id io.file_id ORDER BY io.io_stall_read_ms io.io_stall_write_ms DESC;这里io_stall_read_ms是累计的读等待总毫秒数除以num_of_reads能得到单次平均读延迟。用这个数据可以快速判断哪个文件的延迟最离谱。我实际遇到过一个系统数据文件和日志文件放在同一块机械盘上日志写入频繁导致数据文件读延迟也飙到 30ms 以上把事务日志单独挪到 SSD 后整体查询延迟立刻掉了一半。2.4 并发与阻塞性能问题的另一大来源并发问题有个特点症状是“慢”根源是“等锁”。所以除了看资源指标还要看这些并发相关指标User Connections用户连接数、Processes blocked被阻塞的进程数、Lock Waits/sec每秒锁等待次数、Number of Deadlocks/sec每秒死锁次数。User Connections 并不是越低越好也不是越高越糟核心是看它的趋势和连接池是否健康。Processes blocked 大于 0 就要警惕持续大于 5 基本可以确定有阻塞链了。定位阻塞链用这条查询能直接看到谁堵了谁SELECT blocked.session_id AS BlockedSessionID, blocked.blocking_session_id AS BlockingSessionID, DB_NAME(blocked.database_id) AS DatabaseName, blocked.wait_type, blocked.wait_time, blocked_text.text AS BlockedSQL, blocking_text.text AS BlockingSQL FROM sys.dm_exec_requests blocked OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text OUTER APPLY ( SELECT text FROM sys.dm_exec_sql_text( (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id blocked.blocking_session_id) ) ) blocking_text WHERE blocked.blocking_session_id 0;执行之后BlockingSessionID就是堵住别人的会话BlockingSQL就是它正在执行的事务。大部分阻塞问题看到这里就一目了然了要么是有人在事务里改了数据不提交要么是两个事务更新顺序不一致互相等待。死锁监控方面计数器Number of Deadlocks/sec一旦有波动就要重视。光知道有死锁还不够要拿到死锁详情建议打开跟踪标志 1222 和 1204把死锁信息记录到错误日志里DBCC TRACEON(1224, -1); DBCC TRACEON(1204, -1);注意 1222 和 1204 同时开启会重复记录一般二选一就够了。我习惯只开 1222它输出的死锁图信息更完整包含每个死锁会话的 SQL 文本和锁资源。真到分析死锁图的时候重点看两个会话的资源获取顺序是不是正好相反——这就是“两个事务按不同顺序更新多张表”的典型死锁模型。2.5 查询与缓存执行计划层面的监控资源层和等待层之外的第三个层面是查询行为本身。这里有两个容易被忽略的指标Plan Cache Hit Ratio计划缓存命中率和前面提过的编译/重编译计数器。计划缓存命中率如果持续低于 95%说明大量 SQL 是临时生成的动态语句执行计划无法复用每次都要重新编译。需要触发大量重编译的因素还包括临时表上的统计数据变化、索引重建、SET 选项不一致、参数嗅探等等。从 2016 版本开始SQL Server 自带的 Query Store查询存储是监控查询性能的利器。强烈建议把生产库都开启 Query Store它会自动记录每个查询的执行计划变化、执行次数、耗时、CPU、逻辑读的统计值还能直接定位到“执行计划漂移”导致的性能回退。开启方式很简单ALTER DATABASE [YourDatabase] SET QUERY_STORE ON; ALTER DATABASE [YourDatabase] SET QUERY_STORE ( OPERATION_MODE READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS 900, INTERVAL_LENGTH_MINUTES 60, MAX_STORAGE_SIZE_MB 512 );开启后定期用这段脚本看消耗最高的查询趋势SELECT TOP 10 qsqt.query_sql_text, qsrs.count_executions, qsrs.avg_duration, qsrs.avg_cpu_time, qsrs.avg_logical_io_reads FROM sys.query_store_query qsq JOIN sys.query_store_query_text qsqt ON qsqt.query_text_id qsq.query_text_id JOIN sys.query_store_plan qsp ON qsp.query_id qsq.query_id JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id qsp.plan_id ORDER BY qsrs.avg_duration DESC;很多人以为 Query Store 只是 DBA 面板里的一个花哨功能但真正排查“昨天还快今天突然慢”这类问题时它能在几分钟内帮你定位是哪条 SQL 的执行计划变了比翻历史监控图高效太多。3. 落地实操可以直接复制的监控脚本与采集清单3.1 第一步等待统计体检等待统计是 SQL Server 性能监控里最有价值的一张表。它记录的是实例启动以来所有等待事件的累计时间。思路是SQL Server 的每个线程没有事情做的时候都是在等待某个资源把这些等待时间按类型汇总就能看清整个实例的时间都花在哪里。直接查询sys.dm_os_wait_stats是可以的但里面混了大量空闲等待类型比如 WAITFOR、BROKER_RECEIVE_WAITFOR、SLEEP_TASK 这些会干扰判断。所以至少在统计时要过滤掉它们。我实际在用的简化脚本是这条它能按累计等待时间自动过滤掉常见噪音等待类型WITH Waits AS ( SELECT wait_type, wait_time_ms / 1000.0 AS WaitS, (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS, signal_wait_time_ms / 1000.0 AS SignalS, waiting_tasks_count AS WaitCount, 100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS Percentage FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( NBROKER_EVENTHANDLER, NBROKER_RECEIVE_WAITFOR, NBROKER_TASK_STOP, NBROKER_TO_FLUSH, NBROKER_TRANSMITTER, NCHECKPOINT_QUEUE, NCHKPT, NCLR_AUTO_EVENT, NCLR_MANUAL_EVENT, NCLR_SEMAPHORE, NDBMIRROR_DBM_EVENT, NDBMIRROR_EVENTS_QUEUE, NDBMIRROR_WORKER_QUEUE, NDBMIRRORING_CMD, NDIRTY_PAGE_POLL, NDISPATCHER_QUEUE_SEMAPHORE, NEXECSYNC, NFSAGENT, NFT_DOCID_CACHE, NFT_MASTER_MERGE, NFT_NEED_CRAWLS, NHADR_FILESTREAM_IOMGR_IOCOMPLETION, NHADR_LOGCAPTURE_WAIT, NHADR_NOTIFICATION_DEQUEUE, NHADR_TIMER_TASK, NHADR_WORK_QUEUE, NKSOURCE_WAKEUP, NLAZYWRITER_SLEEP, NLOGMGR_QUEUE, NMEMORY_ALLOCATION_EXT, NONDEMAND_TASK_QUEUE, NPREEMPTIVE_OS_LIBRARYOPS, NPREEMPTIVE_OS_COMOPS, NPREEMPTIVE_OS_CRYPTOPS, NPREEMPTIVE_OS_FLUSHFILE, NPREEMPTIVE_OS_GENERICOPS, NPREEMPTIVE_OS_QUEUEOP, NPREEMPTIVE_OS_QUERYREGISTRY, NPREEMPTIVE_OS_RACOP, NPREEMPTIVE_OS_RECOVERYOPS, NPREEMPTIVE_OS_SNAPSHOTOPS, NPREEMPTIVE_OS_SWCENUM, NPREEMPTIVE_OS_WRITEFILE, NPREEMPTIVE_XE_CALLBACKEXECUTE, NPREEMPTIVE_XE_DISPATCHER, NPREEMPTIVE_XE_GETTARGETSTATE, NPREEMPTIVE_XE_SESSIONCOMMIT, NPREEMPTIVE_XE_TARGETINIT, NPREEMPTIVE_XE_TARGETSHUTDOWN, NPWAIT_ALL_COMPONENTS_INITIALIZED, NQDS_PERSIST_TASK_MAIN_LOOP_SLEEP, NQDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP, NREQUEST_FOR_DEADLOCK_SEARCH, NRESOURCE_QUEUE, NSERVER_IDLE_CHECK, NSLEEP_BPOOL_MAINTENANCE, NSLEEP_DBSTARTUP, NSLEEP_DCOMSTARTUP, NSLEEP_MASTERDBREADY, NSLEEP_MASTERMDREADY, NSLEEP_MASTERUPGRADED, NSLEEP_MSDBSTARTUP, NSLEEP_SYSTEMSTARTED, NSLEEP_TASK, NSLEEP_TEMPDBSTARTUP, NSNI_HTTP_ACCEPT, NSP_SERVER_DIAGNOSTICS_SLEEP, NSQLTRACE_BUFFER_FLUSH, NSQLTRACE_INCREMENTAL_FLUSH_SLEEP, NSQLTRACE_WAIT_ENTRIES, NWAIT_FOR_RESULTS, NWAITFOR, NWAITFOR_TASKSHUTDOWN, NWAIT_XTP_RECOVERY, NWAIT_XTP_HOST_WAIT, NWAIT_XTP_OFFLINE_CKPT_NEW_LOG, NWAIT_XTP_CKPT_CLOSE, NXE_DISPATCHER_JOIN, NXE_DISPATCHER_WAIT, NXE_TIMER_EVENT ) ) SELECT wait_type AS WaitType, CAST(WaitS AS DECIMAL(14, 2)) AS Wait_S, CAST(ResourceS AS DECIMAL(14, 2)) AS Resource_S, CAST(SignalS AS DECIMAL(14, 2)) AS Signal_S, WaitCount, CAST(Percentage AS DECIMAL(4, 2)) AS Percentage FROM Waits WHERE Percentage 0.5 ORDER BY WaitS DESC;看结果的时候重点找这几个等待类型PAGEIOLATCH_SH/PAGEIOLATCH_EX等磁盘把数据页读进内存或写进内存高则说明 IO 延迟大。LCK_M_X等系列等锁释放说明有阻塞。WRITELOG等事务日志刷盘说明日志盘写入太慢。CXPACKET并行查询的线程等待高不一定是坏事要看是否伴随 CPU 压力。ASYNC_NETWORK_IO等客户端接收数据很多情况下是应用端处理慢不一定是数据库问题。注意一点这个表是实例启动到现在的累计值。想判断最近一段时间的趋势要记录两个时间点的差值而不是直接拿一次查询结果下结论。3.2 第二步正在运行的请求与 TOP SQL等待统计告诉我们整体方向但实际动手时还要抓出“当下正在跑什么”。看活动请求用这条SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time, r.cpu_time, r.total_elapsed_time, DB_NAME(r.database_id) AS DatabaseName, SUBSTRING(t.text, (r.statement_start_offset / 2) 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) 1) AS QueryText FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id 50 ORDER BY r.total_elapsed_time DESC;total_elapsed_time是按毫秒算的已执行时长。一般把结果按它排序排在前面基本就是正在“卡”的查询。结合wait_type一眼就能看出它在等什么。如果压测或线上突然有大查询冒出来还想看历史累计的 TOP SQL就按 CPU 时间排SELECT TOP 20 qs.total_worker_time / 1000 AS TotalWorkerTime_ms, qs.total_elapsed_time / 1000 AS TotalElapsedTime_ms, qs.execution_count, qs.total_logical_reads, SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.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) st ORDER BY qs.total_worker_time DESC;这里注意sys.dm_exec_query_stats存的是计划缓存里的语句级统计缓存被内存压力挤掉之后历史数据就清了。所以这条更适合在问题发生当下或问题结束后尽快查。3.3 第三步性能计数器采集清单把零散的监控点位整理成清单我日常巡检主要看下面这些全部来自 Windows 性能监视器性能对象计数器建议观测频率关注阈值与信号Processor% Processor Time1 分钟一次长期高于 80% 且有伴随等待需要深挖SQL Server SQL StatisticsCompilations/sec1 分钟一次持续高于 100 且 CPU 高疑似编译风暴SQL Server SQL StatisticsRe-Compilations/sec1 分钟一次同上SQL Server Buffer ManagerBuffer cache hit ratio5 分钟一次低于 95% 时需要结合 PLE 判断SQL Server Buffer ManagerPage life expectancy5 分钟一次与基线对比持续下降是内存压力信号SQL Server Memory ManagerMemory Grants Pending5 分钟一次持续大于 0内存分配出现等待SQL Server General StatisticsUser Connections5 分钟一次关注趋势和峰值异常的陡增陡降要查SQL Server General StatisticsProcesses blocked1 分钟一次持续大于 0 就是有阻塞SQL Server LocksLock Waits/sec1 分钟一次持续升高说明锁竞争严重SQL Server LocksNumber of Deadlocks/sec1 分钟一次任何大于 0 的持续值都要查PhysicalDiskAvg. Disk sec/Read1 分钟一次机械盘大于 20ms 有风险SSD 大于 10ms 要查PhysicalDiskAvg. Disk sec/Write1 分钟一次同上采集不一定要用 GUI 的性能监视器慢慢看命令行更省事可以用 typeperf 定时拉取到 CSV 文件typeperf \Processor(_Total)\% Processor Time \Memory\Available MBytes \PhysicalDisk(_Total)\Avg. Disk sec/Read \PhysicalDisk(_Total)\Avg. Disk sec/Write -o perf_%date:~0,4%%date:~5,2%%date:~8,2%.csv -sc 600 -f csv-sc 600表示采样 600 次默认间隔一秒也就是 10 分钟的数据量。需要按分钟级采样的话可以加-si 60指定间隔 60 秒。导出的 CSV 可以直接丢进 Excel 画趋势线。命令行输计数器名时注意用英文名称中文版 Windows 在部分版本上识别英文名没问题但如果系统语言不同建议先在性能监视器里确认一下实际名称。3.4 第四步定期巡检与基线记录监控不是出问题时才做的动作更要靠日常积累。我个人的做法是每周做一次轻量巡检每次记录以下信息关键计数器上面那张表的全部项目的峰值和平均值等待统计 TOP 5 等待类型的差值快照错误日志里 severity 大于等于 17 的错误数量SQL Agent 作业中失败的任务备份任务是否按时成功数据库文件的增长情况和下一次自动增长预估这些信息汇总到一张表里按月对比。这样系统变慢时你能立刻知道“磁盘延迟是从这周开始升高的”还是“连接数比上周同期多了三倍”直接缩小排查范围。基线记录的脚本可以很简单不需要额外买监控软件。把上面 3.1-3.3 的查询结果导出保存下来就行。关键是持之以恒最好固定在每周同一天、同一个业务低峰时段做这样数据才有可比性。4. 常见问题与排查思路实录4.1 CPU 飙高却找不到大查询重点看这两处完善的第一反应是抓 TOP SQL但有的时候 CPU 确实飙到了 90%sys.dm_exec_query_stats按 CPU 排出来的语句却看起来很平庸。这时候优先去查编译/重编译计数器的趋势。一次真实的排查经历客户说有台 SQL Server 每天十点半 CPU 准时爆表持续半小时后自动恢复。我上去抓 TOP SQL没有任何一条语句消耗能解释这么高的 CPU。后来看性能计数器Compilations/sec 在十点半突然从几十飙到几千。再结合代码审查发现当时有一个定时任务在大量拼接 SQL 并清空计划缓存导致所有后续查询全部重新编译。定位到问题后把定时任务改成参数化查询CPU 立刻回归正常。第二处容易被忽略的是统计信息更新和索引维护。如果运维自动化作业里每天固定时间点对大表做UPDATE STATISTICS或者重建索引CPU 飙高就是正常的维护窗口开销不算性能问题但一定要和业务时间错开否则会互相拖累。4.2 任务管理器里“SQL Server 占用内存”怎么解释这是 SQL Server 新手最容易误判的点。只要 max server memory 没有设置SQL Server 就会尽量把物理内存吃进 Buffer Pool 当缓存任务管理器看到的内存占用高不代表内存泄漏。真正的内存压力要结合这几个信号判断Target Server Memory 与 Total Server Memory 的长期差值。如果 Target 远大于 Total说明 SQL Server 因为外部限制max server memory或驱动问题无法扩展内存内存分配会变慢。Memory Grants Pending 持续大于 0说明有些查询在排队等内存。PLE 持续低于低位并伴随 Page Reads/sec 高企说明缓存无法容纳工作集每次查询都要走磁盘。如果看到这些信号优先检查 max server memory 配置给操作系统留出 2-4GB 或总量的 5% 内存再考虑是否要加物理内存。不要一看到任务管理器内存高就去限制 max server memory那等于把 SQL Server 的缓存能力废掉一半。4.3 磁盘延迟高但业务体验不明显可能问题在日志盘有次帮一个系统看 IO数据盘延迟在 5ms 以内非常健康但 WRITELOG 等待类型长期排在 TOP 3。点开虚拟文件统计一看事务日志文件的平均写延迟高达 25ms。业务方说“也没有特别明显的卡顿”但实际上所有写事务都在排队刷日志只是量没到临界点而已。事务日志的特点是顺序写但每次提交事务都要等日志落盘才能返回。日志盘的延迟直接决定写事务的响应时间。所以如果WRITELOG等待时间占比高优先检查日志文件所在的物理磁盘而不是去优化 SQL。很多团队习惯把所有文件都丢到一个盘上数据读 IO 和日志写 IO 互相干扰把日志文件单独放到 SSD 上通常立竿见影。同理tempdb 也非常吃 IO并发高的时候和用户库抢磁盘也会拖慢全局性能。4.4 阻塞与死锁的实战排查阻塞最典型的现场是某个后台事务开了事务更新了几行数据但没有提交也没有回滚直接挂机。后面所有要读这几行的查询全部排在LCK_M_X等待上。用 2.4 那两条阻塞查询脚本可以立刻看到BlockingSessionID和它的 SQL 文本。处理方式一般就是和业务确认后杀掉阻塞头会话同时推动代码层把事务尽量做短、统一提交顺序。死锁则不同它不会一直阻塞而是直接报错让其中一个事务回滚。如果业务侧频繁报“事务死锁”就按 2.4 说的打开 1222 跟踪标志然后去错误日志里翻死锁图。调试时多看两个细节第一两个死锁会话分别以什么顺序访问哪些对象第二死锁涉及的索引是不是同一个。前者如果是反序更新改代码统一访问顺序后者可以靠调整索引或加 with (rowlock) 等手段降低锁粒度。4.5 问题速查表最后把常见的症状、要看的指标和对应方向整理成一张速查表排查时对照着看症状优先查看的指标/视图常见指向下一步动作CPU 整体飙高% Processor Time、Compilations/sec大查询或编译风暴查 TOP SQL分析执行计划单个查询一直跑不完sys.dm_exec_requests 的 wait_type锁等待、IO 等待、并行倾斜按等待类型进入对应排查流程缓存命中率低但内存充足PLE、Memory Grants Pending内存配置受限或工作集过大检查 max server memory 和内存信号写事务都慢Avg. Disk sec/Write、WRITELOG日志盘延迟高迁移日志文件到低延迟磁盘业务偶发超时Number of Deadlocks/sec、错误日志死锁开启 1222分析死锁图连接数异常增长User Connections、sys.dm_exec_sessions连接泄漏或应用并发突增查应用连接池设置和活动会话来源之前快后来慢Query Store、执行计划缓存计划漂移/统计信息变化用 Query Store 对比新旧计划个人经验里最重要的一点监控工具的指标不能贪多。我最早恨不得把一百多个计数器全拉出来看最后什么都看不出问题。后来养成的习惯是每次只先看等待统计知道 SQL Server 到底“等”在什么地方再往下钻取对应的资源指标和 SQL 文本。在系统正常的时候先把基线记录好等出状况时才有参照物。没有基线的性能优化基本就是碰运气。
