SQL Server 性能调优(性能基线)

在写这篇东西的时候我也不是很清楚性能基线，到底要检查点什么，dmv要不要检查，perfmon要检测那先。
所以我决定，对我发的《sql server 性能调优》文章内的 perfmon和dmv做一个总结。来建立自己的性能基线。

io

在io中我们要注意哪些性能指标呢？
1. physical disk\disk reads/sec   --这个应该很清楚 一看就就知道 这个指标是指什么的
2. physical disk\ disk writes/sec
一打开文章就看到这2个值，而却有阀值，看到阀值很开心，因为不用你去收集值了。
• Less than 10 ms = good performance
• Between 10 ms and 20 ms = slow performance
• Between 20 ms and 50 ms = poor performance
• Greater than 50 ms = significant performance problem.
接下来就是 sys.dm_os_wait_stats 中的几个wait type
3.  PAGEIOLATCH_*
 PAGEIOLATCH_* 系列的wait type 一共有
PAGEIOLATCH_DT   -- 破坏，什么是破坏，就是把内存中数据页释放掉
PAGEIOLATCH_EX   -- x锁，可以怎么理解，就是排他占用这个锁
PAGEIOLATCH_KP   -- 保持，就是保持这个页不被破坏
PAGEIOLATCH_NL   -- 没有定义，保留
PAGEIOLATCH_SH   -- 在读，数据页的时候就分配这个闩
PAGEIOLATCH_UP   -- 在更新的时候分配这个           
根据onlinebook的解释：在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“XX”模式。长时间的等待可能指示磁盘子系统出现问题。
讲的直白一点就是系统在io，入读或写的时候分配的。等待io请求
4. ASYNC_IO_COMPLETION
根据onlinebook的解释：当某任务正在等待 I/O 完成时出现
这个是等待异步io完成，那么和上面有没有关系呢？答案是没有，上面等待的是io读取出来，或者写入。这个是等待系统的异步io完成是不一样的概念。
5. IO_COMPLETION
根据onlinebook的解释：在等待 I/O 操作完成时出现。通常，该等待类型表示非数据页 I/O。数据页 I/O 完成等待显示为 PAGEIOLATCH_* waits。
这个就不解释了说的很明白了就是等待非数据页的io完成
6. WRITELOG
根据onlinebook的解释：等待日志刷新完成时出现。导致日志刷新的常见操作是检查点和事务提交。
这个也不多解释，就是写入日志时候等待的时间。

cpu

7.Processor/ %Privileged Time                          --内核级别的cpu使用率
8.Processor/ %User Time                                   --用户几倍的cpu使用率
9.Process (sqlservr.exe)/ %Processor Time    --某个进程的cpu使用率
10.SQLServer:SQL Statistics/Auto-Param Attempts/sec    --试图运行自动参数化次数
11. SQLServer:SQL Statistics/Failed Auto-params/sec       -- 自动参数化失败
12. SQLServer:SQL Statistics/Batch Requests/sec             -- 批处理量
13. SQLServer:SQL Statistics/SQL Compilations/sec          -- 编译次数
14.  SQLServer:SQL Statistics/SQL Re-Compilations/sec    -- 反编译次数
15.  SQLServer:Plan Cache/Cache hit Ratio                            -- 执行计划，cache命中率
接下来还是 wait event的
16.signal_wait_time_ms --从发出信号到开始运行的时间差，时间花费在等待运行队列中，是单纯的cpu等待。
下面代码量化的像是signal_wait_time_ms占的比重
SELECT SUM(signal_wait_time_ms) AS TotalSignalWaitTime ,
( SUM(CAST(signal_wait_time_ms AS NUMERIC(20, 2)))
/ SUM(CAST(wait_time_ms AS NUMERIC(20, 2))) * 100 )
AS PercentageSignalWaitsOfTotalTime
FROM sys.dm_os_wait_stats
在创建baseline 的时候 完全可以 按这个sql来获取值。
17.SOS_SCHEDULER_YIELD等待
onlinebook的解释:在任务自愿为要执行的其他任务生成计划程序时出现。在该等待期间任务正在等待其量程更新。
完全看不懂，啥叫量程。
直白的说就是：当查询自动放弃cpu，并且等待恢复执行，这个等待就叫做SOS_SCHEDULER_YIELD。
18.CXPACKET等待
onlinebook:当尝试同步查询处理器交换迭代器时出现。如果针对该等待类型的争用成为问题时，可以考虑降低并行度。
直白点就是：处理器之间的一种同步，一般出现在 并发查询，为啥？因为只有并发查询才用多个处理器。
接下来是 sys.dm_os_schedulers
SELECT scheduler_id ,
current_tasks_count ,
runnable_tasks_count
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
19.主要是查每个处理器上的任务数和可运行的任务数。
 
内存

20.SQL Server :Buffer Manager
又很多有用的计数器都是这 buffer manager 对象下面，可以帮助发现buffer pool滚筒的问题。
21.buffer cache hit ratio
buffer cache hit ratio一般情况下在oltp中要高于95%，在olap中要高于90%。可惜的是没有关于这个性能指标相关的解释，和这个值是如何影响预读机制的。如果这个指标的值有巨大的下降那么就说明有问题。这个不能说明内存压力和sql server 健康指数。
22.page life expectancy
page life expectancy是页生命周期，也就是一个数据页在内存中的时间。在以前sql server 2000 4g的内存已经很大了，sql server buffer pool的大小是1.6g，如果sql server 从磁盘上读取1.6g的数据也只要5分钟，但是今天64g的内存是主流，如果从磁盘一下子读取50g的内存，会严重的冲击io。当存在大量的查询扫描表，读入新的数据页，导致生命周期值下降也不是不正常的。这个值必须长期的监视来分析问题。
23.Free Pages
free pages是内存中空页的数量，不要接近于0。这个值说明查询能否在其他查询不是放内存的情况下，快速的分配内存的主要依据。如果free pages 很少，页生命周期很短，并且伴随着空页争用（free list stalls/sec）的情况那么很有可能导致内存压力。
24.Free list stalls/sec
Free list stalls/sec每秒空页等待的数量，如果一段时间内都在0以上那么说明可能存在内存压力。
25.lazy write/sec
lazy write/sec 就是每秒写入磁盘的次数。如果发生量很大并且生命周期很短，free page 很少，但是 free list stall/sec 量很大，那么就是发生内存压力了。

SQL Server:memory Manager
SQL Server:memory Manager对象内对内存的消费和内存管理的问题提供了很重要参考
26.total server memory 和 target server memory
这2个计数器代表了当前sql server 使用的总共内存和sql server 想要用的内存。如果 target server memory超过了total server memory，也是内存压力的重要标志。sql server 会减少内存的需求来接近服务的可用内存，或者通过最大服务器内存配置，所以当内存出现压力问题的时候不应该第一时间去查看这2个计数器
28.memory grants outstanding
该值是现实多少进程已经成功的获取了内存的授权。在一段时间内，业务高峰期，如果该值过低，那么标志可能存在内存压力，特别是 memory grants pending 也比较高的情况下。
29. memory grants pending
该值是有过少进程正在等待内存的授权。如果为非0，那么说明需要调整或者优化负载或者增加内存。
 
结束语

每个需要跟踪的东西我都简单的解释了一下。关于 wait event 是累计计数的，在计算的时候需要相减。
这样跟踪个一天，设置好频率，就能得出性能基线了，可以做成图标，这样通过图形就更容易看出问题了。
 

作者 Amaranthus