{"id":4888,"date":"2018-05-23T15:50:35","date_gmt":"2018-05-23T22:50:35","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4888"},"modified":"2018-05-23T17:48:09","modified_gmt":"2018-05-24T00:48:09","slug":"sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/","title":{"rendered":"SQLskills SQL101: Why do some wait types need to be ignored?"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0<strong>SQLskills SQL101<\/strong>\u00a0blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\" rel=\"noopener noreferrer\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p style=\"text-align: justify;\">Wait statistics analysis is one of my favorite things to talk about because it&#8217;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&#8217;t just do a <em>SELECT * FROM sys.dm_os_wait_stats<\/em>. 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 <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/wait-statistics-or-please-tell-me-where-it-hurts\/\" target=\"_blank\" rel=\"noopener noreferrer\">this post<\/a>).<\/p>\n<p style=\"text-align: justify;\">One question I&#8217;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 &#8216;benign&#8217; &#8211; they&#8217;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.<\/p>\n<p style=\"text-align: justify;\">For instance, if I take my waits script and remove all the filtering of benign waits, the results on my laptop where I&#8217;m forcing a <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention\/\" target=\"_blank\" rel=\"noopener noreferrer\">tempdb contention problem<\/a>\u00a0are as follows:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nWaitType                            Wait_S    Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help\/Info URL\r\n----------------------------------- --------- ----------  -------- --------- ---------- --------- -------- -------- -----------------------------------------------------------------------\r\nSLEEP_TASK                          123335.21 123326.43   8.77     5232828   10.68      0.0236    0.0236   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/SLEEP_TASK\r\nDIRTY_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\r\nHADR_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\r\nLOGMGR_QUEUE                        82213.89  82210.58    3.31     669980    7.12       0.1227    0.1227   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/LOGMGR_QUEUE\r\nSQLTRACE_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\r\nREQUEST_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\r\nLAZYWRITER_SLEEP                    82210.41  82209.82    0.59     86524     7.12       0.9501    0.9501   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/LAZYWRITER_SLEEP\r\nCHECKPOINT_QUEUE                    82204.96  82204.92    0.04     125       7.12       657.6396  657.6394 0.0003   https:\/\/www.sqlskills.com\/help\/waits\/CHECKPOINT_QUEUE\r\nXE_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\r\nQDS_CLEANUP_STALE_QUERIES_TASK_\r\n                   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\r\nQDS_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\r\nSP_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\r\nXE_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\r\n<\/pre>\n<p style=\"text-align: justify;\">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&#8217;s the <em>LAZYWRITER_SLEEP<\/em> wait type, showing an average of 0.95s average resource wait time in the\u00a0<em>AvgRes_S<\/em> column). You can also see that\u00a0nearly all\u00a0of 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.<\/p>\n<p style=\"text-align: justify;\">The point is that these waits always occur and if you don&#8217;t filter them out, they will show up as the most prevalent wait types on your instance, and they&#8217;re not related to performance issues.<\/p>\n<p>When I put the filters back in, and re-run the script, I get the following output:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nWaitType                            Wait_S    Resource_S  Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help\/Info URL\r\n----------------------------------- --------- ----------  -------- --------- ---------- --------- -------- -------- -----------------------------------------------------------------------\r\nPAGELATCH_UP                        3451.97   3312.34     139.63   502282    56.73      0.0069    0.0066   0.0003   https:\/\/www.sqlskills.com\/help\/waits\/PAGELATCH_UP\r\nPAGELATCH_SH                        2324.96   1449.37     875.60   2030686   38.21      0.0011    0.0007   0.0004   https:\/\/www.sqlskills.com\/help\/waits\/PAGELATCH_SH\r\nLATCH_EX                            217.89    214.96      2.94     7628      3.58       0.0286    0.0282   0.0004   https:\/\/www.sqlskills.com\/help\/waits\/LATCH_EX\r\n<\/pre>\n<p style=\"text-align: justify;\">Now I can see an indication of a problem and know to go look at my <em>sys.dm_os_waiting_tasks<\/em> script (latest version always <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/updated-sys-dm_os_waiting_tasks-script-2\/\" target=\"_blank\" rel=\"noopener noreferrer\">here<\/a>) 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).<\/p>\n<p style=\"text-align: justify;\">Bottom line: always make sure you&#8217;re filtering out benign wait types so you&#8217;re not trying to troubleshoot a problem that you can&#8217;t do anything about.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Wait statistics analysis is one of my [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66,108,101],"tags":[],"class_list":["post-4888","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-sql101","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Why do some wait types need to be ignored? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Why do some wait types need to be ignored? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Wait statistics analysis is one of my [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2018-05-23T22:50:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-05-24T00:48:09+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/\",\"name\":\"SQLskills SQL101: Why do some wait types need to be ignored? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2018-05-23T22:50:35+00:00\",\"dateModified\":\"2018-05-24T00:48:09+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Why do some wait types need to be ignored?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQLskills SQL101: Why do some wait types need to be ignored? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Why do some wait types need to be ignored? - Paul S. Randal","og_description":"SQLskills has an ongoing\u00a0initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019re all blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our\u00a0SQLskills SQL101\u00a0blog posts, check out\u00a0SQLskills.com\/help\/SQL101. Wait statistics analysis is one of my [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/","og_site_name":"Paul S. Randal","article_published_time":"2018-05-23T22:50:35+00:00","article_modified_time":"2018-05-24T00:48:09+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/","name":"SQLskills SQL101: Why do some wait types need to be ignored? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2018-05-23T22:50:35+00:00","dateModified":"2018-05-24T00:48:09+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-why-do-some-wait-types-need-to-be-ignored\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Why do some wait types need to be ignored?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4888","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4888"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4888\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4888"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}