Ring oss på +46 8 409 567 00 Kontakt

Trace Flags usage is now recorded in sqlplan execution plan xml

New in SQL Server 2016 SP1 / SQL Server 2014 SP2 the xml version of the actual execution plan now shows which trace flags were in force when the query was run.

Specifically the QueryPlan xml element now contains, if any trace flag is in force, one or two TraceFlags elements as follows.

The first TraceFlags element is for compile time and has the attribute IsCompileTime=”true” the second TraceFlags element is for execution time and has the attribute IsCompileTime=”false”.

Any session level traceflags set using the OPTION (QUERYTRACEON NNNN) query hint will appear in the compile time section. Global level hints appear typically in both sections.

 

Testing

Apply service pack 1 if you haven’t already, and test the SQL Server version by running select @@version.

 

select @@version

 

-- returns

Microsoft SQL Server 2016 (RTM-GDR) (KB3194716) - 13.0.1722.0 (X64)   Sep 26 2016 13:17:23   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Home 6.3 <X64> (Build 14393: )

 

Turn on the capture of the actual execution plan (Query > Include Actual Execution Plan)

Now run a query with a trace flag hint

 

SELECT TOP 1000 [ReportYear]

      ,[FirstNameId]

      ,[Gender]

      ,[NameCount]

  FROM [SQLIndexWorkbook].[agg].[FirstNameByYear] OPTION (QUERYTRACEON 4135)

 

Navigate to the Execution plan tab of the results window and right click > Show Execution Plan XML

 

<?xml version=”1.0″ encoding=”utf-16″?>

<ShowPlanXML

<QueryPlan DegreeOfParallelism=”1″ CachedPlanSize=”16″ CompileTime=”0″ CompileCPU=”0″>

<MemoryGrantInfo SerialRequiredMemory=”0″ SerialDesiredMemory=”0″ />

<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant=”207215″ …/>

<TraceFlags IsCompileTime=”true”>

              <TraceFlag Value=”834″ Scope=”Global” />

              <TraceFlag Value=”4135″ Scope=”Session” />

</TraceFlags>

<TraceFlags IsCompileTime=”false”>

              <TraceFlag Value=”834″ Scope=”Global” />

</TraceFlags>

 

The great thing about this is that the sqlplan file now includes and encapsulates the traceflag information reducing the need to gather further environmental details.

As described in the following KB article

https://support.microsoft.com/en-us/kb/3170115?sd=rss&spid=18457