SQL Server Trace Flag 8721

Hi Friends,

This is my 50th blog on SQL Server Trace Flag 8721 and last in the series of One Trace Flag a Day. I hope you enjoyed this series. You can go to all blogs under that series by clicking here.

There are lots of things which can be discussed about statistics due to its importance in SQL Server. In my previous blogs, I have also covered various things on statistics. Today, I’ll show you the use of trace flag 8721. This trace flag is used to dump information into SQL Server Error log when AutoStat has been run. Let me show you this practically. Run the below code to create the setup which is also having comments to make self explanatory.

use master
go
if DB_ID('Trace8721') IS NOT NULL
	Drop database Trace8721
go
Create database Trace8721
go
Use Trace8721
go
create table xttrace8721
(
id int identity(1,1) Primary Key,
bal int,
name varchar(100)
)
go
--This will show you only a single statistics object created due to clustered index 
select * from sys.stats where object_id=OBJECT_ID('xttrace8721')
go
--This will create an another statistics object on bal column due to auto create
--No record will be display here because there is no row in the table as of now
select id,name from xttrace8721 where bal=5000
go
--Here you can see both statistics objects
select * from sys.stats where object_id=OBJECT_ID('xttrace8721')
go
--recycle the error log
exec sp_cycle_errorlog
go
--to turn on AUTOSTATS messages
DBCC TRACEON(8721,-1)
go
--to turn on AUTOSTATS messages logging in error log
DBCC TRACEON(3605,-1)
go

Now we will run the below TSQL code to insert the records and triggered an AutoStats.

Use Trace8721
go
--Inserting records will mark the stats as stale
insert into xttrace8721 values(5000,'SQLServerGeeks.com')
go 1000
--this select statement will triggered the autostats event
select name from xttrace8721 where bal=5000
go

Now you can check the SQL Server Error log.

Use Master
Go
exec xp_readerrorlog
Go

From the above output you can see the messages about AUTOSTATS.

Finally do not forget to turn off the trace flag.

Use master
go
DBCC TRACEOFF(8721,3605,-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