SQL Server Trace Flag 9292

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.

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

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.

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.

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

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.