{"id":470,"date":"2012-08-16T12:06:00","date_gmt":"2012-08-16T12:06:00","guid":{"rendered":"\/blogs\/erin\/post\/Finding-When-Wait-Stats-Were-Last-Cleared.aspx"},"modified":"2017-04-13T09:19:07","modified_gmt":"2017-04-13T16:19:07","slug":"figuring-out-when-wait-stats-were-last-cleared","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/","title":{"rendered":"Figuring Out When Wait Statistics Were Last Cleared"},"content":{"rendered":"

One of the topics covered during IE2 is Waits and Queues, and this week one of the attendees asked if there was a way to know when wait statistics were last cleared. It turns out there is a post<\/a> about it on the SQLOS & Cloud Infrastructure Team Blog<\/a>, and what you do is look for the SQLTRACE_INCREMENTAL_FLUSH_SLEEP wait_type in sys.dm_os_wait_stats. The value for wait_tims_ms is the time since wait stats were last cleared.<\/p>\n

Since I talk about clearing wait stats in my DBCC session I decided this information would be useful to include, and then decided to just write a query to figure it out.\u00a0 The query is below, as is a query to show the last time the instance was restarted.\u00a0 Edit: Note that this query is only for SQL Server 2008R2 and higher.\u00a0 The SQLTRACE_INCREMENTAL_FLUSH_SLEEP wait_type did not exist in previous versions of SQL Server.\u00a0 If I can find an alternate wait_type for use, I will post back.<\/em><\/p>\n

Just to be clear, I am not advocating that you immediately clear wait stats on your server.\u00a0 In fact, I\u2019d be more interested to know whether wait stats have been cleared since the instance was last restarted.\u00a0 Let me back up a bit\u2026\u00a0 If you\u2019re not familiar with wait statistics, I suggest starting with Paul\u2019s post, Wait statistics, or please tell me where it hurts.\u00a0 Whether you have performance issues on your server or not, I highly recommend looking at wait stats regularly to understand what your \u201cnormal\u201d waits are.\u00a0 If you are having issues, waits can be a starting point to sorting out what\u2019s causing the problem.\u00a0 But wait stats should not be viewed in a vacuum \u2013 always use other counters or information to correlate findings.\u00a0 Once you have a basic understanding of your wait stats, you may decide to capture them on a regular basis.\u00a0 But wait stats are cumulative, and will only reset when you restart the instance, or if you clear them manually.\u00a0 They can be cleared using:<\/p>\n

\r\nDBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);\r\n<\/pre>\n
There are a variety of reasons to clear wait stats, but it shouldn\u2019t be done arbitrarily.\u00a0 You may clear them because you\u2019ve made a change to the system and want to understand if and how that change affects wait statistics.\u00a0 You may clear wait stats every morning at 5 AM because backups and other maintenance tasks run at night, but you want to understand performance during business hours when users are the system and therefore decide to filter out (or investigate separately) waits from those tasks.\u00a0 In the latter case, you probably have a scheduled job to clear the wait stats, and other DBAs may or may not know about this job.\u00a0 If another DBA decides to clear wait stats for some reason, without telling you, they may affect the wait information you\u2019ve been capturing.\u00a0 As such, I would always verify with colleagues whether or not wait stats are cleared regularly and this script will give you an idea of whether are being cleared or not.\u00a0 However, even if they haven\u2019t been cleared since the last restart, I would still perform some due diligence and verify it won\u2019t alter anyone else\u2019s data.\u00a0 I am interested to hear different ideas for how this information could be used, so feel free to post back or email me.\u00a0 And if you\u2019re not currently looking at your wait stats regularly, please start!<\/p>\n
\r\n\/* when were wait stats last cleared? *\/\r\nSELECT\r\n[wait_type],\r\n[wait_time_ms],\r\nDATEADD(ms,-[wait_time_ms],getdate()) AS [Date\/TimeCleared],\r\nCASE\r\nWHEN [wait_time_ms] < 1000 THEN CAST([wait_time_ms] AS VARCHAR(15)) + ' ms'\r\nWHEN [wait_time_ms] between 1000 and 60000 THEN CAST(([wait_time_ms]\/1000) AS VARCHAR(15)) + ' seconds'\r\nWHEN [wait_time_ms] between 60001 and 3600000 THEN CAST(([wait_time_ms]\/60000) AS VARCHAR(15)) + ' minutes'\r\nWHEN [wait_time_ms] between 3600001 and 86400000 THEN CAST(([wait_time_ms]\/3600000) AS VARCHAR(15)) + ' hours'\r\nWHEN [wait_time_ms] > 86400000 THEN CAST(([wait_time_ms]\/86400000) AS VARCHAR(15)) + ' days'\r\nEND [TimeSinceCleared]\r\nFROM [sys].[dm_os_wait_stats]\r\nWHERE [wait_type] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';\r\n\r\n\/* check SQL Server start time - 2008 and higher *\/\r\nSELECT\r\n[sqlserver_start_time]\r\nFROM [sys].[dm_os_sys_info];\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
One of the topics covered during IE2 is Waits and Queues, and this week one of the attendees asked if there was a way to know when wait statistics were last cleared. It turns out there is a post about it on the SQLOS & Cloud Infrastructure Team Blog, and what you do is look […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,18],"tags":[],"yoast_head":"\nHow to Determine When SQL Server Wait Statistics Were Last Reset<\/title>\n<meta name=\"description\" content=\"SQL Server Wait Statistics are reset when an instance restarts, and can also be manually reset. Find when they were last reset using the included script.\" \/>\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\/erin\/figuring-out-when-wait-stats-were-last-cleared\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Determine When SQL Server Wait Statistics Were Last Reset\" \/>\n<meta property=\"og:description\" content=\"SQL Server Wait Statistics are reset when an instance restarts, and can also be manually reset. Find when they were last reset using the included script.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2012-08-16T12:06:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:07+00:00\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\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\/erin\/figuring-out-when-wait-stats-were-last-cleared\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/\",\"name\":\"How to Determine When SQL Server Wait Statistics Were Last Reset\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2012-08-16T12:06:00+00:00\",\"dateModified\":\"2017-04-13T16:19:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"SQL Server Wait Statistics are reset when an instance restarts, and can also be manually reset. Find when they were last reset using the included script.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Figuring Out When Wait Statistics Were Last Cleared\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Determine When SQL Server Wait Statistics Were Last Reset","description":"SQL Server Wait Statistics are reset when an instance restarts, and can also be manually reset. Find when they were last reset using the included script.","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\/erin\/figuring-out-when-wait-stats-were-last-cleared\/","og_locale":"en_US","og_type":"article","og_title":"How to Determine When SQL Server Wait Statistics Were Last Reset","og_description":"SQL Server Wait Statistics are reset when an instance restarts, and can also be manually reset. Find when they were last reset using the included script.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/","og_site_name":"Erin Stellato","article_published_time":"2012-08-16T12:06:00+00:00","article_modified_time":"2017-04-13T16:19:07+00:00","author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/","name":"How to Determine When SQL Server Wait Statistics Were Last Reset","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2012-08-16T12:06:00+00:00","dateModified":"2017-04-13T16:19:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"SQL Server Wait Statistics are reset when an instance restarts, and can also be manually reset. Find when they were last reset using the included script.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/figuring-out-when-wait-stats-were-last-cleared\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Figuring Out When Wait Statistics Were Last Cleared"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/470"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=470"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/470\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}