SQL Server内存用得怎么样，性能瓶颈到底在哪儿，怎么监控才靠谱

SQL Server内存用得怎么样？

SQL Server是一个非常“贪吃”内存的软件，这是它的核心设计理念，它的主要内存区域叫做“缓冲池”（Buffer Pool），你可以把它想象成SQL Server给自己开辟的一个巨大的“数据工作台”。（来源：微软官方文档对缓冲池的描述）

这个工作台的主要用途是：

1. 缓存数据页：当SQL Server需要读取数据时，它首先会去这个工作台找，如果数据已经在台上了（称为“页在缓存中”），它就直接拿来用，速度极快，这叫“逻辑读”，如果台上没有，它就得慢吞吞地去硬盘上把数据搬上来，这叫“物理读”，SQL Server会尽可能地把所有常用数据都放在这个工作台上，目标是让物理读越少越好,从而极大提升查询速度。
2. 缓存执行计划：当你执行一条SQL语句时，SQL Server需要先制定一个“作战方案”（执行计划），决定先查哪个表，用哪个索引等，生成这个方案需要成本，所以它会把这个方案也放在内存的另一个区域（计划缓存）里，下次遇到一样的语句就直接用现成的方案,省时省力。

一个健康且忙碌的SQL Server实例，其内存使用率通常会很高，甚至会试图用完所有分配给它的内存（默认情况下，它几乎会吃掉Windows系统能给出的所有剩余内存），这本身不一定是问题，反而是它高效工作的表现，关键在于，这些内存是否被用在了“刀刃”上——即是否有效地减少了昂贵的磁盘读写。

性能瓶颈到底在哪儿？

性能瓶颈就像交通堵塞，可能发生在各个环节，对于SQL Server，最常见的瓶颈点有三个：内存、磁盘和CPU。

1. 内存瓶颈（Page Life Expectancy过低）：这是最需要关注的指标之一，它直译是“页寿命”，指的是一个数据页从被搬上内存工作台到可能被踢出去为新数据腾地方的平均时间（单位是秒），这个值越高，说明数据在内存里待得越久，被重复利用的概率越高，性能越好，如果这个数值持续且剧烈地下降（比如从几百秒掉到几十秒），就是一个强烈的警报，说明内存工作台太小了，数据刚放上去就被挤掉了，SQL Server不得不疯狂地访问硬盘，性能会急剧下降，这通常意味着服务器物理内存不足，或者需要调整SQL Server的内存设置。（来源：微软PSS（产品支持服务）团队及众多资深DBA的经验总结）

2. 磁盘瓶颈：当内存不够用，或者有大量需要写入的数据时，压力就给到了磁盘,监控磁盘瓶颈主要看两个计数器：

   • 平均磁盘队列长度：可以理解为等待读写的“车辆”排了多长的队，如果这个值持续高于物理磁盘数量的2倍，说明磁盘已经忙不过来了,成了瓶颈。
   • 平均磁盘秒/读 和 平均磁盘秒/写：这直接反映了磁盘一次读写操作需要多长时间，通常这个值应该低于20毫秒（0.02秒），如果经常超过这个值，特别是达到几百毫秒，说明磁盘速度太慢，或者磁盘子系统存在故障或配置问题。（来源：Windows性能计数器通用阈值标准）

3. CPU瓶颈：当有大量复杂的计算（如排序、聚合运算）或者许多并发查询时，CPU可能会成为瓶颈，监控的关键指标是CPU使用率，如果SQL Server进程的CPU使用率持续保持在80%甚至90%以上，就说明CPU压力很大，高CPU使用率通常是由效率低下的查询引起的，比如缺少合适的索引、写了很复杂的逻辑或者查询没有使用到索引（表扫描）。（来源：服务器性能分析通用原则）

怎么监控才靠谱？

靠猜和感觉是不靠谱的，必须依靠数据,最直接有效的方法是使用系统自带的工具。

1. 使用性能计数器（Performance Monitor）：这是Windows自带的宝藏工具，是监控SQL Server健康状况的“听诊器”,你需要添加并持续观察一些关键的计数器：

   • 内存相关：
     • SQLServer:Buffer Manager\Page Life Expectancy：核心中的核心,目标值是持续高于300秒。
     • SQLServer:Buffer Manager\Buffer cache hit ratio：缓冲池命中率，表示有多少比例的数据请求直接从内存得到满足，理想情况下应高于95%，但对于超大型数据库，这个值可能偏低,因此PLE是更可靠的指标。
   • 磁盘相关：
     • PhysicalDisk(_Total)\Avg. Disk Queue Length
     • PhysicalDisk(_Total)\Avg. Disk sec/Read
     • PhysicalDisk(_Total)\Avg. Disk sec/Write
   • CPU相关：
     • Processor(_Total)\% Processor Time：看总CPU压力。
     • Process(sqlservr)\% Processor Time：看SQL Server进程独占了多少CPU。

2. 使用动态管理视图（DMVs）：这是SQL Server内部的一个“仪表盘”，可以让你看到实时的内部运行状态，通过执行一些查询,你能找到具体是哪些SQL语句在消耗资源。

   • 查询当前消耗CPU最高的请求：SELECT * FROM sys.dm_exec_requests ORDER BY cpu_time DESC;
   • 查询累计消耗IO最多的查询：可以关联sys.dm_exec_query_stats等视图来找到“罪魁祸首”的SQL文本。（来源：SQL Server动态管理视图官方文档）

3. 使用SQL Server Profiler或扩展事件（Extended Events）：这是一个更高级的“黑匣子”，可以捕获服务器上发生的每一个事件，比如每条执行的SQL语句及其耗时，它功能强大但对性能有一定影响，通常用于在出现特定问题时进行针对性的深入抓取和分析,而不是7x24小时开启。

总结一下靠谱的监控思路：

• 日常健康检查：定期（如每天）查看性能计数器的关键指标，特别是PLE、磁盘延迟和CPU使用率，建立性能基线，知道正常情况下这些值是多少,一旦发生偏离就能迅速发现。
• 出现问题时：结合性能计数器定位瓶颈方向（是内存、磁盘还是CPU），然后使用DMVs查询去定位到具体是哪个数据库、哪张表、哪条SQL语句导致了这个问题。
• 根本解决：大多数性能问题最终都会追溯到索引缺失或SQL语句写法不佳，通过监控找到问题查询后，通过创建索引、重写查询等方法从根本上解决。

监控的目的不是等服务器瘫痪了再去救火，而是通过持续观察，在问题变得严重之前就发现苗头,防患于未然。