MS SQL数据库监控与调优

　　其实在进行性能测试过程中，我们选择的业务都与数据库有关，即选择的业务都会经过数据库的处理，那么业务的响应时间就包含数据库查询的时间，同样如果数据库查询的时间过长，那么将直接导致事务的响应时间过长，所以数据库的调优也是性能调优的重要组成部分。关于数据库调优有两部分的内容：数据库的监控与调优，而关于数据库的监控又分为两个方面：一是SQL Server资源监控;二是查询语句执行的监控;而调优则主要是索引调优级、T-SQL调优和阻塞、死锁的调优。

　　一、监控SQL Server资源

　　如果测试过程中发现是由于SQL Server性能引起的事务响应时间过长，那么接下来就必须分析是什么原因导致SQL Server出现性能问题，通常的原因有两个方面的可能性，一是SQL Server资源出现问题;二是查询语句速度太慢;如果是资源的原因导致性能出现问题，那么接下来需要深入分析是什么系统资源导致SQL Server性能出现问题的。最常用的资源监控工具为System Monitor，这个工具在9.1.2章节进行了详细的介绍，在本章中就不详细介绍。

　　1、瓶颈类型

　　所谓的瓶颈是指数据库的性能受到某个方面资源的限制，导致数据库的性能未达到预期期望或是性能没有达到最佳状态，这样在性能测试过程中就必须分析诊断是什么原因导致数据库性能出现瓶颈，当找到原因后就需要想办法解决，例如，某服务器的CPU使用率将近达到100%，那么可以确定是服务器的CPU出现瓶颈。

　　关于瓶颈的原因可能有很多种，在性能测试过程中不可能一次性找到所有的问题所在，必须经过多次测试才能找到最佳的配置方案，当然这样做的目的很简单，就是希望数据库服务提供最佳的性能，同时资源利用率上达到一个平衡。

　　从硬件资源的维度来划分，瓶颈主要包括三个方面：内存、磁盘和CPU。数据库中所有的数据都存储在磁盘中，当数据库从磁盘中读取或写入数据页面时，需要消耗内存，如果此时缺少内存，这样会对SQL Server的产生严重的影响，因为当内存不够时，SQL Server被将分页写到磁盘，这对性有带来很大的损失。

　　同理，如果系统的磁盘出现问题，那么读取磁盘中的数据页和往磁盘中写数据页时，都会导致读写数据和速度被延迟。

　　在一些时候SQL Server需要计算总和、排序或连接数据库，这些操作都需要CPU时钟周期，那么如果CPU不够使用的话，SQL Server也一样会受到严重的影响。

　　2、内存瓶颈

　　SQL Server服务器内存通常有两种分类：VAS(Virtual address space虚拟地址空间)和物理内存。如果内存压力是由自身进程引起的，称之为内部内存压力，如果内存压力是由于Windows中其它的进程引起的，则称之为外部内存压力。

　　关于内部内存压力通常由两种原因引起的：第一：缓冲池变化，缓冲池收缩导致内存出现问题，此时需要注意是不是Max server memory的值在减小;第二：一些相关的作业消耗了SQL Server内部内存，如扩展存储过程、COM对象、SQLCLR以及链接服务器都会消耗内存，导致缓冲池出现压力。

　　外部内存压力是指由于其它的Windows进程争用内存引起的，如果Windows操作系统给SQL Server Operating System(SQLOS)中的Resource Monitor发出请求信号，要求SQL减少其分配的内存空间，这样SQL会重新计算目标占用水平以决定是否减少内存占用水平。

　　判断是否出现外部内存压力，通常可以分析以下计数器：

　　Process-Working Set：指这个处理的Working Set中的当前字节数，Working Set是在处理中被线程最近触到的那个内存页集。在输出中找出消耗内存最多的进程，可以发现除SQL Server之外，哪个应用程序消耗了较多的内存，进而可以判断SQL Server是否有足够的使用内存。

　　SQL Server:Buffer Manager-Total Pages:Buffer Manager/Total Pages计数器表示SQL Server已获得的页面总数。

　　SQL Server:Buffer Manager-Target Pages：SQL Server:Buffer Manager/Target Pages计数器表示SQL Server Buffer Pool所需要的理想页面数。

　　如果Target Pages与Total Pages的值相同，则表示SQL Server拥有足够的内存，如果Total Pages值小于Target Pages说明存在外部内存压力，SQL Server不能获得足够的内存数量。

　　在确定内存是否存在瓶颈，除了需要检查Available Bytes、Pages/second、Paging File utiliztion(这三个计数器在9.1.3小节中进行了详细的介绍，在该小节中就不进行介绍)之外还需要检查以下几个计数器：

　　SQL Server：Buffer Manager-Page Life Expectancy：Buffer Manager中的Page Life Expectancy计数器表示数据页在缓冲池中驻留的时间长度，单位秒。Page Life Expectancy值越大，说明服务器的性能越好，如果服务器存在内存压力，那么Page Life Expectancy的值将小于300秒，如果监控到的值小于300秒，那么很有可能是因为缺少内存引起的，还有一种情况，如果在整个监控过程中发现Page Life Expectancy的值在不断的降低，那么就需要多加留意了。

　　SQL Server:Buffer Manager-Buffer cache hit ratio：Buffer cache hit ratio计数器显示待查询请求页面在SQL Server缓冲池(即物理内存中)中被找到的数目，如果在缓冲池中没有找到数据页，那么SQL Server必须将数据页从磁盘中读入到缓冲区，而从磁盘读入到缓冲池的过程相对较慢，主要影响的时间是磁盘寻道时间和旋转延迟时间，即使是企业级的SAN上，从磁盘上读取一个页面所花费的时间也是直接从内存中读取页面所花费时间的数倍，所以显然希望该值越大越好，这样从磁盘读取的页面数就越来越少，即所花费的时间就越短。

　　缓冲池的大小由sp_config中的min server memory(最小服务器内存)和max server memory(最大服务器内存)两个选项决定，但需要注意的是，假设配置了最大服务器的内存为512MB，但并不代表SQL Server就一定能够储备这么多的内存，很可能出现这种情况一些应用程序优先于SQL Server进行了内存储备。在规划缓冲池大小时，需要注意至少应该保证buffer cache hit ratio计数器的值大于98%，在测试过程中如果buffer cache hit ratio的值小于98%，则有可能表示服务器没有足够的空闲内存。

　　SQL Server：Buffer Manager-Stolen pages：Stolen pages计数器表示内存中被其它进程挪用的页面，如果该值相对于总目标页面数来说是一个较高的值，那么说明服务器正在经受内存压力，在查询窗口中，输入dbcc memorystatus命令可以当前关于内存的详细信息，如图11-1所示。　　

　　图11-1 内存详细信息

　　SQL Server：Buffer Manager-Memory Grants Pending：Memory Grants Pending表示等待内存授权的进程队列，正常情况下服务器应该是没有明显的等待内存授权的进程，如果出现等待内存的进程队列，那么服务器的性能将下降。

　　SQL Server：Buffer Manager-Checkpoint pages/sec：SQL Server检查点会检查所有的脏页是否写到磁盘上，检查点进程在磁盘输入/输出方面付出的代价很昂贵，当服务器运行的内存较少时，检查点进程会比正常情况下更频繁的发生，因为SQL Server会试图在缓冲池中创建空间，如果测试过程中发现Checkpoint pages/sec的值出现持续高于服务器正常速度的情况，那么表示服务器缺少内存。

　　SQL Server：Buffer Manager-Lazy writes/sec：表示每秒钟SQL Server将脏页从缓冲池中重新部署到磁盘的次数，正常情况应该提供足够的缓冲池空间，以使lazy writes尽可能接近于0，如果显示的每秒钟lazy writes的次数为大于20，则说明缓冲池不够大。

　　3、 CPU瓶颈

　　如果发现CPU的使用率过高，通常是高于85%，那么很有可能是CPU出现瓶颈，接下来需要进一步分析是什么原因导致CPU的使用率过高的，通常需要分析的读数器包括：System-Processor Queue Length、Processor-%Privilage Time、Processor-%User Time、Processor-%User Time-sqlservr、SQLServer：SQL Statistics-SQL Compilations/sec和SQLServer：SQL Statistics-SQL Re-Compilations/sec，Processor-%Privilage Time和Processor-%User Time这两个计数器，在第9.1.3章节中进行了详细的介绍，在本章节中就不做介绍，本章节主要介绍其它的几个计数器，具体如下：

　　System-Processor Queue Length：处理器的队列长度，正常情况下服务器不忙碌的时候不可能出现处理器排队的现象，只有当服务器处于忙碌的状态时，才可能出现处理器队列的问题，所以当处理器出现排队现象，那么说服务器和性能受到影响，一般情况下处理器的队列长度不超过CPU个数。

　　Processor-%User Time-sqlservr：该计数器显示SQL Server进程所消耗的CPU时间数量，通过分析该计数器，可以分析过高的用户模型所消耗的CPU的时间，是否是由SQL Server进程引起的。

　　SQLServer：SQL Statistics-SQL Compilations/sec和SQLServer：SQL Statistics-SQL Re-Compilations/sec这两计数器表示执行计划编译与重编译率，对于一台暖服务器，执行计划的重用率至少应该在90%以上，即在执行过程中，最多只有10%的查询计划需要重新进行编译，如果当执行过程中需要重新编译的查询计划过多，那么将导致消耗过高的CPU资源。

　　4、磁盘瓶颈

　　在执行查询计划过程中，最多的是对磁盘进行写入与读出数据的操作，所在SQL Server服务器应该尽可能的避免频繁的在磁盘与内存间进行传递数据，以降低对服务器性能的影响，为了解决这个问题，SQL Server使用缓冲高速缓存(buffer cache)和计划高速缓存(plan cache)，其中buffer cache用于预载数据，plan cache用于加载检索数据的方法是否是最优计划。如果对磁盘进行调优，在调优之前需要获得磁盘性能的一个基线值，否则测试过程中如果遇到磁盘出现队列或出现延迟现象，就无法确定这个问题是否正常。

　　如果怀疑磁盘出现瓶颈，通常需要监控的计数器包括：PhysicalDisk- Avg.Disk Queue Length、PhysicalDisk- Avg.Disk sec/Read、PhysicalDisk- Avg.Disk sec/Write、SQL Server：Access Methods-Full scans/sec和SQL Server：Access Methods-Page Splits/sec。其中PhysicalDisk- Avg.Disk Queue Length、PhysicalDisk- Avg.Disk sec/Read和PhysicalDisk- Avg.Disk sec/Write三个计数器在9.1.3章节中进行了详细的介绍，在本章节中就不做介绍。

　　SQL Server：Access Methods-Full scans/sec：该计数器表示每秒钟完全扫描索引或完全扫描基本表的数目，在数据库设计过程中应该尽量降低全表扫描的次数，特别是对于那些大表，如果进行全表扫描将会直接导致性能下降，如果扫描频率大于1次，那么说明缺少索引或索引较差。

　　SQL Server：Access Methods-Page Splits/sec：该计数器表示每秒钟页面拆分数量，在执行插入或更新计划时，如果当前的数据页没有足够的空间来完成这些操作，那么就必须增加新页来完成插入或更新操作，过多的拆分页会损害服务器的性能。

　　5、Wait Statistics监控

　　新的SQL Server版本中添加一个新的性能监控对象SQLServer:Wait Statistics，该计数器报告有关等待状态的信息，也包含一些全局的等待信息，SQLServer:Wait Statistics性能对象的12个计数器，具体的见表11-1。　　

表11-1 Wait Statistics计数器

　　其中每个计数器对象包含4个实例，这4个实例都是相同的，具体的见表11-2。　　

表11-2 计数器对象实例

　　常用的几个计数器对象为：SQLServer:Wait Statistics- Lock waits、SQLServer:Wait Statistics- Memory grant queue waits和SQLServer:Wait Statistics- Page IO latch waits，具体内容如下：

　　SQLServer:Wait Statistics- Lock waits：该计数器主要显示进程等待获得锁的时间，通过该计数器可以对进行获得锁的能力进行评估，对判断加锁很有帮助。

　　SQLServer:Wait Statistics- Memory grant queue waits：该计数器表示正在等待内存授权的进程数，也可以理解为进程式在队列中等待所花费的时间长度。

　　SQLServer:Wait Statistics- Page IO latch waits：统计与页 I/O 闩锁相关的统计信息，SQL Server要求锁存器来确保数据同步，在争锁存器的过程中可能存在超时现象，这样将直接影响SQL Server的性能。

　　本章关于“MS SQL数据库监控与调优”的内容就分享完了，大家喜欢的话记得每天来这里和小编一起学习涨薪技能哦。(笔芯)

　　附：川石信息全国校区最新开班时间，课程资料获取13691729932(微信同号)。