SQL Server查询优化（关联等待和队列，确定行动方案）

关联等待和队列

在实例级找出重量级等待后，就应该把它们和队列关联起来，以找出有问题的资源。一步这主要使用性能监视器来完成的。

SQL Server也提供一个名为sys.dm_os_performance_counters的DVM，其中包含了性能监视器中所有与SQL Server实例对象相关的计数器。

以下是一些常用的参数指标：

1. SQL Server Buffer: Buffer Cache Hit Ratio

　　SQL Server从缓存中而不是磁盘中获得数据的频率.sqlserver会将某些查询过来的数据缓存在内存中用于以后再次查询使用。当一个查询甲进来了以后数据库会编译这个SQL看看需要哪些数据，然后执行计划首先去内存中找看是否有这次查询所需要的数据，如果这个同样的SQL刚才已经执行过了或者该表的数据已经缓存在内存中，但是却没有在内存中找到数据，那就有可能是因为内存不足引起内存挤压将缓存数据写回硬盘或者释放掉来提供数据库其他请求来使用。一般来说OLTP的系统，这个值最起码也应该在90％以上，理想值是99％。如果这个值低于90％，那建议你应该添加内存了。

2. Memory: Pages/sec

　　这个也是监控内存是否不足的一个比较重要的参数。这个计数器记录的是每秒钟内存和磁盘之间交换的页面数。频繁的交换页面就会消耗更多的IO，这会影响到服务器的性能。打个比方，超市有一个货架上边摆满了新进的各种商品A，b，C，当你去超市想买一个的时候直接去货架就能拿到一个，方便的很，当顾客进超市逛一圈以后跟你说我怎么没有发现旧商品d呢，我就想买这个d，然后工作人员就会去仓库把商品d拿出来摆放到货架上供下次顾客来买。但是货架摆满了怎么办呢，只能将时间长没有人问津的一个下架放到仓库然后空出来地方摆放d，但是下次另一个顾客来了又有想要购买一个的意向，工作人员就得再次把一个拿出来替换掉货架上的d。其实内存就是这个货架，硬盘就是仓库。因为货架太小了，导致只能频繁的更换货架上的商品来提供正常的运营，想减少反复 回搬运产生的IO开销，只能换个更大的货架来满足需求。

　　如果服务器上只跑的SQLSERVER，那这个指标的理想范围应该是0-20之间，偶尔超过20的话影响不大，如果这个值频繁的超过20，那说明你的这台服务器可能需要加内存了。

当然这个指标要配合着上一个指标缓冲区命中率来看，如果上一个指标缓冲命中一直在99％或者更高，而这个期间内你的页面交换一直在20以上，那意味着不仅仅是内存不足，而且其他的程序占用了系统内存。

3. Memory: Available Bytes

　　另一个监控内存情况的计数器就是这个。这个值最少最少也得大于5M，因为SQLSERVER需要始终维持5-10米的自由内存用于分配，当这个值低于5米的时候，那SQLSERVER可能会因为缺少内存而产生性能瓶颈。

4. Physical Disk: % Disk Time

　　这个计数器记录的是磁盘的繁忙程度（是整个磁盘阵列或者物理磁盘的繁忙程度）。理论上这个值应该低于55％，如果持续的高于55％，那说明这台服务器上可能有IO瓶颈。

如果只是偶尔的出现几次，那不必担心，但是可以对应的找到这个时间点，数据库正在干嘛执行了哪些语句，对应的优化一下。

5. Physical Disk: Avg. Disk Queue Length

　　 这是一个比较重要的查看磁盘IO情况的指标。理论上每个物理磁盘的值不应该超过2.当然这个值是需要计算的，比如用4块物理盘做了个RAID10，此时在一个监控周期内磁盘队列的均值是10，那每块磁盘的队列值就是10/4 = 2.5，那么就可以说这个磁盘阵列存在I / O瓶颈了。这个跟之前的disktime指标一样，偶尔出现不必担心，如果长时间出现，那就得着手考虑解决磁盘的IO性能问题了。

6. Processor: % Processor Time

　　这是监视cpu情况的一个指标（类似于磁盘时间）。这个是观察cpu利用率的一个关键参数。如果处理器时间计数器的值持续超过80％，说明cpu存在瓶颈问题。如果只是偶尔出现，那说明可能是这个时间点有个特别消耗CPU的查询，可以在下一次这个时间点来临的时候尝试抓一下SQL并且优化它如果在某一个时间点以后的CPU一直飙高，常见的情况就是：突然1。间的高并发2.索引重整3.突然一个经常使用的数据量特别大的索引失效了4.死锁5.其他好多好多。先找到问题所在，在处理掉它。

7. System: Processor Queue Length 

　　这个指标类似于磁盘队列长度，也是算单个cpu的。单个cpu不能超过2，比如你是2u的机器，那这个值不应该超过4，如果在一个监控周期内持续性的超过4，那就可能出现CPU瓶颈了。

 

确定行动方案

在找出主要等待类型和这些等待所涉及的资源之后，接下来的这一步是优化过程中承上启下的一步。根据目前掌握的信息，你将确定下一步研究的方向。在我们的例子中，就是要找出导致I / O，并行查询，与网络的等待，以及与事务日志等待的原因。

与I / O相关的等待需要我们进一步细化到数据库级别。并行等待（CXPACKET），通过降低服务器的最大并行数，可以缓解这个问题。网络等待的值较高，表示网络带宽有问题，但可能表示其他问题

更多行动方案可以参考《Microsoft SQL Server 2008技术内幕：T-SQL查询》