Finding what SQL Server Trace Flags were enabled for a query


By:   |   Updated: 2017-01-11   |   Comments   |   Related: More > Performance Tuning

Itís the Database. Or Is It? Put an End to Finger Pointing!

Free MSSQLTips Webinar: Itís the Database. Or Is It? Put an End to Finger Pointing!

During this webinar, we'll show how SolarWinds Server & Application Monitor (SAM) can enable your IT team to monitor the health and availability of your Microsoft SQL Servers. SAM in the hands of your IT teams provides broad visibility across the stack to easily identify the underlying cause for database availability and performance problems.


Problem

SQL Server Trace Flags are used mainly to switch on or off a specific set of behaviors globally at the instance level or locally at the session level. Trace flags can be used to diagnose performance issues or change the behavior of queries. The DBCC TRACESTATUS command can be used to list all trace flags that are enabled at all levels. Is there a way to get trace flag information from the query plan?

Solution

Starting with SQL Server 2014 SP2 and including SQL Server 2016 SP1, trace flag information can be reviewed in the TraceFlags section of the query XML execution plan, where all trace flags that are enabled at the time the query is compiled and executed at the instance and session levels will be listed.

Running the DBCC TRACESTATUS command will show us all enabled trace flags both globally at the instance level and locally at the session level, with the trace flag status:

DBCC TRACESTATUS;
GO

The result in our case shows us that the only enabled trace flag globally at the instance level is trace flag 4199:

SQL Server Trace Flag status

If we execute the below SELECT statement:

USE MSSQLTipsDemo 
GO
SELECT TOP 1000 [CountyCode],[RowVersion]
FROM [MSSQLTipsDemo].[dbo].[CountryInfoNew]

Then from the execution plan for that query, right-click on the SELECT operator and choose Properties:

SQL Server Execution Plan

The SELECT operatorís properties window contains a separate node for the TraceFlags that show us all trace flags that are enabled with the scope (globally or locally) at both compile time with the IsCompileTime value equal to true and at the run time with the IsCompileTime value equal to false:

SQL Server Exection Plan Trace Flag Properties

The same information can be found in the queries in the XML execution plan. Right-click on the graphical execution plan and choose Show Execution Plan XML:

SQL Server Execution Plan Show Execution Plan XML...

There is now a section in the XML execution plan, TraceFlags, in which we can see all enabled trace flags and the scope for the trace flags, globally at the instance level or locally at the session level both at compile time with IsCompileTime value equal to true and at run time with IsCompileTime value equal to false.

The below result shows us that only trace flag 4199 was enabled globally at the instance level both at compilation and runtime:

XML Execution Plan Trace Flags with Global Scope

If we modify the previous query and enable trace flag 1118 at the session level using the QUERYTRACEON option:

USE MSSQLTipsDemo 
GO
SELECT TOP 1000 [CountyCode],[RowVersion]
FROM [MSSQLTipsDemo].[dbo].[CountryInfoNew]
OPTION (QUERYTRACEON 1118);

The generated XML execution plan will show us trace flag 1118 is enabled at compilation time at the session scope as shown below:

XML Execution Plan Trace Flags with Global and Session Scope

Take into consideration that if there is no trace flag enabled at the instance or session levels at the compilation or execution time, there will not be a section for the TraceFlags in the queries XML execution plan.

Next Steps


Last Updated: 2017-01-11


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Comments For This Article





download


Recommended Reading

Different Ways to Flush or Clear SQL Server Cache

Fastest way to Delete Large Number of Records in SQL Server

UPDATE Statement Performance in SQL Server

How to find out how much CPU a SQL Server process is really using

SQL Server stored procedure runs fast in SSMS and slow in application





get free sql tips
agree to terms


Learn more about SQL Server tools