Hi Friends,

This is my 48th blog on SQL Server Trace Flag 9292 in the series of One Trace Flag a Day. You can go to all blogs under that series by clicking here.

We all knew that statistics plays a very important role inside SQL Server. We also know that statistics are used by query optimizer to generate the execution plan for query. So the question is, Can we check which statistic object is useful for the query execution? Yes, by using SQL Server trace flag 9292. This is one of the trace flag which can be used during troubleshooting. This trace flag is used to get the report about statistics objects considered as interesting by query optimizer during compilation or recompilation of query. Keep in mind that only header is loaded for these interesting statistics. Let me show you this practically.

Run the below TSQL to create database, tables and procedure. Here inside procedure we have specified recompile option to prevent from parameter sniffing issue.

Now, we shall run the below TSQL code with actual execution plan to see the impact of trace flag 9292. Check the output under Messages tab.

traceflag_9292

From the above output you can see that statistics header of statistics created due to non clustered index has been loaded by optimizer.

Finally do not forget to turn off the trace flag.

PS: Do not use trace flags in production environment without testing it on non production environments and without consulting because everything comes at a cost.

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