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.
use master go if DB_ID('Trace9292') IS NOT NULL Drop database Trace9292 go Create database Trace9292 go Use Trace9292 go create table xttrace9292 ( id int identity(1,1) Primary Key, bal int, name varchar(100) ) go create NonClustered Index IX_xttrace9292_bal on xttrace9292(bal) go insert into xttrace9292 values(1000,'SQLServerGeeks.com') go insert into xttrace9292 values(5000,'SQLServerGeeks.com') go 1000 Create proc spFirst(@balance int) as Select name from xttrace9292 TF where bal=@balance OPTION (RECOMPILE) go
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.
Use Trace9292 go DBCC TRACEON(9292,3604,-1) go exec spFirst 5000 go
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.
use master go DBCC TRACEOff(9292,3604,-1) go
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