Trace flags are often-hidden configuration switches within SQL Server that can be used to change advanced configuration options, enable or disable fixes, or provide additional diagnostic information. Microsoft recently (on the time scale of the product) published a list of supported trace flags. The community has found other, undocumented trace flags through various means. The best repository that I’m aware of is maintained by Konstantin Ktaranov here and published here. This blog post contains a stored procedure that can find trace flags. The code contained in this blog post is extremely dangerous and should never be run in production. Be prepared to say goodbye to any server that you run it on.
Hunting Technique
Microsoft gives us a few clues about the range of valid trace flags. All of the documented trace flags (other than the one to make trace flags globally scoped, -1) are positive integers less than 11025. Looking through the list, we can see that similar trace flags are often grouped together. However, the most important hint is that DBCC TRACEON and DBCC TRACEOFF threw an error for any trace flag that’s above some hardcoded maximum. In SQL Server 2017 CU2 the largest allowed value is 11498. What if we had a stored procedure which could take a query to run, turn on one trace flag at a time from 1 to 11498, save off the XML from the estimated plan, turn off the trace flag, go to the next one, and compare all of the generated XML? That would give us a way to check for changes to the XML for everything in the known range of possible trace flags.The technique described above will not work for trace flags that can only be activated at server startup. You’ll need something like Brent’s method here for that. It also won’t work well for trace flags which need other trace flags to be enabled at the same time. Some trace flags have an effect which cannot be observed through an estimated plan, such as some server settings and aggregate pushdown for CCIs. Still, with the right test query and a little patience you can get some interesting results.
Prepare for the Hunt
The stored procedure requires a few tables to exist and be populated. There is a set of three tables that exclude trace flags in different ways:
CREATE TABLE dbo.STACK_DUMP_TFS (TF INT, PRIMARY KEY (TF));CREATE TABLE dbo.TFS_TO_EXCLUDE (TF INT, PRIMARY KEY (TF));CREATE TABLE dbo.KNOWN_TFS (TF INT, PRIMARY KEY (TF));
The first table, STACK_DUMP_TFS, contains trace flags that cause stack dumps which you never want to enable. I found two such trace flags during my testing, but there could be others depending on server configuration and the query that you’re testing. The TFS_TO_EXCLUDE table contains trace flags that you don’t want to enable for one reason or another. Maybe you want to enable a trace flag throughout the lifetime of a test or you’ve found a previously unknown trace flag and you don’t want to continue to show up your test results. The KNOWN_TFS table contains all trace flags found in Konstantin’s trace flag guide. Of course, you can populate the tables with whatever you want, but I put code to populate the tables with the dataset that I use on pastebin.Finally, the stored procedure logs what it finds to the following table:
CREATE TABLE dbo.LOG_TF_XML_COMPARE (TEST_NAME VARCHAR(100) NOT NULL,TF_SCOPE VARCHAR(10) NOT NULL,TF_NUMBER INTEGER NOT NULL,TF_IS_KNOWN INT,LOG_TIME DATETIME,QUERY_PLAN_XML XML,QUERY_ERROR_TEXT NVARCHAR(4000),PRIMARY KEY (TEST_NAME, TF_SCOPE, TF_NUMBER));
The structure of the table will make more sense after I go through an example later on in this post.
Set Your Bait
The stored procedure has a few parameters to define the search:
@test_name VARCHAR(100),@query_text NVARCHAR(3900),@tf_scope VARCHAR(10) = 'GLOBAL',@first_TF_to_search INT = 1,@last_TF_to_search INT = 11498,@skip_known_TFs INT = 1
The @test_name parameter controls what is logged to the TEST_NAME column of LOG_TF_XML_COMPARE. The stored procedure deletes all existing data from the table with a matching @test_name and @tf_scope.The @query_text parameter contains the query for which an estimated plan to be generated. Currently, the procedure only supports single statement queries, so you can’t define a variable or create a table in the query text and do something else after.The @tf_scope parameter controls if the trace flags are enabled at the session, global, or query level with QUERYTRACEON. Allowed inputs are 'GLOBAL', 'SESSION', and 'QUERY'. The query level can only be used if @query_text contains a '{{QUERYTRACEON}}' or '{{QUERYHINT}}' placeholder. '{{QUERYTRACEON}}' should be used as a substitute for QUERYTRACEON in the query and '{{QUERYHINT}}' should be used to create the OPTION part of a query, so you’d only use '{{QUERYTRACEON}}' if you need to specify other hints at the query level.@first_TF_to_search is the first trace flag to search with a default value of 1. Trace flags are always searched in ascending order.@last_TF_to_search is the last trace flag to search with a default value of 11498. I haven’t done any testing on lower versions of SQL Server, so it’s possible that you’ll see errors when trying trace flags with a higher value on some product versions.If @skip_known_TFs is set to 0 then trace flags in the KNOWN_TFS table will be skipped during the stored procedure run. Trace flags in the STACK_DUMP_TFS and TFS_TO_EXCLUDE tables are always skipped.
Begin the Hunt
The procedure enables a trace flag, generates a cached plan, does some cleanup on the plan, and saves it into the logging table if it’s different from the plan without any trace flags. The full code of the procedure is below:
-- THIS CODE DOES BAD THINGS!!!!CREATE OR ALTER PROCEDURE [dbo].[FIND_TRACE_FLAGS] (@test_name VARCHAR(100),@query_text NVARCHAR(3900),@tf_scope VARCHAR(10) = 'GLOBAL',@first_TF_to_search INT = 1,@last_TF_to_search INT = 11498,@skip_known_TFs INT = 1)ASBEGINDECLARE@plan_handle VARBINARY(64),@plan_xml XML,@query_error nvarchar(4000),@TF INT,@trace_sql VARCHAR(1000),@standard_plan_xml_as_string NVARCHAR(MAX),@TF_is_known INT,@query_text_to_run_w_placeholders NVARCHAR(4000),@query_text_to_run NVARCHAR(4000);SET NOCOUNT ON;IF @tf_scope NOT IN ('GLOBAL', 'SESSION', 'QUERY')BEGINTHROW 50001, 'Fix @tf_scope', 1;RETURN;END;IF @tf_scope = 'QUERY' AND @query_text NOT LIKE '%{{QUERYTRACEON}}%' AND @query_text NOT LIKE '%{{QUERYHINT}}%'BEGINTHROW 50001, '@query_text needs {{QUERYTRACEON}} or {{QUERYHINT}} placeholder', 1;RETURN;END;IF @first_TF_to_search NOT BETWEEN 1 AND 11498 -- max that doesn't error out in DBCC traceon is 11498BEGINTHROW 50001, 'Fix @first_TF_to_search', 1;RETURN;END;IF @last_TF_to_search NOT BETWEEN 1 AND 11498 -- max that doesn't error out in DBCC traceon is 11498BEGINTHROW 50001, 'Fix @@last_TF_to_search', 1;RETURN;END;IF @tf_scope = 'QUERY' AND @query_text NOT LIKE '%{{QUERYTRACEON}}%' AND @query_text NOT LIKE '%{{QUERYHINT}}%'BEGINTHROW 50001, '@query_text needs {{QUERYTRACEON}} or {{QUERYHINT}} placeholder', 1;END;DBCC TRACEON(8757) with NO_INFOMSGS; -- disable trivial plansDELETE FROM dbo.LOG_TF_XML_COMPARE WITH (TABLOCK)WHERE TEST_NAME = @test_name AND TF_SCOPE = @tf_scopeOPTION (RECOMPILE);DBCC FREEPROCCACHE with NO_INFOMSGS;SET @query_text_to_run_w_placeholders = N'SET NOEXEC ON; /* FIND_ME */' + @query_text;IF @tf_scope <> 'QUERY'BEGINSET @query_text_to_run = @query_text_to_run_w_placeholdersENDELSEBEGINSET @query_text_to_run = REPLACE(REPLACE(@query_text_to_run_w_placeholders, N'{{QUERYTRACEON}}', N''), N'{{QUERYHINT}}', N'');END;BEGIN TRYEXEC (@query_text_to_run);END TRYBEGIN CATCHSET @query_error = ERROR_MESSAGE();END CATCH;IF @query_error IS NOT NULLBEGINTHROW 50001, @query_error, 1;RETURN;END;SELECT /* HIDE_ME */ @plan_handle = ecp.plan_handle, @plan_xml = eqp.query_planFROM sys.dm_exec_cached_plans ecpCROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) estCROSS APPLY sys.dm_exec_query_plan (ecp.plan_handle) eqpWHERE est.text LIKE '%/* FIND_ME */%'AND est.text NOT LIKE '%/* HIDE_ME */%';DBCC FREEPROCCACHE (@plan_handle) WITH NO_INFOMSGS;SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[1]');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementCompId');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileTime');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileCPU');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileMemory');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/TraceFlags');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/OptimizerHardwareDependentProperties/@MaxCompileMemory');IF @tf_scope = 'QUERY' -- only wipe out text and other stuff when QUERYTRACEON is usedBEGINSET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementText');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@QueryHash');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@QueryPlanHash');END;INSERT INTO dbo.LOG_TF_XML_COMPARE VALUES (@test_name, @tf_scope, 0, 0, GETDATE(), @plan_xml, @query_error);SET @standard_plan_xml_as_string = CAST(@plan_xml AS NVARCHAR(MAX));SET @TF = @first_TF_to_search;WHILE @TF <= @last_TF_to_searchBEGINSET @query_error = NULL;SET @plan_handle = NULL;SET @plan_xml = NULL;SET @TF_is_known = 0;IF @TF = 8757 OR EXISTS (SELECT 1 FROM dbo.STACK_DUMP_TFS WHERE TF = @TF) OR EXISTS (SELECT 1 FROM dbo.TFS_TO_EXCLUDE WHERE TF = @TF)BEGINSET @TF = @TF + 1;CONTINUE;END;IF EXISTS (SELECT 1 FROM dbo.KNOWN_TFS WHERE TF = @TF)BEGINSET @TF_is_known = 1;END;IF @TF_is_known = 1 AND @skip_known_TFs = 1BEGINSET @TF = @TF + 1;CONTINUE;END;-- set trace flag at right levelIF @tf_scope = 'GLOBAL'BEGINSET @trace_sql = 'DBCC TRACEON(' + CAST(@TF AS VARCHAR(5)) + ', -1) with NO_INFOMSGS';EXEC (@trace_sql);ENDELSE IF @tf_scope = 'SESSION'BEGINSET @trace_sql = 'DBCC TRACEON(' + CAST(@TF AS VARCHAR(5)) + ') with NO_INFOMSGS';EXEC (@trace_sql);ENDELSEBEGINSET @query_text_to_run = REPLACE(REPLACE(@query_text_to_run_w_placeholders, N'{{QUERYTRACEON}}', N', QUERYTRACEON ' + CAST(@TF AS NVARCHAR(5))), N'{{QUERYHINT}}', N'OPTION(QUERYTRACEON ' + CAST(@TF AS NVARCHAR(5)) + N')' );END;BEGIN TRYEXEC (@query_text_to_run);END TRYBEGIN CATCHSET @query_error = ERROR_MESSAGE();END CATCH;IF @query_error IS NULLBEGINSELECT /* HIDE_ME */ @plan_handle = ecp.plan_handle, @plan_xml = eqp.query_planFROM sys.dm_exec_cached_plans ecpCROSS APPLY sys.dm_exec_sql_text(ecp.plan_handle) estCROSS APPLY sys.dm_exec_query_plan (ecp.plan_handle) eqpWHERE est.text LIKE '%/* FIND_ME */%'AND est.text NOT LIKE '%/* HIDE_ME */%';IF @plan_handle IS NOT NULLBEGINDBCC FREEPROCCACHE (@plan_handle) WITH NO_INFOMSGS;SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple[1]');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementCompId');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileTime');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileCPU');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/@CompileMemory');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/TraceFlags');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/OptimizerHardwareDependentProperties/@MaxCompileMemory');IF @tf_scope = 'QUERY' -- only wipe out text and other stuff when QUERYTRACEON is usedBEGINSET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementText');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@QueryHash');SET @plan_xml.modify('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";delete /ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@QueryPlanHash');END;END;END;IF @standard_plan_xml_as_string <> CAST(@plan_xml AS NVARCHAR(MAX))BEGININSERT INTO dbo.LOG_TF_XML_COMPARE VALUES (@test_name, @tf_scope, @TF, @TF_is_known, GETDATE(), @plan_xml, @query_error);END;-- set trace flag at right levelIF @tf_scope = 'GLOBAL'BEGINSET @trace_sql = 'DBCC TRACEOFF(' + CAST(@TF AS VARCHAR(5)) + ', -1) with NO_INFOMSGS';EXEC (@trace_sql);ENDELSE IF @tf_scope = 'SESSION'BEGINSET @trace_sql = 'DBCC TRACEOFF(' + CAST(@TF AS VARCHAR(5)) + ') with NO_INFOMSGS';EXEC (@trace_sql);END;SET @TF = @TF + 1;END;SELECT *FROM LOG_TF_XML_COMPAREWHERE TEST_NAME = @test_name AND TF_SCOPE = @tf_scopeORDER BY TEST_NAME, TF_SCOPE, TF_NUMBER;DBCC TRACEOFF(8757) with NO_INFOMSGS; -- enable trivial plansEND;
There are some limitations. The current version can only handle single statement queries up to 4000 characters. The trace flag to disable trivial plans is automatically set during the run to avoid some trace flags that are missed otherwise. The run time is based on the complexity of the query plan. The XML parsing isn’t very efficient but I can run one test on average every two minutes.Running this procedure will clean the plan cache, clear any trace flags already set, and possibly do other bad things including, but not limited to, causing stack dumps or data integrity issues.
The First Kill
Looking for trace flags related to adaptive joins is a good way to test the procedure. Dima already posted about a few undocumented trace flags here so it should be easy to verify the results. I used the same table that I created as part of this blog post. Here’s the code that I ran for the test:
EXEC [dbo].[FIND_TRACE_FLAGS]'DEMO_FOR_BLOG',N'SELECT *FROM dbo.MY_FIRST_CCI oINNER JOIN dbo.SEEK_ME i ON o.JOIN_ID = i.JOIN_ID';
After 101 seconds here are the results:
I can click on the plans and diff them to get clues as to what the listed trace flags do.
Final Thoughts
Remember, this code is extremely dangerous and should never be run in production or even in an important test environment. Feel free to use it to hunt for trace flags if you wish. My only request is that you share anything that you find with the community in some form.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.