首页 > 代码库 > sql server发生的等待统计信息

sql server发生的等待统计信息

2024-09-27 08:22:01   205人阅读

SELECT * FROM sys.dm_os_wait_statsWHERE wait_time_ms>0ORDER BY wait_time_ms DESC

只有sql server重启的时候才会自动清除等待统计信息，可以通过 DBCC SQLPERF("sys.dm_os_wait_stats", CLEAR)命令清除统计信息。

通过一个临时表和waitfor delay 语句跟踪一段时间内发生的变化，从而判断当前正在发生的等待。

IF OBJECT_ID(‘tempdb..#wait_stats‘) IS NOT NULLDROP TABLE #wait_statsSELECT * INTO #wait_statsFROM sys.dm_os_wait_statsWAITFOR DELAY ‘00:00:05‘SELECT ws1.wait_type,    ws2.waiting_tasks_count - ws1.waiting_tasks_count    AS waiting_tasks_count,    ws2.wait_time_ms - ws1.wait_time_ms AS wait_time_ms,CASE WHEN ws2.max_wait_time_ms > ws1.max_wait_time_ms THEN ws2.max_wait_time_msELSE ws1.max_wait_time_msEND AS max_wait_time_ms,    ws2.signal_wait_time_ms - ws1.signal_wait_time_ms    AS signal_wait_time_ms,    (ws2.wait_time_ms - ws1.wait_time_ms)-(ws2.signal_wait_time_ms - ws1.signal_wait_time_ms)    AS resource_wait_time_msFROM sys.dm_os_wait_stats AS ws2JOIN #wait_stats AS ws1 ON ws1.wait_type=ws2.wait_typeWHERE ws2.wait_time_ms - ws1.wait_time_ms > 0ORDER BY ws2.wait_time_ms - ws1.wait_time_ms DESC

在查看资源等待时间和信号等待时间的差别的时候，最好观察两者在总时间内所占的比例。

SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms,    SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms,    SUM(signal_wait_time_ms) * 1.0 / SUM(wait_time_ms) * 100 AS signal_wait_percent,　　 SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM(wait_time_ms) * 100 AS resource_wait_percentFROM sys.dm_os_wait_stats

 

sql server发生的等待统计信息


联系
我们