SQLskills SQL101: Why do some wait types need to be ignored?

SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

Wait statistics analysis is one of my favorite things to talk about because it’s so incredibly useful for performance tuning and can dramatically shorten the time it takes to zero in on the root cause of a performance problem. But you have to do it correctly. You can’t just do a SELECT * FROM sys.dm_os_wait_stats. Various people have published scripts online to aggregate and display wait statistics in an actionable way, and my script is one of the most popular (latest version is always in this post).

One question I’m often asked is why does my script have a list of wait types that it specifically filters out? The answer is that those wait types are what I call ‘benign’ – they’re usually not a problem but happen frequently enough from regular SQL Server operations that they would show up as the top waits and so would obscure the waits that you can do something about.

For instance, if I take my waits script and remove all the filtering of benign waits, the results on my laptop where I’m forcing a tempdb contention problem are as follows:

WaitType                            Wait_S    Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
----------------------------------- --------- ----------  -------- --------- ---------- --------- -------- -------- -----------------------------------------------------------------------
SLEEP_TASK                          123335.21 123326.43   8.77     5232828   10.68      0.0236    0.0236   0.0000   https://www.sqlskills.com/help/waits/SLEEP_TASK
DIRTY_PAGE_POLL                     82215.60  82214.61    0.98     808502    7.12       0.1017    0.1017   0.0000   https://www.sqlskills.com/help/waits/DIRTY_PAGE_POLL
HADR_FILESTREAM_IOMGR_IOCOMPLETION  82215.08  82214.43    0.65     163809    7.12       0.5019    0.5019   0.0000   https://www.sqlskills.com/help/waits/HADR_FILESTREAM_IOMGR_IOCOMPLETION
LOGMGR_QUEUE                        82213.89  82210.58    3.31     669980    7.12       0.1227    0.1227   0.0000   https://www.sqlskills.com/help/waits/LOGMGR_QUEUE
SQLTRACE_INCREMENTAL_FLUSH_SLEEP    82212.97  82212.94    0.03     20546     7.12       4.0014    4.0014   0.0000   https://www.sqlskills.com/help/waits/SQLTRACE_INCREMENTAL_FLUSH_SLEEP
REQUEST_FOR_DEADLOCK_SEARCH         82212.74  0.00        82212.74 16442     7.12       5.0002    0.0000   5.0002   https://www.sqlskills.com/help/waits/REQUEST_FOR_DEADLOCK_SEARCH
LAZYWRITER_SLEEP                    82210.41  82209.82    0.59     86524     7.12       0.9501    0.9501   0.0000   https://www.sqlskills.com/help/waits/LAZYWRITER_SLEEP
CHECKPOINT_QUEUE                    82204.96  82204.92    0.04     125       7.12       657.6396  657.6394 0.0003   https://www.sqlskills.com/help/waits/CHECKPOINT_QUEUE
XE_TIMER_EVENT                      82204.08  0.00        82204.08 37409     7.12       2.1974    0.0000   2.1974   https://www.sqlskills.com/help/waits/XE_TIMER_EVENT
QDS_CLEANUP_STALE_QUERIES_TASK_
                   MAIN_LOOP_SLEEP  82201.37  82201.36    0.01     1371      7.12       59.9572   59.9572  0.0000   https://www.sqlskills.com/help/waits/QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP
QDS_PERSIST_TASK_MAIN_LOOP_SLEEP    82201.29  82201.28    0.01     1371      7.12       59.9572   59.9572  0.0000   https://www.sqlskills.com/help/waits/QDS_PERSIST_TASK_MAIN_LOOP_SLEEP
SP_SERVER_DIAGNOSTICS_SLEEP         82200.36  0.00        82200.36 299612    7.12       0.2744    0.0000   0.2744   https://www.sqlskills.com/help/waits/SP_SERVER_DIAGNOSTICS_SLEEP
XE_DISPATCHER_WAIT                  82198.10  82198.10    0.00     686       7.12       119.8223  119.8223 0.0000   https://www.sqlskills.com/help/waits/XE_DISPATCHER_WAIT

All of these are benign waits happening on system threads. For instance, you can see that the lazy writer thread on my instance is waking up every 1 second or so to check for memory pressure in the buffer pool, finding none, and then sleeping again (that’s the LAZYWRITER_SLEEP wait type, showing an average of 0.95s average resource wait time in the AvgRes_S column). You can also see that nearly all of these waits have a total wait time of around 82, 200 seconds, which is how long my laptop has been running since its last reboot.

The point is that these waits always occur and if you don’t filter them out, they will show up as the most prevalent wait types on your instance, and they’re not related to performance issues.

When I put the filters back in, and re-run the script, I get the following output:

WaitType                            Wait_S    Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
----------------------------------- --------- ----------  -------- --------- ---------- --------- -------- -------- -----------------------------------------------------------------------
PAGELATCH_UP                        3451.97   3312.34     139.63   502282    56.73      0.0069    0.0066   0.0003   https://www.sqlskills.com/help/waits/PAGELATCH_UP
PAGELATCH_SH                        2324.96   1449.37     875.60   2030686   38.21      0.0011    0.0007   0.0004   https://www.sqlskills.com/help/waits/PAGELATCH_SH
LATCH_EX                            217.89    214.96      2.94     7628      3.58       0.0286    0.0282   0.0004   https://www.sqlskills.com/help/waits/LATCH_EX

Now I can see an indication of a problem and know to go look at my sys.dm_os_waiting_tasks script (latest version always here) for further investigation (and note the automatically-generated URLs which will take you to the relevant page of my waits library for explanation of the wait types and troubleshooting advice).

Bottom line: always make sure you’re filtering out benign wait types so you’re not trying to troubleshoot a problem that you can’t do anything about.

The post SQLskills SQL101: Why do some wait types need to be ignored? appeared first on Paul S. Randal.

关注dbDao.com的新浪微博

扫码加入微信Oracle小密圈,了解Oracle最新技术下载分享资源

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569