sql server 优化论-分析实例级别的等待

学习来源：SQL2008技术内幕：T-SQL查询 

步骤：

1.分析实例级别的等待

2.关联等待和队列

3.确定方案

4.细化到进程级

6.优化索引、查询



一。分析实例级别的等待

使用查询动态管理图（DMV）sys.dm_os_wait_stats来完成。
select 
wait_type,
waiting_tasks_count,--表示该类型的等待数量
wait_time_ms--以毫秒为单位的该类等待的总等待时间（该时间包含signal_wait_time_ms）

max_wait_time_ms,
signal_wait_time_ms--这个是在线程收到信号到通知运行的时间差
from sys.dm_os_wait_stats
order     by wait_type



等待类型：锁，闩锁（latch，一种轻型锁），I/O(包括I/O闩锁)，并行查询，事物日志，内存，编译，OLEDB(链接服务器和其他OLEDB组件)等相关等待

I/O相关：

网络等相关：

        如：这个ASYNC_NETWORK_IO等待类型的等待时间过长会，有可能是网络问题。

数据访问相关：

        如：CMEMTHREAD等待类型的数值较高（当某任务正在等待线程安全的内存对象时出现这种等待）

多线程并行查询相关：

        如：CXPACKET一些线程可能等待的时间过长，才继续工作。这个CXPACKET也又可能是其他原因造成的（可能是因为缺乏适当的索引而引起的过度的I/O）,这时I/O相关的等待会较高。

 WRITELOG：sql server不能足够快的写完日志时，这个值会较高

临时表相关： tempdb数据库中的性能问题可能会导致与I/O相关的等待或其他等待出现较高

  闩锁等待 PAGE_LATCH_UP:这个较高时，则表示内部结构(如:IAM,GAM,SGAM和PFS页面)上出现了争用。原因可能是临时表频繁的分配页面，向堆空间插入大量数据等。不正规的文件布局也可能导致争用

OLEDB（与服务器调用）、BULK INSERT、Full Text,当调用开始时，开始等待，结束时，结束等待。这个值较高时不一定是性能问题。

并发相关的阻塞问题：锁（LCK）较高。

分离重量级的等待

1.从系统最后一次重启或计数器清空以来，该等待类型的等待时间

2.该类型的等待时间占总时间的百分比

3.从最重量级的等待类型到当前等待类型的连续百分比

4.信号等待时间占等待时间的百分比

with waits as (
  select wait_type,
  wait_time_ms/1000 wait_time_s,
  100*wait_time_ms/sum(wait_time_ms) over() as pct,
  row_number() over(order by wait_time_ms desc ) as rn ,
  100*signal_wait_time_ms/wait_time_ms as signal_pct
  from sys.dm_os_wait_stats 
  where wait_time_ms>0 
  and wait_type not like '%SLEEP%'
    and wait_type not like '%IDLE%'
    and wait_type not like '%QUEUE%'
    and wait_type not in ( 'CLR_AUTO_EVENT','REQUEST_FOR_DEADLOCK_SEARCH','SQLTRACE_BUFFER_FLUSH')
  )
  select 
  w1.wait_type,--等待类型
  cast(w1.wait_time_s as numeric(12,2)) as wait_time_s,--
  cast(w1.pct as numeric(5,2)) as qct,
  cast(sum(w1.pct) as numeric(5,2)) as running_qct,
  cast(w1.signal_pct as numeric(5,2)) as signal_pct
  from waits as w1 
  join waits as w2 on w2.rn<=w1.rn
  group by w1.rn ,w1.wait_type,w1.wait_time_s,w1.pct,w1.signal_pct
  having sum(w2.pct)-w1.pct<80 or w1.rn<=5
  order by w1.rn
收集等待信息