Wait statistics, or please tell me where it hurts
本文大意：
waits stats（waits and queues） 是sql server调优的一个重要环节。waits是sql server 跟踪的值，queue是线程等待的资源。当线程使用cpu是（running状态），当等待一个资源时移动到等待队列（suspended状态），并且从一个先进先出的队列中取出一个线程控制cpu执行。当线程获取到资源就开始等待cpu执行（runnable状态）并等待获取cpu。sql server 会跟踪从running状态到下一个running状态的时间，叫做等待时间（wait time），从runnable到running的时间叫做信号等待时间（signal wait time）
WITH
[Waits]
AS
(
SELECT
[wait_type]
,
[wait_time_ms]
/
1000.0
AS
[WaitS]
,
(
[wait_time_ms]
-
[signal_wait_time_ms]
)
/
1000.0
AS
[ResourceS]
,
[signal_wait_time_ms]
/
1000.0
AS
[SignalS]
,
[waiting_tasks_count]
AS
[WaitCount]
,
100.0
*
[wait_time_ms]
/
SUM
(
[wait_time_ms]
)
OVER
()
AS
[Percentage]
,
ROW_NUMBER
()
OVER
(
ORDER
BY
[wait_time_ms]
DESC
)
AS
[RowNum]
FROM
sys
.
dm_os_wait_stats
WHERE
[wait_type]
NOT
IN
(
N'CLR_SEMAPHORE'
,
N'LAZYWRITER_SLEEP'
,
N'RESOURCE_QUEUE'
,
N'SQLTRACE_BUFFER_FLUSH'
,
N'SLEEP_TASK'
,
N'SLEEP_SYSTEMTASK'
,
N'WAITFOR'
,
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION'
,
N'CHECKPOINT_QUEUE'
,
N'REQUEST_FOR_DEADLOCK_SEARCH'
,
N'XE_TIMER_EVENT'
,
N'XE_DISPATCHER_JOIN'
,
N'LOGMGR_QUEUE'
,
N'FT_IFTS_SCHEDULER_IDLE_WAIT'
,
N'BROKER_TASK_STOP'
,
N'CLR_MANUAL_EVENT'
,
N'CLR_AUTO_EVENT'
,
N'DISPATCHER_QUEUE_SEMAPHORE'
,
N'TRACEWRITE'
,
N'XE_DISPATCHER_WAIT'
,
N'BROKER_TO_FLUSH'
,
N'BROKER_EVENTHANDLER'
,
N'FT_IFTSHC_MUTEX'
,
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'
,
N'DIRTY_PAGE_POLL'
,
&
nbsp
;
N'SP_SERVER_DIAGNOSTICS_SLEEP'
)
)
SELECT
[W1]
.
[wait_type]
AS
[WaitType]
,
CAST
(
[W1]
.
[WaitS]
AS
DECIMAL
(
14
,
2
))
AS
[Wait_S]
,
CAST
(
[W1]
.
[ResourceS]
AS
DECIMAL
(
14
,
2
))
AS
[Resource_S]
,
CAST
(
[W1]
.
[SignalS]
AS
DECIMAL
(
14
,
2
))
AS
[Signal_S]
,
[W1]
.
[WaitCount]
AS
[WaitCount]
,
CAST
(
[W1]
.
[Percentage]
AS
DECIMAL
(
4
,
2
))
AS
[Percentage]
,
CAST
((
[W1]
.
[WaitS]
/
[W1]
.
[WaitCount]
)
AS
DECIMAL
(
14
,
4
))
AS
[AvgWait_S]
,
CAST
((
[W1]
.
[ResourceS]
/
[W1]
.
[WaitCount]
)
AS
DECIMAL
(
14
,
4
))
AS
[AvgRes_S]
,
CAST
((
[W1]
.
[SignalS]
/
[W1]
.
[WaitCount]
)
AS
DECIMAL
(
14
,
4
))
AS
[AvgSig_S]
FROM
[Waits]
AS
[W1]
INNER
JOIN
[Waits]
AS
[W2]
ON
[W2]
.
[RowNum]
<=
[W1]
.
[RowNum]
GROUP
BY
[W1]
.
[RowNum]
,
[W1]
.
[wait_type]
,
[W1]
.
[WaitS]
,
[W1]
.
[ResourceS]
,
[W1]
.
[SignalS]
,
[W1]
.
[WaitCount]
,
[W1]
.
[Percentage]
HAVING
SUM
(
[W2]
.
[Percentage]
)
-
[W1]
.
[Percentage]
<
95
;
-- percentage threshold
GO
这个sql可以用来产看95%以上的等待。
DBCC
SQLPERF
(
N'sys.dm_os_wait_stats'
,
CLEAR
);用来清空等待信息
作者对经常碰到的等待类型做出了解释：
CXPACKET:在并发查询中，某个线程等待其他线程完成时出现。可以使用cost threshold for parallelism，max degree of parallelism2个参数的配置，或者设置资源调控器来减少等待的发送，但往往不是解决问题的根本方法。
PAGEIOLATCH_XX:从磁盘读入到内存时发送，不一定是io问题，可能是执行计划问题。或者内存压力问题。
ASYNC_NETWORK_IO:通常在sql server等待客户端取走数据时发送，客户端生产大量数据，导致取数据很慢，往往是程序设计不合理造成。
WRITELOG:日志管理系统等待日志刷新到磁盘时发送。往往说明io子系统的问题，1.把符合分散到多个数据库上或者缩小长事务。可以使用sys.dm_io_virtual_file_stats检查日志的io问题
MSQL_XP: sql server等待扩展存储过程完成时发送，检查扩展存储过程代码
LCK_M_XX:线程等待锁的分配，说明线程堵塞
IO_COMPLETION:等待io完成时出现，往往说明io问题
SOS_SCHEDULER_YIELD:在等待spinlock时发现可能会浪费很多cpu因此，线程确定自动让出cpu
PAGELATCH_XX:在访问page时出现（buf闩）的等待。可能是热点页，GAM，SGAM，PFS可能会引起这个问题
LATCH_XX:非buf闩的等待（闩分为2种，buf闩和非buf闩，SQL Server 2008内部剖析与故障分析一书的6.6中有详细介绍）
PREEMPTIVE_XX:切换到抢占模式通过windows调度做相关操作时出现的等待
THREADPOOL:等待可用的workthreads
DBMIRROR_DBM_MUTEX:发送buffer时出现的等待，可能是镜像回话过多
RESOUCE_SEMAPHORE:查询语句等待分配内存时出现，可能是查询语句过大或者需求的内存过大。
MSQL_DG: sql server等待分布式查询完成时出现，说明分布式查询有问题
RESOUCE_SEMAPHORE_QUERY_COMPLIE:过大的并发编译，主要是重编译和无缓冲plan造成
MSSEARCH:全文查询等待
本文大意：
本文主要介绍了4个wait stats相关的DMV,和使用的相关sql
sys.dm_os_wait_stats，sys.dm_os_waiting_tasks， sys.dm_os_latch_stats， sys.dm_os_spinlock_stats
sys.dm_os_wait_stats:sql server 会把跟踪到的等待会记录，通过这个dmv反映，缺点是所有的等待都集中在一起无法等位具体sql，只能用于发现问题，使用的sql 可以查看上一篇文字
sys.dm_os_wait_tasks:通过这个dmv可以查看当前系统正在等待的等待类型
SELECT
[owt]
.
[session_id]
,
[owt]
.
[exec_context_id]
,
[owt]
.
[wait_duration_ms]
,
[owt]
.
[wait_type]
,
[owt]
.
[blocking_session_id]
,
[owt]
.
[resource_description]
,
[es]
.
[program_name]
,
[est]
.1
,
[est]
.
[dbid]
,
[eqp]
.
[query_plan]
,
[es]
.
[cpu_time]
,
[es]
.
[memory_usage]
FROM
sys
.
dm_os_waiting_tasks
[owt]
INNER
JOIN
sys
.
dm_exec_sessions
[es]
ON
[owt]
.
[session_id]
=
[es]
.
[session_id]
INNER
JOIN
sys
.
dm_exec_requests
[er]
ON
[es]
.
[session_id]
=
[er]
.
[session_id]
OUTER
APPLY
sys
.
dm_exec_sql_text
(
[er]
.
[sql_handle]
)
[est]
OUTER
APPLY
sys
.
dm_exec_query_plan
(
[er]
.
[plan_handle]
)
[eqp]
WHERE
[es]
.
[is_user_process]
=
1
ORDER
BY
[owt]
.
[session_id]
,
[owt]
.
[exec_context_id]
;
GO
sys.dm_os_latch_stats:latch分为2类buf latch 和非buf latch，闩是一个轻量级别的锁用来保护内存的访问和修改，若要获取一个闩，那么需要从running到suspended状态。其中非buf latch 和 wait_stats中的LATCH_XX相关
WITH
[Latches]
AS
(
SELECT
[latch_class]
,
[wait_time_ms]
/
1000.0
AS
[WaitS]
,
[waiting_requests_count]
AS
[WaitCount]
,
100.0
*
[wait_time_ms]
/
SUM
(
[wait_time_ms]
)
OVER
()
AS
[Percentage]
,
ROW_NUMBER
()
OVER
(
ORDER
BY
[wait_time_ms]
DESC
)
AS
[RowNum]
FROM
sys
.
dm_os_latch_stats
WHERE
[latch_class]
NOT
IN
(
N'BUFFER'
)
--AND [wait_time_ms] > 0
)
SELECT
[W1]
.
[latch_class]
AS
[LatchClass]
,
CAST
(
[W1]
.
[WaitS]
AS
DECIMAL
(
14
,
2
))
AS
[Wait_S]
,
[W1]
.
[WaitCount]
AS
[WaitCount]
,
CAST
(
[W1]
.
[Percentage]
AS
DECIMAL
(
14
,
2
))
AS
[Percentage]
,
CAST
((
[W1]
.
[WaitS]
/
[W1]
.
[WaitCount]
)
AS
DECIMAL
(
14
,
4
))
AS
[AvgWait_S]
FROM
[Latches]
AS
[W1]
INNER
JOIN
[Latches]
AS
[W2]
ON
[W2]
.
[RowNum]
<=
[W1]
.
[RowNum]
GROUP
BY
[W1]
.
[RowNum]
,
[W1]
.
[latch_class]
,
[W1]
.
[WaitS]
,
[W1]
.
[WaitCount]
,
[W1]
.
[Percentage]
HAVING
SUM
(
[W2]
.
[Percentage]
)
-
[W1]
.
[Percentage]
<
95
;
-- percentage threshold
GO
sys.dm_os_spinlock_stats:是一个轻量级的同步机制在访问特定的数据结构是会实用，并且使用时间很短，自旋锁不会产生重新调度的状况。自旋锁缺点或照成cpu的浪费
IF
EXISTS
(
SELECT
*
FROM
[tempdb]
.
[sys]
.
[objects]
WHERE
[name]
=
N'##TempSpinlockStats1'
)
DROP
TABLE
[##TempSpinlockStats1]
;
IF
EXISTS
(
SELECT
*
FROM
[tempdb]
.
[sys]
.
[objects]
WHERE
[name]
=
N'##TempSpinlockStats2'
)
DROP
TABLE
[##TempSpinlockStats2]
;
GO
-- Baseline
SELECT
*
INTO
[##TempSpinlockStats1]
FROM
sys
.
dm_os_spinlock_stats
WHERE
[collisions]
>
0
ORDER
BY
[name]
;
GO
-- Now do something
DBCC
CHECKDB
(
N'SalesDB'
)
WITH
NO_INFOMSGS
;
GO
-- Capture updated stats
SELECT
*
INTO
[##TempSpinlockStats2]
FROM
sys
.
dm_os_spinlock_stats
WHERE
[collisions]
>
0
ORDER
BY
[name]
;
GO
-- Diff them
SELECT
'***'
AS
[New]
,
[ts2]
.
[name]
AS
[Spinlock]
,
[ts2]
.
[collisions]
AS
[DiffCollisions]
,
[ts2]
.
[spins]
AS
[DiffSpins]
,
[ts2]
.
[spins_per_collision]
AS
[SpinsPerCollision]
,
[ts2]
.
[sleep_time]
AS
[DiffSleepTime]
,
[ts2]
.
[backoffs]
AS
[DiffBackoffs]
FROM
[##TempSpinlockStats2]
[ts2]
LEFT
OUTER
JOIN
[##TempSpinlockStats1]
[ts1]
ON
[ts2]
.
[name]
=
[ts1]
.
[name]
WHERE
[ts1]
.
[name]
IS
NULL
UNION
SELECT
''
AS
[New]
,
[ts2]
.
[name]
AS
[Spinlock]
,
[ts2]
.
[collisions]
-
[ts1]
.
[collisions]
AS
[DiffCollisions]
,
[ts2]
.
[spins]
-
[ts1]
.
[spins]
AS
[DiffSpins]
,
CASE
(
[ts2]
.
[spins]
-
[ts1]
.
[spins]
)
WHEN
0
THEN
0
ELSE
(
[ts2]
.
[spins]
-
[ts1]
.
[spins]
)
/
(
[ts2]
.
[collisions]
-
[ts1]
.
[collisions]
)
END
AS
[SpinsPerCollision]
,
[ts2]
.
[sleep_time]
-
[ts1]
.
[sleep_time]
AS
[DiffSleepTime]
,
[ts2]
.
[backoffs]
-
[ts1]
.
[backoffs]
AS
[DiffBackoffs]
FROM
[##TempSpinlockStats2]
[ts2]
LEFT
OUTER
JOIN
[##TempSpinlockStats1]
[ts1]
ON
[ts2]
.
[name]
=
[ts1]
.
[name]
WHERE
[ts1]
.
[name]
IS
NOT
NULL
AND
[ts2]
.
[collisions]
-
[ts1]
.
[collisions]
>
0
ORDER
BY
[New]
DESC
,
[Spinlock]
ASC
;
GO
SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock
本文大意：
SOS_SCHEDULER_YIELD自动让步让其他线程运行，往往出现在cpu使用比较高的代码中。当放弃查看并等待自旋锁的访问时不是显式的退让，而是直接sleep，资源退让后，直接到了runnable队列中。当出现大量这里等待时就需要关心性能问题。作者用一个例子演示，里面出现了大量的SOS_SCHEDULER_YIELD等待。使用sys.dm_os_waiting_stats没有发现什么问题，当使用sys.dm_os_spinlock_stats是出现大量的LOCK_HASH，SQL Server并没有继续自选，而是使用自动退让的方法。
为了解决这一类问题有以下2个方法：
把冲突分散到多个数据库中
使用中间件或者客户端缓存，并使用数据修改的通知机制
本文大意：
MAXDOP是根据环境的不同，设置也是不同的。
1.在oltp下MAXDOP设置为1，并使用查询提示的MAXDOP去覆盖，往往是不错的选择
2.在混合负载下使用MAXDOP，总会让另外一种负载性能有问题最好的办法是设置为1，并且使用查询提示或者资源调控器来处理
3.当出现CXPACKETd等待时，应该先考虑清楚是什么问题造成的，可能是统计信息过期或者统计信息不正确
4.考虑使用并发阀值
本文大意：
作者使用例子说明语句级别的wait stats的收集和分析。
本文大意：
为了保证非buf数据结构的线程级安全，就必须要使用同步机制，要不就是闩要不就是自选。当使用频繁，使用闩开销太大，使用时间很短的情况下就会使用自选锁。若在统计信息里面发现是LATCH_XX等待有问题，那么为了进一步缩小范围应该查询sys.dm_os_latch_stats,也可以再sys.dm_os_waiting_tasks中的resource_description中看到闩锁类型。一下是10个比较常见的闩锁类型：
ACCESS_METHODS_DATASET_PARENT,ACCESS_METHODS_SCAN_RANGE_GENERATOR:当并发扫描给定一个pageid 范围让各个线程扫描是会出现，往往伴随出现LATCH_XX,CXPACKET,PAGEIOLATCH_XX。
ACCESS_METHODS_HOBT_COUNT:hobt页和行计数器的访问时出现，可能是单表中出现大量dml引起。
TRACE_CONTROLLER:这个闩缓存跟踪的很多事情，出现冲突说明有多个trace的问题
DBCC_MULTIOBJECT_SCANNER:只有在DBCC CHECK允许并行时出现
ACCESS_METHODS_HOBT_VIRTUAL_ROOT:这个闩用于访问索引元数据和根页，出现冲突主要是根页的分页，可能是小索引上的大量并发移动
FGCB_ADD_REMOVE:出现在文件组中文件的添加删除，文件增长，填充率重新计算（每8192分配就会重新计算），文件组中文件循环分配信息（多文件的数据库可能会出现）。
DATABASE_MIRRORING_CONNECTION:控制镜像消息流，出现冲突可能是有太多数据库回话。
NESTING_TRANSACTION_FULL:用于控制访问并发嵌套事务的事务访问结构。调用并发操作的查询必须为每个并发事务启动子事务，这些事务是并发嵌套查询的子事务。出现这个问题往往是没必要的并发导致但是不能定论是否如此
本文转自 Fanr_Zh 博客园博客，原文链接：http://www.cnblogs.com/Amaranthus/archive/2013/05/03/3056203.html
，如需转载请自行联系原作者
