分析实例级等待

 优化方法论的

第一步是用DMV在实例级找出那些等待类型占用了大部分的等待时间。

  下面的查询返回你系统中的等待

SELECT
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  max_wait_time_ms,
  signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type;

DMV重最后一次开始累计值，如果需要重新设置DMV的值则需要运行下面的语句

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
DMV 包含下面列属性
wait_type 等待类型
waiting_tasks_count,表示该类等待的等待数；
wait_time_ms 他是以毫秒为单位的该类等待的总等待时间
max_wait_time_ms;signal_wait_time 他是正在等待的线程重收到信号通知到开始运行之间的时间差

以下查询使在到在系统中等待时间的 90% 的总数积聚的最高等待隔离

WITH Waits AS
(
  SELECT
    wait_type,
    wait_time_ms / 1000. AS 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
  FROM sys.dm_os_wait_stats
  WHERE wait_type NOT LIKE '%SLEEP%'
  -- filter out additional irrelevant waits
)
SELECT
  W1.wait_type,
  CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
  CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
  CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_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
HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold
ORDER BY W1.rn;

把信息保存到一个表然后定时更新

Run the following code to create the WaitStats table:

USE Performance;
GO
IF OBJECT_ID('dbo.WaitStats') IS NOT NULL
  DROP TABLE dbo.WaitStats;
GO

SELECT GETDATE() AS dt,
  wait_type, waiting_tasks_count, wait_time_ms,
  max_wait_time_ms, signal_wait_time_ms
INTO dbo.WaitStats
FROM sys.dm_os_wait_stats
WHERE 1 = 2;

ALTER TABLE dbo.WaitStats
  ADD CONSTRAINT PK_WaitStats PRIMARY KEY(dt, wait_type);
CREATE INDEX idx_type_dt ON dbo.WaitStats(wait_type, dt);
定义一个定时运行的任务。使用下面代码重DMV中加载当前数据
INSERT INTO Performance.dbo.WaitStats
SELECT GETDATE(),
 wait_type.waiting_tasks_count,
wait_time_ms,
max_wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats;