Beyond Statistics Histogram – Part 3 (TF 2389 feat. TF 4139)

In the previous article we’ve shown how can we use TF 2390 to handle queries with literals beyond statistics histogram in SQL Server 2012. We saw that SQL Server creates some statistics on the fly for all columns used in filter expressions. If we would enable trace flag on the instance level that means that whenever SQL Server creates an execution plan, it will touch indexes on all filtered columns in all queries. And not all columns have problems with ascending statistics. It would make more sense to apply these action only on columns branded by SQL Server as “Ascending” columns. This is exactly use case for trace flag 2389. It performs the same action and ends up with the same result as TF 2390, but it is applied to columns that SQL Server knows as ascending columns.

SQL Server marks a column as ascending column in a little bit esoteric way. Statistics object on the column should be updated three times in a row with the option “FULL SCAN” and for each update more than 90% of newly added rows should have value in that column greater than the max value from the actual statistic histogram. If this is true the column is branded as ascending. If it’s not the column is marked as stationary.

To demonstrate this let’s create a table and populate it with 10M rows following the procedure we described above. Click here to download the code. After we have updated statistics three times we can see that SQL Server marked the statistics object on the orderdate column as ascending.

DBCC TRACEON(2388);
DBCC SHOW_STATISTICS ('OrdersAsc','ix_orderdate');
DBCC TRACEOFF(2388);

Here is the output that confirms that the leading column Type is Ascending.

1201

Now we add additional 1M rows into the table. Again, this is not enough for automatically statistics update and statistic object remains unchanged.

Let’s recall our query from the previous post, to get all orders for customer with ID 160 in 2014. We will create two queries: one that uses TF 2390 and the other with TF 2389 and an Extended Events session to check which calculations on the fly are performed during the query execution in both cases. Here is the code for the trace session:

CREATE EVENT SESSION TF2389 ON SERVER
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
WHERE (sqlserver.database_id=10))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,MAX_DISPATCH_LATENCY=1 SECONDS)
GO
ALTER EVENT SESSION TF2389 ON SERVER STATE = START;
GO

Let’s execute both queries:

SELECT * FROM dbo.OrdersAsc
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2390, RECOMPILE);
SELECT * FROM dbo.OrdersAsc
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2389, RECOMPILE);

As mentioned above we got exactly the same execution plan and execution details. The only difference we can see in the trace file:

1202

TF 2389 instructs SQL Server to check the value in the orderdate column, while TF 2390 checks the column custid, too. So, if you want to apply this workaround only against really ascending columns use TF 2389. However, TF 2389 works only for columns branded as ascending; if column status changes back to Unknown it does not work anymore. TF 2390 works in both cases. So, if you are sure that column will be always ascending, use TF 2389.

And what happens if the column is marked as Stationary? Well, neither 2390 nor 2389 can help in this case! The plan is suboptimal, the same as without trace flags, with a lot of logical reads and unacceptable long execution time. Until July 2014 SQL Server 2012 didn’t have a workaround for dealing with stationary columns. Since that you can use another trace flag (TF 4139) to simulate the same behavior with stationary columns. TF 4139 is available from SQL Server 2012 SP1 CU 10 and SQL Server 2012 SP2 CU 1. More info about it you can find in this KB article.

Use this code to populate a table identical to previous one, but where the orderdate column is marked as Stationary.

Now we’ll execute the same query, this time in three different versions:

SELECT * FROM dbo.OrdersStat
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2390, RECOMPILE);
SELECT * FROM dbo.OrdersStat
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 2389, RECOMPILE);
SELECT * FROM dbo.OrdersStat
WHERE custid = 160 AND orderdate >= '20140101' AND orderdate < '20150101' OPTION(QUERYTRACEON 4139, RECOMPILE);

Let’s observe the execution plans:

1203

As we can see trace flags 2389 and 2390 are helpless, and only TF 4139 ensures the correct execution plan.

Conclusion

Let’s summarize dealing with ascending columns with the help of trace flags so far:

TF 2389 helps the Query Optimizer to come up with better estimations and correct execution plans only for columns branded as Ascending.

TF 2390 instructs SQL Server to perform on-the-fly calculation for statistics objects for columns marked as Ascending or Unknown.

TF 4139 works with all three Leading Column Types: Ascending, Unknown and Stationary. However, it is available from SQL Server 2012 SP1 CU10 or SP2 CU1

And one note regarding the status of the flags in the latest SQL Server version: SQL Server 2014 does not support these flags. It ignores all of them, since the behavior we described in this and previous post is implemented in the new database engine and SQL Server 2014 without trace flags comes up with similar execution details as SQL Server 2012 with the flags. It seems that we don’t need these flags in SQL Server 2014. It seems. Until the next post.

Thanks for reading.

Comments

  1. Great stuff!
    “It seems that we don’t need these flags in SQL Server 2014. It seems. Until the next post.” ==>
    As long as the new cardinality estimator is enabled either with compatibility level >= 120, or with trace flag 2312.
    If the old cardinality estimator is in play – whether due to compatibility level <= 110 or trace flag 9481 – the role of trace flags 2389, 2390 & 4139 may again be important.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: