sql server trace flag 2388

Hi Friends,

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

This trace flag provide us lots of information. Let me explain one by one.

First: In SQL Server, if you want to see the information of last four statistics update on a statistics object then you can use trace flag 2388. In simple words, we can say that this trace flag provide us the historical information about statistics update. There are some another interesting use of this trace flag, I’ll show you later. First let me show you the historical information of updates using trace flag 2388.

use master
go
--Enable the trace flag
DBCC TRACEON(2388,-1)
go
--Drop this database if alreay exist
if DB_ID('StatsDemo2014')>0
begin  
Alter database StatsDemo2014 set single_user 
Drop database StatsDemo2014
end
go
--Create a database for Demo at default files location
Create database StatsDemo2014
go
Use StatsDemo2014
go
--Create table
create table xtstatsdemo
(
id int not null,
balance int not null,
name varchar(25)
)
go
--create non clustered index on id column
--this will generate statistics on this index with the same name as of index
create nonclustered index IX_xtstatsdemo_id on xtstatsdemo(id)
go 
------ First Block to insert data and update the stats --------
insert into xtstatsdemo values(1,1,'data1');
update statistics dbo.xtstatsdemo with fullscan;
go
------ Second Block to insert data and update the stats --------
insert into xtstatsdemo values(2,2,'data2');
update statistics dbo.xtstatsdemo with fullscan;
go
------ Third Block to insert data and update the stats --------
insert into xtstatsdemo values(3,3,'data3');
update statistics dbo.xtstatsdemo with fullscan;
go
------ Fourth Block to insert data and update the stats --------
insert into xtstatsdemo values(4,4,'data4');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

From above output it should be clear that it shows the information for last four updates only. Here I just hide some columns to show only required columns. Oh! You are still in doubt because I have updated the stats only for four times. Ok, let me do one more row insert and one more update stats to clear the doubt.

Use StatsDemo2014
go
------ Fifth Block to insert data and update the stats --------
insert into xtstatsdemo values(5,5,'data5');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

I have tested this on SQL Server 2012 and SQL Server 2014. From above output it is clear that this trace flag can provide us the statistics historical information of statistics objects only for last four updates.

Second: Output also shows the information of insert, update and delete on leading stats column which is ‘id’ in our case. Let me show you this behavior:

Use StatsDemo2014
go
------ First Block to insert data and update the stats --------
insert into xtstatsdemo values(6,6,'data6');
update statistics dbo.xtstatsdemo with fullscan;
go
------ Second Block to insert data and update the stats --------
insert into xtstatsdemo values(7,7,'data7');
update statistics dbo.xtstatsdemo with fullscan;
go
------ Third Block to delete data and update the stats --------
delete from xtstatsdemo where id=7;
update statistics dbo.xtstatsdemo with fullscan;
go
------ Fourth Block to update id and update the stats --------
update xtstatsdemo set id=7 where name='data6' 
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

In above output from below to above:
1st Row-Block: Inserted one row, Showing NULL for both insert and delete
2nd Row-Block: Inserted one row, Showing 1 under Inserts.
3rd Row-Block: Deleted one row, Showing 1 under Deletes.
4th Row-Block: Update one row, interesting 0.5 under both inserts and Deletes.

Third: Most interesting and useful output shown in ‘Leading Column Type’ column. The value for most updated row (first row) can be Ascending, Stationary or Unknown.

It will be ascending, if all inserted or updated value for leading column (id) will be in ascending order during last three stat updates.

Use StatsDemo2014
go
------ First Block to delete data and update the stats --------
delete from xtstatsdemo where id=7
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go
------ Second Block to insert data and update the stats --------
insert into xtstatsdemo values(11,11,'data11');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go
------ Third Block to insert data and update the stats --------
insert into xtstatsdemo values(12,12,'data12');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go
------ Fourth Block to insert id and update the stats --------
insert into xtstatsdemo values(13,13,'data13');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

It will be Stationary, if any inserted or updated value for leading column (id) will not be in ascending order during last stat updates.

Use StatsDemo2014
go
------ First Block to insert data and update the stats --------
insert into xtstatsdemo values(21,21,'data21');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go
------ Second Block to insert data and update the stats --------
insert into xtstatsdemo values(15,15,'data15');
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go
------ Third Block to update data and update the stats --------
update xtstatsdemo set id=16 where name='data13'
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

It will be Unknown, for first time stats creation along with first two stat updates or if you will delete any value from that leading column ‘id’

Use StatsDemo2014
go
------ First Block to insert data and update the stats --------
delete from xtstatsdemo where id=12;
update statistics dbo.xtstatsdemo with fullscan;
go
DBCC SHOW_STATISTICS('xtstatsdemo','IX_xtstatsdemo_id')
Go

In last please do not forget to turn off this trace flag.

Use Master
go
DBCC TRACEOFF(2388,-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