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.

traceflag_2388_1

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.

traceflag_2388_2

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:

traceflag_2388_3

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.

traceflag_2388_4

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

traceflag_2388_5

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’

traceflag_2388_6

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

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