SQL Server性能监控这件事我做了十多年。从早期的2000、2005一路摸到2019、2022身边很多同事和朋友经常问我为什么生产环境跑着跑着就卡了为什么同样的查询昨天3秒今天30秒为什么加了索引还是不解决问题几乎每次排查到最后答案都落在同一个点上——你根本没有建立一套完整的性能监控体系。装好实例、建好数据库、业务上线这只是开始真正决定数据库稳不稳、快不快的是你能不能持续盯着那几项核心指标并在指标异常的第一时间做出反应。这篇东西想聊的就是SQL Server性能监控里最值得盯的核心指标以及我这些年实际在用的监控脚本、排查套路和踩坑记录。目标读者是那些已经开始接触SQL Server、想从能跑就行进化到主动运维的DBA、开发者和运维同学。我会尽量把每一个指标讲清楚告诉你它为什么重要、怎么看、阈值大概是多少以及异常时下一步去哪查。1. 先搞清楚SQL Server性能监控到底在监控什么很多人一提监控就想到看CPU、看内存、看磁盘这没错但太粗了。SQL Server是一个高度自治的关系型数据库引擎它自己管理内存、缓存、锁、事务日志操作系统层面的指标只能告诉你服务器压力大不大却说不清压力到底来自哪里。真正的监控要分成两个层面来看一是服务器硬件资源是否充足二是SQL Server内部工作机制是否健康。1.1 监控的本质是找瓶颈所有的性能问题归根结底都是资源竞争问题。某个请求需要CPU而CPU已经满了某个查询需要读数据页而内存里的缓存不够某个事务要写日志而磁盘响应时间太长。系统慢下来的时候往往不是所有资源都紧张而是某一个关键资源成了瓶颈把所有请求堵在门口。所以监控的核心思路不是看哪个指标高而是定位当前系统的瓶颈点在哪里。这需要一组能够反映资源供给和需求关系的指标再结合SQL Server自己记录的等待信息才能把问题锁定。我见过不少运维同学看到CPU利用率95%就急着加CPU结果加了CPU问题依旧因为真正的瓶颈是大量查询走全表扫描产生巨大的I/O压力CPU只是被I/O等待拖累的表象。只看表面指标不做关联分析就会走弯路。1.2 建立基线的意义监控的另一件事是建立基线。没有基线的监控等于没有刻度的温度计。你看到CPU 60%觉得不高但如果这个系统平时CPU只有15%那60%就已经是重大异常了。反过来一个OLAP报表库平时CPU就是80%满载运行80%再正常不过。所以部署监控的第一步是先采集正常运行状态下至少一周的指标形成基线。之后所有告警阈值都基于基线来定而不是套网上随便搜来的CPU超过80%就报警之类的经验值。基线的维度不止CPU还有关键查询的执行时长、缓存命中率、等待类型的占比、并发连接数等等。建立好基线之后你才会明白指标异常不是超过某个固定值而是偏离了正常轨迹。2. 基础设施类指标CPU、内存、磁盘、网络基础设施指标是性能监控的地基。SQL Server再强大也是跑在操作系统上的应用程序。这四个维度覆盖了绝大多数资源层面的问题。2.1 CPU信号等待和时间占比CPU指标不能只看系统整体的处理器利用率这一个值。SQL Server的CPU压力分两种情况持续高负载CPU长时间处于90%以上通常意味着系统真的忙或者是某条查询在疯狂运算。间歇性尖峰CPU有规律地冲到100%可能是作业调度、编译重编译、或者某些大查询周期性地发生。我习惯看的指标有两个Processor: % Processor Time _Total整机CPU利用率。超过80%就要警惕超过90%持续10分钟以上基本算严重。SQLServer: SQL Statistics - SQL Compilations/sec和SQL Re-Compilations/sec每秒编译和重编译次数。如果编译次数很高说明经常有即席查询ad-hoc query或者计划被频繁扔掉CPU大量消耗在生成执行计划上而不是真正执行查询。另外SQL Server内部的等待类型里有一个叫SOS_SCHEDULER_YIELD这个等待如果比例很高说明CPU调度不过来有大量任务在排队。它对应的是CPU瓶颈的内部视角。我排查CPU问题的时候习惯先看等待统计再看CPU计数器最后抓当时的活动会话三步下来基本能把元凶找出来。2.2 内存缓存命中率和页生命期望内存是SQL Server最容易让人误判的领域。它默认会尽量吃满物理内存作为缓冲池这是设计行为不是内存泄漏。所以监控内存的核心不应该是内存还有多少空闲而要看SQL Server用内存干什么以及缓存是否高效。关键指标SQLServer: Buffer Manager - Buffer cache hit ratio缓冲池命中率。代表要从内存读取数据页的比例。高于99%是健康的如果长期低于95%说明内存不足或者查询扫了大量的冷数据页。SQLServer: Buffer Manager - Page life expectancyPLE页生存期单位秒。它表示一个数据页在缓冲池里平均待多久才会被淘汰。低于基线值通常低于300秒算危险说明内存压力很大数据页被频繁挤出缓存。注意PLE没有绝对标准必须结合基线看。SQLServer: Memory Manager - Memory Grants Pending等待内存授权的进程数。如果这个数值持续大于0说明有查询在等待内存典型的复杂排序、哈希连接把内存耗尽了。我特别想提醒一点很多人看到SQL Server占内存大就紧张然后手动限制Max Server Memory。这种做法非常危险。限制得过低缓存命中率和PLE会暴跌查询被迫反复读磁盘性能反而更差。正确做法是先通过DMV分析缓冲池里到底缓存了多少数据再结合PLE走势逐步调整Max Server Memory。2.3 磁盘延迟和队列长度磁盘是SQL Server性能最大的瓶颈之一也是最容易被忽视的。CPU追一追还能上去内存可以花钱加磁盘延迟要是高整个库都会跟着遭殃。第一个要盯的是PhysicalDisk - Avg. Disk sec/Read和Avg. Disk sec/Write。这两个值代表每次读写耗时单位秒通常建议分别看毫秒。经验值小于10ms很好10ms - 20ms一般注意观察大于20ms慢需要排查超过50ms严重业务必然受影响第二个指标是PhysicalDisk - Current Disk Queue Length。它反映磁盘队列中等待处理的I/O请求数量。如果持续高于磁盘轴数机械盘通常1-2SSD可以更高说明磁盘已经忙不过来了请求在排队。另外日志文件和数据文件的I/O模式完全不同。事务日志是顺序写数据文件是随机读写。如果日志文件所在的磁盘延迟高你会看到大量WRITELOG等待如果数据文件慢则是PAGEIOLATCH_SH、PAGEIOLATCH_EX等待。区分这两种等待类型能帮你快速判断是该优化查询还是该换磁盘。2.4 网络吞吐量网络指标在SQL Server监控里经常被忽略但跨机房、跨地域访问数据库时网络延迟会直接拖垮应用。Network Interface - Bytes Total/sec和TCPv4 - Segments Retransmitted/sec是两个基础指标。重传率高说明网络不稳定有丢包TCP在重发数据这是导致查询偶尔变慢的隐形杀手。另外如果应用和数据库之间走的是高延迟链路你实际感受到的慢可能不是查询执行慢而是数据传输慢。这种情况SQL Server内部很难看到明显问题因为执行时间很短但整体耗时很长。排查方法是用SQL Server Profiler或者扩展事件记录Duration和CPU time两者差距太大时问题就在网络传输环节。3. SQL Server内部核心指标等待统计、锁、索引、查询基础设施指标只能告诉你服务器喘不过气但SQL Server心里的苦水要听它自己说。下面这些内部指标才是指出具体是谁在搞事情的关键。3.1 等待统计Wait Stats等待统计是SQL Server性能诊断里我认为最有力的工具。一个请求执行的时候会遇到各种各样的等待SQL Server会把每一次等待的原因记录到sys.dm_os_wait_stats里。通过分析等待类型的累计时间可以知道整个实例的时间都花在哪里。常见的等待类型和含义等待类型含义常见原因PAGEIOLATCH_SH/EX等待数据页从磁盘读入内存磁盘慢、缺失索引、内存不足WRITELOG等待日志写入磁盘日志磁盘慢、事务过于频繁LCK_M_X/LCK_M_S等待获取锁锁竞争、长事务阻塞CXCONSUMER/CXPACKET并行查询等待并行度配置不合理SOS_SCHEDULER_YIELDCPU调度等待CPU过载RESOURCE_SEMAPHORE等待内存授权内存压力、大查询并发注意等待统计是累计值从实例启动一直累积。所以我建议定期比如每小时做一次快照计算两次快照之间的增量才能看到当前时间段最突出的等待。不要只看累加值那反映的是历史总量而不是当下问题。3.2 锁和阻塞锁等待是SQL Server中最直接、也最令人头疼的问题。一次更新操作锁住了表里的某些行另一个查询需要读这些行就被阻塞在那里前端表现为卡住不动。监控锁和阻塞我习惯看SQLServer: Locks - Lock Waits/sec和Lock Wait Time (ms)。但这两个计数器只能告诉你有锁等待不能告诉你谁锁谁。真正的元凶信息藏在DMV里sys.dm_tran_locks当前所有锁的详细信息。sys.dm_os_waiting_tasks当前正在等待的会话。sys.dm_exec_requests每个正在执行的请求里面有blocking_session_id直接告诉你是谁阻塞了谁。当阻塞发生时用下面这个查询可以立刻得到阻塞链SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, t.text AS sql_text, r.status FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.blocking_session_id 0;这个查询的结果里blocking_session_id不为空的那一行就是受害者对应记录的blocking_session_id就是加锁方。拿加锁方的session_id再去查它正在执行的SQL就能定位到罪魁祸首。实际处理时常见的阻塞来源是长事务不提交、应用层没有及时提交事务、索引缺失导致更新锁范围扩大、以及死锁重试逻辑不完善。3.3 索引使用情况与缺失索引索引是SQL Server性能调优最大的战场。很多慢查询的根源就是一个表几十万行甚至几百万行查询却走全表扫描。监控索引要分两个方面第一看现有索引到底有没有被用上。可以用sys.dm_db_index_usage_stats这个DMV记录了每个索引被查找、扫描、更新的次数。我排查的时候常用这个查询找出认真维护却从来没人用的索引SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON s.object_id i.object_id AND s.index_id i.index_id WHERE s.database_id DB_ID() AND s.user_updates s.user_seeks s.user_scans s.user_lookups ORDER BY s.user_updates DESC;如果一个索引上更新次数远大于查询次数说明这个索引带来的维护成本可能超过了价值可以考虑删除。当然要结合业务判断不能机械地删除。第二看系统建议你创建的缺失索引。sys.dm_db_missing_index_details和sys.dm_db_missing_index_group_stats记录了缺失索引信息和预计收益。这个信息非常有用但不要盲目照搬系统建议。实际中我见过太多情况按建议建了索引之后查询是快了但数据写入变慢甚至其他查询的执行计划被改变引发新问题。正确做法是先看缺失索引背后的典型查询确认它的场景再决定要不要建、怎么建。3.4 查询性能编译/重编译、计划缓存SQL Server对每一条查询都会生成执行计划然后把计划缓存在内存里重复使用。计划缓存管理得好不好直接影响CPU和内存。SQL Compilations/sec这个计数器如果持续高通常是因为即席查询太多每次传到SQL Server的SQL文本都不同导致系统无法复用计划只能不断编译。我见过一个典型的生产事故应用层用字符串拼接SQL每条SQL的where条件里带不同的参数值结果系统每秒编译上千次CPU占用直接拉满。这种问题的解法是强制参数化或者改写应用逻辑使用参数化查询。另外sys.dm_exec_query_stats记录了每条查询的累计执行时间、CPU时间、逻辑读、物理读。用它可以找出消耗资源最多的TOP N查询SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu_ms, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.execution_count, 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 statement_text 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;这个查询能帮你快速定位吃CPU最狠的几条SQL。拿到SQL文本后再去看执行计划分析是缺索引、统计信息过旧、还是写法有问题。4. 用DMV写一套自己的监控脚本很多人觉得监控SQL Server要买商业工具其实不用。SQL Server自带的DMV和性能计数器已经覆盖了绝大部分监控需求只要你会组合它们就能搭一套足够用的监控体系。4.1 关键DMV速查我把日常用得最多的DMV列出来按用途分组方便你查表资源状态sys.dm_os_performance_counters、sys.dm_os_sys_memory会话与请求sys.dm_exec_requests、sys.dm_exec_sessions等待统计sys.dm_os_wait_stats查询与计划sys.dm_exec_query_stats、sys.dm_exec_sql_text、sys.dm_exec_query_plan锁与阻塞sys.dm_tran_locks、sys.dm_os_waiting_tasks索引状态sys.dm_db_index_usage_stats、sys.dm_db_missing_index_details数据库文件I/Osys.dm_io_virtual_file_stats这套DMV组合拳是每个SQL Server性能排查人员都应该熟记的。我的习惯是每次排查问题时先跑一套预设的体检脚本同时采集这些视图里的关键数据存成表格再做对比分析。4.2 一条命令看全局下面分享一个我一直在用的快速体检脚本。它会把当前实例的等待状态、CPU使用率、内存压力和主要工作负载汇总成一张表几秒钟就能看出问题苗头SELECT GETDATE() AS collection_time, (SELECT COUNT(*) FROM sys.dm_exec_requests WHERE status running) AS running_requests, (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process 1) AS user_sessions, (SELECT TOP 1 wait_type FROM sys.dm_os_wait_stats WHERE wait_type NOT LIKE %SLEEP% ORDER BY wait_time_ms DESC) AS top_wait_type, (SELECT CAST(Available_physical_memory_kb / 1024.0 / 1024 AS DECIMAL(10,2)) FROM sys.dm_os_sys_memory) AS available_memory_gb, (SELECT CAST(physical_memory_in_use_kb / 1024.0 / 1024 AS DECIMAL(10,2)) FROM sys.dm_os_process_memory) AS sql_memory_in_use_gb;这个查询不会给你所有答案但能帮你快速建立当前系统的快照状态。如果发现running_requests一直在涨、top_wait_type集中到一两个类型、内存可用量下降很快那就说明系统正处于异常状态接下来继续深入排查。4.3 监控工具选型SQL Server本身的监控工具有好几种不用全都上选适合自己场景的就行。性能监视器Performance Monitor适合采集操作系统层面的计数器可以设置定时采样适合做长期趋势。SSMS活动监视器适合临时查看当前活动和阻塞不适合做历史分析。扩展事件适合做轻量级审计和慢查询捕获。相比SQL TraceProfiler扩展事件对性能影响小得多我强烈建议新项目直接用扩展事件。SQL Agent作业 DMV脚本最朴素但最有效的方案。写一个SQL脚本定期把关键指标插入一张监控表再用报表展示趋势就是一套完全自建的历史监控体系。第三方商业工具如Quest Spotlight、SolarWinds DPA等功能全但成本高适合预算充足的大型企业。我自己的做法是先用脚本和作业搭建基础监控数据量大了再考虑商业工具。商业工具的价值主要在于自动化发现异常和直观的可视化但底层依据依然是那些核心指标和等待统计。5. 实操从零搭建一套性能监控方案很多朋友说道理都懂但不知道从哪里下手。这里我给出一个可落地的方案照着做你至少能拥有一个可以跑起来的监控体系。5.1 采集策略与频次监控数据不是采集得越频繁越好。太频繁会加重数据库负担太稀疏又漏掉关键问题。我的建议是分两级采集实时级对关键指标CPU、内存、等待、阻塞每1-5分钟采集一次写入监控表。这个频次足够覆盖绝大多数性能问题也不会对系统造成明显影响。明细级对慢查询、错误日志、死锁信息等按事件触发采集。比如用扩展事件抓执行时间超过5秒的查询实时存储到文件。建一张监控样本表字段大概包含采集时间、CPU利用率、PLE、缓存命中率、内存占用、各类等待累计时间、活跃会话数、阻塞数、磁盘延迟等。每次采集就是往这张表插入一行。长期积累之后你就能用最简单的SELECT语句画出趋势图。5.2 设定告警阈值告警阈值必须结合基线来定下面给的是我常用的初始值你可以作为起点调整指标初始告警阈值说明CPU利用率持续15分钟 85%排除瞬时尖峰缓冲池命中率 95%低于95%通常意味着内存压力或大量扫表页生存期PLE 300秒结合基线调整磁盘读写延迟 20ms高延迟直接拖垮查询阻塞会话数持续 5个出现持续阻塞必须人工介入等待类型变化某一类等待占比 30%不设具体值看分布变化告警工具可以先用SQL Server Agent的作业轮询监控表发现超阈值就发邮件。生产环境如果有完善的监控平台如Zabbix、Prometheus也可以把DMV数据推送过去用平台的告警规则来做通知。5.3 输出日报/周报监控不能只靠告警日常的趋势分析同样重要。我每周会跑一个汇总脚本统计本周和上周的指标平均值、峰值、异常次数并生成一张简单的对比表。这个习惯帮我发现过很多还没有变成告警但已在恶化的问题比如PLE每周下降10%磁盘延迟从5ms慢慢涨到15ms都是需要提前介入的信号。日报和周报不需要做得很花哨一张表能说明问题就够。关键是坚持采集和对比让数据形成闭环。6. 常见问题排查技巧实录最后这部分我挑几个真实场景来讲排查思路。这些都是我在生产环境里反复处理过的问题希望对你有帮助。6.1 CPU 100%却不知道谁在跑很多人遇到CPU 100%第一反应是打开任务管理器但看到的全是sqlservr.exe等于什么都没看到。正确做法是先抓SQL Server内部。我一般分三步查sys.dm_exec_requests找到当前正在运行的会话按CPU时间排序。把CPU时间最高的会话的SQL文本和执行计划拿出来看。结合sys.dm_exec_query_stats查历史累计确认这条查询是不是长期存在。这里有个容易忽略的坑如果CPU尖峰只持续几秒等你打开DMV去查的时候查询已经跑完了。所以最好的方式是设置一个扩展事件捕获CPU使用超过某个阈值的事件或者定期比如每30秒把sys.dm_exec_requests里的活动会话快照保存下来。这样还原现场就有据可查。6.2 内存持续增长却无法释放SQL Server的内存增长绝大多数是正常的但有一种情况要注意如果SQLServer: Memory Manager - Memory Grants Pending持续大于0同时PLE快速下降说明有大量查询在向SQL Server申请内存做排序或哈希而缓冲池内存已经被这些内存授权借走了。这种问题常见于大查询并发场景。排查方法是先找到正在等待内存授权的会话看它们的查询计划里是不是有大量Sort或Hash操作。解决办法不是盲目加内存而是优化这些查询减少排序和哈希所需的内存或者限制最大并行度避免多个大查询同时消耗海量内存。6.3 查询时快时慢一条查询有时候几十毫秒有时候好几秒这种抽风式性能问题最能迷惑人。原因通常有三类参数嗅探SQL Server根据第一次传入的参数生成执行计划后续传入不同参数时原有计划不一定适合。典型表现是第一次运行快换参数后变慢再重编译又快。统计信息过期数据分布变化后优化器还在用旧统计信息生成的计划与实际数据不匹配。从sys.dm_db_stats_properties可以查看更新时间和抽样行数。缓存被挤出数据页在内存里是热是冷直接决定查询是命中缓存还是重新读磁盘。如果PLE低某条查询时快时慢很可能就是这个原因。处理时快时慢我会先看执行计划和实际IO统计判断查询在哪些运行中走了不同计划。如果是参数嗅探可以尝试加OPTION (RECOMPILE)或者OPTION (OPTIMIZE FOR UNKNOWN)但一定要在理解业务语义的前提下用。6.4 tempdb瓶颈tempdb是SQL Server的临时数据库用来存放临时表、排序、hash等中间结果。高并发下tempdb很容易成为热点因为所有用户的临时对象都在同一个库里。排查思路是看sys.dm_io_virtual_file_stats里tempdb数据文件的I/O延迟和等待同时看PAGELATCH_UP、PAGEIOLATCH_SH这些等待类型。如果tempdb频繁出现并发瓶颈常见解决方案包括增加tempdb数据文件数量通常建议和CPU核数一致但要结合实际情况让多个数据文件均匀分担分配页的争用启用内存优化临时表或者把临时表的读写尽量改成表变量。记住一个原则tempdb不是用来优化查询逻辑的它是查询执行过程中的工作台工作台堵了所有活都得等。这个项目做到后面我自己最深的体会是性能监控不是一锤子买卖不是一个脚本跑完就结束的工作而是一个持续积累、持续对比、持续纠正误判的过程。你不需要在一开始就上很重的平台先用DMV把关键指标采起来把基线和阈值跑通再慢慢补工具这套体系就会越长越厚实。数据库的最佳性能也从来不是一个固定点而是你对自己系统的理解越来越深之后能够更早发现问题、更快解决问题的那个动态过程。