Hi Friends,

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

In my previous blog, we have seen the use of Trace Flag 9292. This loads only the header for statistics which are considered as interesting or useful by optimizer. But how can we know that which statistics actually used by optimizer to calculate or estimate the cardinality? We can identify this by using SQL Server trace flag 9204. 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 which are fully loaded and used by the optimizer for cardinality estimation. Let me show you this practically. I am using SQL Server 2012 for this test.

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

Now I’ll run the below TSQL code with actual execution plan to see the impact of trace flag 9204. Check the output under Messages tab.


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

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.


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