SQL Server statistics – traceflags 2389 & 2390

A common problem with statistics is when it’s based on an index with an incrementing column, like an IDENTITY column or a DATETIME column that is behaving like a time stamp. The problem occurs when you have a large table and you add more records (less than 20% of the number of existing records) and then tries to select those newly added records. As the auto update statistics is only triggered when 20% of the records are updated, you’ll have statistics that are “out of date” and doesn’t know anything about the new records.

This lack of statistics will cause the SQL optimizer to choose a plan that is based on the assumption that there are only one records for the incrementing column, and that’s usually a bad idea from a performance perspective.

 When you select records, you’ll see differences between the estimated and actual number of records.

Since SQL server 2005 SP1, there are two undocumented (and unsupported) trace flags that can be used to solve this issue and they also applies to SQL2008, SQL2008R2 and SQL2012. Traceflag 2389 will  enable auto-quick-statistics update for known ascending keys and 2390 will enable auto-quick-statistics update for all columns, known ascending or unknown. 

What does that mean? Well, when the statistics are seen to increase three times the column is branded ascending.  If trace flag 2389 is set, and a column is branded ascending, and an index exists with that ascending column as the leading key, then the statistics will be updated automatically at query compile time.  SQL Server finds the highest value and adds a new step at the end of the existing histogram.   

Lets look at a sample

-- create a table with values from 1 to 200 000
SELECT * 
INTO dbo.IncreasingColumn
FROM dbo.[fnNumsTable](1,200000,1) AS fnt; -- this function generates records from x to Y

-- create a clustered index
CREATE CLUSTERED INDEX ix ON dbo.IncreasingColumn (n);

-- the statistics is up to date
-- take a look at the execution plan
-- as you can see, the estimated and the actual number of records match
SELECT n FROM dbo.increasingColumn WHERE n > 200000

-- lets add 100 records
-- not enough to trigger the auto update stats 
INSERT INTO dbo.IncreasingColumn
SELECT n
FROM [SQLServiceTools].dbo.[fnNumsTable](200001,200100,1) AS fnt;

--if you compare the estimated and actual number of records,
--you'll notice that estimated number of records is only 1
-- this is beacause the value I am lookin for is not within range in
-- the statistics 
SELECT n FROM dbo.increasingColumn WHERE n > 200000

-- let's activate the trace flags
DBCC TRACEON(2389,2390);

-- and compare the actual and estimated again.
-- now they match each other, exactly what we want
SELECT n FROM dbo.increasingColumn WHERE n > 200000 OPTION(RECOMPILE)

As you can see in the actual execution plan, with the trace flags active you’ll get more accurate statistics and that’s a good thing for your performance. When you have accurate statistics it’s more likely that SQL server optimizer will choose a good plan and the right indexes depending on the number of records you’ll trying to get.

You can activate the trace flags on Global, Session or Statement level ( by using OPTION(QUERYTRACEON 2390,QUERYTRACEON 2389, RECOMPILE) ), but you have to be a member of the sysadmin role.

If you experience performancec issues, call us and our SQL server consultants will help you.

/Håkan Winther
Twitter: @h_winther