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.
Prince Kumar Rastogi