Hello Friends,

SQL Server trace flags are important consideration for a query plan compilation and execution. Trace flag can impact the execution of a query and might hit the performance badly if not used appropriately. Sometimes during performance troubleshooting by using execution plan is not easier because we don’t have the information about the trace flags which are enabled on the server. SQL Server 2014 SP2 and SQL Server 2016 onward, this information has been added to the XML plan. That trace flag information will be taken in consideration while doing performance troubleshooting. In this blog post we will take a look about SQL Server Trace Flags Information in Execution Plans. Let me show you the same practically:

In above query, you can see that trace flag 4199 enabled at global level, Actual execution plan enabled using STATISTICS XML ON and trace flag 9471 enabled at query level using QueryTraceON.

SQL Server Trace Flags Information in Execution Plans

Click on the link to open up the XML plan and search the trace flag section, you will see the enabled trace flag level information:

SQL Server Trace Flag 2

We can easily see that which trace flag is impacting the compilation as well. You will see the same information if you are using the graphical execution plan. Click on the select section of the graphical execution plan and right click on properties:

SQL Server Trace Flag 3

This will open up the properties window in Right hand side and bottom of that windows there will be the trace flag section as mention below:

SQL Server Trace Flag 4

Reference: Click Here.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook