{"id":562,"date":"2011-06-02T12:59:00","date_gmt":"2011-06-02T12:59:00","guid":{"rendered":"\/blogs\/paul\/post\/The-pros-and-cons-of-trace-flags.aspx"},"modified":"2017-04-13T11:41:18","modified_gmt":"2017-04-13T18:41:18","slug":"the-pros-and-cons-of-trace-flags","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/","title":{"rendered":"The pros and cons of trace flags"},"content":{"rendered":"<p>\n<font face=\"verdana,geneva\" size=\"2\">If I told you about an undocumented mod you could make to your car&#39;s brakes to make them more efficient, would you try it? What if a random mechanic told you? What if someone who&#39;s an expert in your make and model of car told you? What if the car manufacturer asked&nbsp;you to try it to solve a brake problem &#8211; and just drive around a bit to see what happens and see if it stops the car crashing again?<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">:-)<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">A month ago I kicked off a <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/survey-whats-your-comfort-level-with-trace-flags-in-production\/\" target=\"_blank\"><font face=\"verdana,geneva\" size=\"2\">survey<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> about your comfort level with using trace flags in production and now it&#39;s time to look at the results. <\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">&nbsp;<img decoding=\"async\" src=\"\/blogs\/paul\/wp-content\/uploads\/2011\/6\/traceflags.jpg\" alt=\"\" \/><\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The Other values are:<\/font>\n<\/p>\n<ul>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">15 x &quot;It depends!!!!&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">7 x &quot;We use them where specifically needed for short amounts of time example &#8211; the deadlock trace flags (1204 and 1222) to dump the details to the error log. in some cases as needed we will use startup trace flags &#8211; example 845 on 2005 x64 standard to allow.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">6 x &quot;If Paul says they&#39;re okay.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">4 x &quot;We use Trace flags which well documented by MS *and* reputable sources and tested in a controlled manner to ensure continued stability.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">3 x &quot;Deadlock full details &#8211; because deadlocks are rare, I need full details every time because lightening doesn&#39;t strike twice.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">3 x &quot;I feel comfortable, each trace does different things, if we need it we&#39;ll use it.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">3 x &quot;I rarely if ever use a trace flag in production but I&#39;m comfortable using one if it&#39;s documented by a reputable source and it makes sense for us.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">2 x &quot;Enable it if css asks, or it its documented in ms official documentation but test before implementing.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">&quot;Either from MS source or from a non-MS but reputable source.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">&quot;If I hear\/read about it from a non-MS, but reputable source, to fix a bug or boost performance.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">&quot;If some doc is evident and MS knows what the TF actually does, instead of their telling you to turn it on without realizing it affects more than they thought (ex\/ TF2330 stops all index usage stats gathering).&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">&quot;Only after testing in a dev\/test environment first.&quot;<\/font>\n\t<\/div>\n<\/li>\n<li>\n<div>\n\t<font face=\"verdana,geneva\" size=\"2\">&quot;Will use them on need basis &#8211; for example &#8211; deadlock trace 1222 to log the deadlock info in the error log.&quot;<\/font>\n\t<\/div>\n<\/li>\n<\/ul>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Trace flags are interesting things. They change the behavior of SQL Server in a huge variety of sometimes useful, sometimes dangerous, and sometimes bizarre ways. There are many hundreds of trace flags, most of which are undocumented &#8211; and usually for the very good reasons that they&#39;re only supposed to be used for testing, for debugging problems with Product Support, or to enable specific bug fixes that may be detrimental to others.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">I&nbsp;never advise turning on a trace flag (either when teaching a class or discussing with a client) unless I&#39;m rock-solid 100% sure about what it does and what the effects are. You should do the same when considering trace flag use on a production system.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">The example I like to use is trace flag 661 that disables the ghost cleanup background task. It&#39;s documented in <\/font><a href=\"https:\/\/support.microsoft.com\/en-us\/help\/920093\/tuning-options-for-sql-server-when-running-in-high-performance-workloads\"><font face=\"verdana,geneva\" size=\"2\">KB&nbsp;920093<\/font><\/a><font face=\"verdana,geneva\" size=\"2\"> and I&#39;ve blogged about it <\/font><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/turning-off-the-ghost-cleanup-task-for-a-performance-gain\/\"><font face=\"verdana,geneva\" size=\"2\">here<\/font><\/a><font face=\"verdana,geneva\" size=\"2\">&nbsp;(and in a few other posts). It&#39;s a very useful trace flag which is safe to use, is documented by MS, is referenced by trustworthy sources (well, me :-) and can give a performance boost under some circumstances. However,&nbsp;it can have horrible side-effects if you don&#39;t understand the ramifications of turning it on &#8211; deleted data and index records stay around until the next index rebuild\/reorganize, leading to page splits and data file growth.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Where trace flags are concerned, you need to test and validate before enabling in production &#8211; no matter *who* you hear about it from.<\/font>\n<\/p>\n<p>\n<font face=\"verdana,geneva\" size=\"2\">Be careful out there!<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"<p>If I told you about an undocumented mod you could make to your car&#39;s brakes to make them more efficient, would you try it? What if a random mechanic told you? What if someone who&#39;s an expert in your make and model of car told you? What if the car manufacturer asked&nbsp;you to try it [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[91,96],"tags":[],"class_list":["post-562","post","type-post","status-publish","format-standard","hentry","category-surveys","category-trace-flags"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The pros and cons of trace flags - 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\/the-pros-and-cons-of-trace-flags\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The pros and cons of trace flags - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"If I told you about an undocumented mod you could make to your car&#039;s brakes to make them more efficient, would you try it? What if a random mechanic told you? What if someone who&#039;s an expert in your make and model of car told you? What if the car manufacturer asked&nbsp;you to try it [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-06-02T12:59:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:41:18+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=\"3 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\/the-pros-and-cons-of-trace-flags\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/\",\"name\":\"The pros and cons of trace flags - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-06-02T12:59:00+00:00\",\"dateModified\":\"2017-04-13T18:41:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The pros and cons of trace flags\"}]},{\"@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":"The pros and cons of trace flags - 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\/the-pros-and-cons-of-trace-flags\/","og_locale":"en_US","og_type":"article","og_title":"The pros and cons of trace flags - Paul S. Randal","og_description":"If I told you about an undocumented mod you could make to your car&#39;s brakes to make them more efficient, would you try it? What if a random mechanic told you? What if someone who&#39;s an expert in your make and model of car told you? What if the car manufacturer asked&nbsp;you to try it [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/","og_site_name":"Paul S. Randal","article_published_time":"2011-06-02T12:59:00+00:00","article_modified_time":"2017-04-13T18:41:18+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/","name":"The pros and cons of trace flags - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-06-02T12:59:00+00:00","dateModified":"2017-04-13T18:41:18+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-pros-and-cons-of-trace-flags\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The pros and cons of trace flags"}]},{"@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\/562","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=562"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/562\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=562"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=562"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=562"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}