{"id":781,"date":"2016-11-09T06:00:04","date_gmt":"2016-11-09T14:00:04","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=781"},"modified":"2017-04-13T09:19:33","modified_gmt":"2017-04-13T16:19:33","slug":"trace-flag-information-in-query-plans","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/","title":{"rendered":"Trace Flag Information in Query Plans"},"content":{"rendered":"

I was perusing the release notes for SQL Server 2014 SP2<\/a> and found this gem:<\/p>\n

Information about enabled trace flags is added to the showplan XML in SQL Server 2014 SP2<\/a><\/p>\n

Ohhhhhh, very cool.\u00a0 This is great information for troubleshooting!<\/p>\n

I fired up my 2014 VM and applied SP2, then verified the trace flags I had enabled for the instance:<\/p>\n

\r\nDBCC TRACESTATUS;\r\nGO\r\n<\/pre>\n
\"Trace<\/a>
Trace flags enabled<\/figcaption><\/figure>\n
I have three trace flags enabled, which we typically recommend for all installations*.<\/p>\n
So let\u2019s run a few queries and then check the plan.\u00a0 For the first query I\u2019m not adding any other trace flags:<\/p>\n
\r\nSELECT [p].[ProductID],[p].[StandardCost], [p].[ListPrice], [pi].[Quantity], [p].[Name]\r\nFROM [Production].[Product] [p]\r\nJOIN [Production].[ProductInventory] [pi] ON [p].[ProductID] = [pi].[ProductID]\r\nWHERE [p].[SellEndDate] IS NOT NULL\r\nAND [pi].[Quantity]\u00a0 < 1000;\r\nGO\r\n<\/pre>\n
\"Query<\/a>
Query Plan<\/figcaption><\/figure>\n
The plan is nothing too crazy \u2013 it\u2019s not great because it\u2019s scanning the clustered indexes, but I\u2019m not tuning here so we\u2019ll ignore that for now.\u00a0 But notice that when I click on the SELECT operator with the Properties window open, you\u2019ll see an entry for TraceFlags at the bottom:<\/p>\n
\"Properties<\/a>
Properties for the SELECT operator<\/figcaption><\/figure>\n
If we expand that, we can see the three trace flags that I have enabled, and it\u2019s noted whether they are enabled globally or locally, and at compile time (IsCompileTime True) and run time (IsCompileTime False):<\/p>\n
\"Trace<\/a>
Trace flag information from the properties window<\/figcaption><\/figure>\n
And I can also go directly to the XML to find the information:<\/p>\n
\"Trace<\/a>
Trace flag information from the plan XML<\/figcaption><\/figure>\n
Cool.\u00a0 What happens if I use a trace flag, like 9130 to push out residual predicates into a FILTER operator, in a query hint?<\/p>\n
\r\nSELECT [p].[ProductID],[p].[StandardCost], [p].[ListPrice], [pi].[Quantity], [p].[Name]\r\nFROM [Production].[Product] [p]\r\nJOIN [Production].[ProductInventory] [pi] ON [p].[ProductID] = [pi].[ProductID]\r\nWHERE [p].[SellEndDate] IS NOT NULL\r\nAND [pi].[Quantity]\u00a0 < 1000\r\nOPTION (QUERYTRACEON 9130);\r\nGO\r\n<\/pre>\n
We see the FILTER operators in the plan:<\/p>\n
\"Query<\/a>
Query plan with FILTER operator<\/figcaption><\/figure>\n
And in the XML we see 9130 for the compilation as a session trace flag, but it doesn’t show up in the execution flags:<\/p>\n
\"Trace<\/a>
Trace flags (with hint) from qeury plan XML<\/figcaption><\/figure>\n
Now, in the aforementioned examples, I\u2019ve run the query in Management Studio and captured the actual plan.\u00a0 Do I get all the same information from the plan cache?\u00a0 To check, I\u2019ll clear the plan cache, re-run the most query, and then interrogate the plan cache to see what I get:<\/p>\n
\r\nDBCC FREEPROCCACHE; \/* not for production use! *\/\r\nGO\r\n\r\nSELECT [p].[ProductID],[p].[StandardCost], [p].[ListPrice], [pi].[Quantity], [p].[Name]\r\nFROM [Production].[Product] [p]\r\nJOIN [Production].[ProductInventory] [pi] ON [p].[ProductID] = [pi].[ProductID]\r\nWHERE [p].[SellEndDate] IS NOT NULL\r\nAND [pi].[Quantity]\u00a0 < 1000\r\nOPTION (QUERYTRACEON 9130);\r\nGO\r\n\r\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nGO\r\n\r\nSELECT [s].[text], [qs].[last_execution_time], [qp].[query_plan]\r\nFROM [sys].[dm_exec_query_stats] [qs]\r\nCROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) qp\r\nCROSS APPLY [sys].[dm_exec_sql_text]([qs].[plan_handle]) [s]\r\nWHERE [s].[text]\r\nLIKE '%SellEndDate%';\r\nGO\r\n\r\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;\r\nGO\r\n<\/pre>\n
When I open the query plan from the output and look at the XML, I see compilation information, but no runtime info:<\/p>\n
\"Trace<\/a>
Trace flag information from query plan XML retrieved from cache<\/figcaption><\/figure>\n
Presumably, if I run the query again, without clearing the plan cache, and taking off the 9130 trace flag, I would get a new entry:<\/p>\n
\r\nSELECT [p].[ProductID],[p].[StandardCost], [p].[ListPrice], [pi].[Quantity], [p].[Name]\r\nFROM [Production].[Product] [p]\r\nJOIN [Production].[ProductInventory] [pi] ON [p].[ProductID] = [pi].[ProductID]\r\nWHERE [p].[SellEndDate] IS NOT NULL\r\nAND [pi].[Quantity]\u00a0 < 1000;\r\nGO\r\n\r\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nGO\r\n\r\nSELECT [s].[text], [qs].[last_execution_time], [qp].[query_plan]\r\nFROM [sys].[dm_exec_query_stats] [qs]\r\nCROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) qp\r\nCROSS APPLY [sys].[dm_exec_sql_text]([qs].[plan_handle]) [s]\r\nWHERE [s].[text]\r\nLIKE '%SellEndDate%';\r\nGO\r\n\r\nSET TRANSACTION ISOLATION LEVEL READ COMMITTED;\r\nGO\r\n<\/pre>\n
\"Query<\/a>
Query information in the plan cache<\/figcaption><\/figure>\n
True.\u00a0 When I check the plan for the second entry, with the later execution time, in the XML I find:<\/p>\n
\"Trace<\/a>
Trace flags from query plan XML (no hint)<\/figcaption><\/figure>\n
Ok, so I can find what trace flags are enabled when a query is compiled, and if anyone is using QUERYTRACEON to enable trace flags, I can see that here as well.\u00a0 (If you\u2019re wondering, yes, I could see also see that from querying the plan cache because it\u2019s in the text of the query.)\u00a0 On a bigger scale, I can mine the plan cache for this information.\u00a0 HUGE thanks to Jonathan for saving me from a losing battle with XQuery:<\/p>\n
\r\nSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;\r\nGO\r\n\r\nWITH XMLNAMESPACES\r\n(DEFAULT 'http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan')\r\nSELECT\r\nSTUFF((SELECT ', ' +\u00a0 tf.value('(.\/@Value)[1]', 'varchar(10)')\r\nFROM stmt.nodes('.\/QueryPlan\/TraceFlags[@IsCompileTime="1"]\/TraceFlag[@Scope="Global"]') AS t(tf)\r\nFOR XML PATH('')\r\n), 1, 2, '') AS GlobalTraceFlags,\r\nSTUFF((SELECT ', ' +\u00a0 tf.value('(.\/@Value)[1]', 'varchar(10)')\r\nFROM stmt.nodes('.\/QueryPlan\/TraceFlags[@IsCompileTime="1"]\/TraceFlag[@Scope="Session"]') AS t(tf)\r\nFOR XML PATH('')\r\n), 1, 2, '') AS SessionTraceFlags,\r\nstmt.query('.') as stmt_node,\r\ncp.usecounts,\r\nqp.query_plan,\r\ncp.plan_handle,\r\nqp.objectid,\r\ncp.objtype,\r\ncp.cacheobjtype,\r\ncp.size_in_bytes\r\nFROM sys.dm_exec_cached_plans AS cp\r\nCROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp\r\nCROSS APPLY query_plan.nodes('\/ShowPlanXML\/BatchSequence\/Batch\/Statements\/StmtSimple') AS batch(stmt)\r\nWHERE stmt.exist('.\/QueryPlan\/TraceFlags[@IsCompileTime="1"]') = 1\r\nOPTION(RECOMPILE, MAXDOP 1);\r\n<\/pre>\n
\"Output<\/a>
Output from query that checks the plan cache<\/figcaption><\/figure>\n
Awesome…Jon’s code breaks out global and session trace flags from the query plan, which makes it easy to find any session level flags in use.\u00a0 Note that this query will return the entire plan cache, so I recommend including additional predicates in the WHERE clause (e.g. where the session flag is NOT NULL).<\/p>\n
*Why we recommend 3226 and 1118:<\/p>\n
Fed up with BACKUP success messages bloating your error logs?<\/a><\/p>\n
Misconceptions around TF 1118<\/a><\/p>\n
*Note that 4199 is no longer needed in SQL Server 2016 if you\u2019re using database compatibility mode 130, see SQL Server query optimizer hotfix trace flag 4199 servicing model.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
I was perusing the release notes for SQL Server 2014 SP2 and found this gem: Information about enabled trace flags is added to the showplan XML in SQL Server 2014 SP2 Ohhhhhh, very cool.\u00a0 This is great information for troubleshooting! I fired up my 2014 VM and applied SP2, then verified the trace flags I […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"yoast_head":"\nTrace Flag Information in Query Plans - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Query plans in SQL Server 2014 SP2 include a new attribute, TraceFlags, which lists trace flags enabled when a query is compiled and executed.\" \/>\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\/trace-flag-information-in-query-plans\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Trace Flag Information in Query Plans - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Query plans in SQL Server 2014 SP2 include a new attribute, TraceFlags, which lists trace flags enabled when a query is compiled and executed.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2016-11-09T14:00:04+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:19:33+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/11\/trace-flags-enabled.jpg\" \/>\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=\"6 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\/trace-flag-information-in-query-plans\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/\",\"name\":\"Trace Flag Information in Query Plans - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2016-11-09T14:00:04+00:00\",\"dateModified\":\"2017-04-13T16:19:33+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Query plans in SQL Server 2014 SP2 include a new attribute, TraceFlags, which lists trace flags enabled when a query is compiled and executed.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Trace Flag Information in Query Plans\"}]},{\"@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":"Trace Flag Information in Query Plans - Erin Stellato","description":"Query plans in SQL Server 2014 SP2 include a new attribute, TraceFlags, which lists trace flags enabled when a query is compiled and executed.","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\/trace-flag-information-in-query-plans\/","og_locale":"en_US","og_type":"article","og_title":"Trace Flag Information in Query Plans - Erin Stellato","og_description":"Query plans in SQL Server 2014 SP2 include a new attribute, TraceFlags, which lists trace flags enabled when a query is compiled and executed.","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/","og_site_name":"Erin Stellato","article_published_time":"2016-11-09T14:00:04+00:00","article_modified_time":"2017-04-13T16:19:33+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2016\/11\/trace-flags-enabled.jpg"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/","name":"Trace Flag Information in Query Plans - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2016-11-09T14:00:04+00:00","dateModified":"2017-04-13T16:19:33+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Query plans in SQL Server 2014 SP2 include a new attribute, TraceFlags, which lists trace flags enabled when a query is compiled and executed.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/trace-flag-information-in-query-plans\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Trace Flag Information in Query Plans"}]},{"@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\/781"}],"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=781"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/781\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=781"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=781"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=781"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}