{"id":1661,"date":"2018-10-23T00:26:06","date_gmt":"2018-10-23T00:26:06","guid":{"rendered":"http:\/\/www.theSQLReport.com\/?p=1661"},"modified":"2018-10-23T00:31:35","modified_gmt":"2018-10-23T00:31:35","slug":"is-trace-flag-8001-still-relevant","status":"publish","type":"post","link":"https:\/\/www.theSQLReport.com\/?p=1661","title":{"rendered":"Is Trace Flag 8001 still relevant"},"content":{"rendered":"

After reading an old article about the SQL Server DMV sys.dm_os_wait_stats running on SQL Server 2005.\u00a0 I learned about the undocumented trace flag 8001, which enables all the wait types to displayed when DMV sys.dm_os_wait_stats is run.\u00a0 Microsoft determined that is was not necessary that we see all the wait types when using this DMV.\u00a0 Most of the wait types hidden from us were related to things like the startup & shutdown of SQL Server.<\/p>\n

When tested on a SQL Server 2005 instance, we see 49 more wait types are available when the trace flag is enabled.<\/p>\n

\"\"<\/a><\/p>\n

When first tested this trace flag in SQL Server 2016, I found no difference in the count of wait types for the DMV sys.dm_os_wait_stats when the trace flag was enabled.<\/p>\n

\"\"<\/a><\/p>\n

A little more digging, and found that this trace flag was only usable for SQL Server 2005.\u00a0 Starting with SQL Server 2008 (before R2), Microsoft made all wait types available for the DMV sys.dm_os_wait_stats.<\/p>\n

Below is the queries I ran to get the results above:<\/p>\n

SET NOCOUNT ON;\r\n\r\nselect @@version;\r\ngo\r\n\r\nselect count(*) as 'count from sys.dm_os_wait_stats without trace flag 8001 enabled' \r\nfrom sys.dm_os_wait_stats;\r\ngo\r\n\r\ndbcc traceon (8001, -1);\r\ngo\r\n\r\ndbcc tracestatus;\r\ngo\r\n\r\nselect count(*) as 'count from sys.dm_os_wait_stats with trace flag 8001 enabled' \r\nfrom sys.dm_os_wait_stats;\r\ngo\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
After reading an old article about the SQL Server DMV sys.dm_os_wait_stats running on SQL Server 2005.\u00a0 I learned about the undocumented trace flag 8001, which enables all the wait types … Continue Reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":1662,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[137,116],"class_list":["post-1661","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","tag-sys-dm_os_wait_stats","tag-trace-flag"],"_links":{"self":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1661"}],"collection":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1661"}],"version-history":[{"count":6,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1661\/revisions"}],"predecessor-version":[{"id":1671,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/posts\/1661\/revisions\/1671"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=\/wp\/v2\/media\/1662"}],"wp:attachment":[{"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1661"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1661"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.theSQLReport.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1661"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}