Identifying SQL Server 2014 New Cardinality Estimator issues and Service Pack 1 improvement

  • Article

 

In a previous blog, I talked about SQL 2014’s new Cardinality Estimator (new CE) and trace flags 9481 and 2312 can be used to control which version of Cardinality Estimator is used.

In this blog, I will modify a real scenario customer hit to show you that how you can use these trace flags to spot issues related to new CE and how SQL 2014 SP1 can help.

Problem

Customer reported to us that a particular query ran slowly on SQL 2014.  After troubleshooting, we determined that new Cardinality Estimator grossly over-estimated.  We compared the plans using trace flag 2312 (new CE) and 9481 (old CE).

Here is a simplified repro without revealing customer’s table and data.  Customer’s query is more complex.  But the issue is the over-estimation of a particular join that produced a bad query plan overall.

The script below inserts 1 million rows in table t.  c1 is primary key.  c2 has duplicates( each number repeats 100 times).

create table t (c1 int primary key, c2 int)
go
set nocount on

go
begin tran
declare @i int
set @i = 0
while @i < 1000000
begin

insert into t values (@i, @i % 10000)
set @i = @i +1

end
commit tran
go
create index ix on t(c2)
go
update statistics t
go

 

How many rows do you think the following query will return?  It will return 100 rows.  Note that I used querytraceon 2312 to force new CE on SQL 2014.  But if your database compatibility level is 120, you will get new CE without having to use the trace flag.  Again, the previous blog has instructions how to control new and old CE with trace flags and database compatibility level.

select t1.c2 from t t1 join t t2 on t1.c2 = t2.c2
where t1.c1 = 0 option (querytraceon 2312)

 

Do you think the query below will return more rows or less rows than the query above?  Note that I added an AND clause “t1.c1 <> t2.c1”.   This should have made it more restrictive and return no more rows than the previous query.  It actually returns 99 rows because there is one row filtered out by t1.c1 <> t2.c1.

select t1.c2 from t t1 join t t2 on t1.c2 = t2.c2 and t1.c1 <> t2.c1
where t1.c1 = 0 option (querytraceon 2312)

Let’s take a look at optimizer estimates.

The first query has very accurate estimate.

image

Take a look at the estimate below for secondary query.  The query has 99 rows returned but the estimate is 1,000,000 rows.    The difference is that I added one more AND predicate in the secondary query (t1.c1 <> t2.c1).  It should have cut down the estimate.  But it actually made it much larger.

image

 

Note that the same query has fairly low estimate by forcing old CE with trace flag 9481

select t1.c2 from t t1 join t t2 on t1.c2 = t2.c2 and t1.c1 <> t2.c1
where t1.c1 = 0 option (querytraceon 9481)

image

 

It is this behavior that made customer’s original query much slower. This is a bug in new Cardinality Estimator.

Customer called in for help tuning the query.  First we had them revert to old Cardinality Estimator by trace flag 9481 which made query fast.   We knew that we had quite a few fixes in this space and asked customer to apply SP1 on a test machine.  But the query was still slow.   So we went ahead and collected statistics clone and started to look at query in house.   We were able to reproduce the issue where new Cardinality Estimator had very high estimate but old Cardinality Estimator has low estimate even on SP1

We thought it’s a new bug after SP1.  But as we looked at the fix more closely, it required trace flag  4199 to be enabled.  In fact all optimizer fixes require 4199 to activate.    After enabling 4199, SP1 was able to estimate correctly.  Customer tested their original query on SP1 with trace flag 4199 and it ran fast.

Solution

You need to apply SP1 but you must also enable trace flag 4199 in order to activate the fix.

SQL Server 2014 Service Pack 1 made various fixes on new Cardinality Estimator (new CE).  The release notes also documents the fixes.

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

 

twitter| pssdiag |Sql Nexus

Comments

  • Anonymous
    August 23, 2015
    What Compatibility level i need to use when i enable trace flag 4199 with SQL Server 2014 SP1? is it the Older or newer compatibility ?

  • Anonymous
    September 03, 2015
    Use sql2014 compatibility and test your environment. usually would n't be any issues. If you encounter any performance issues they might be because of old optimizer

  • Anonymous
    November 17, 2015
    Hi, I tried using trace flag 4199 and SP1 on 2014 to improve the performance of a slow query but it didn't help. Please let me know if there is any other suggestion. Thanks.

  • Anonymous
    March 24, 2016
    Is this fix included in any CUs for SP1? Thanks.

  • Anonymous
    April 01, 2016
    Hi,I am also tried using trace flag 4199 and SP1 on 2014 to improve the performance of a slow query but it didn't help.The CPU,Memory all the resource utilization is reaching to the peak.Please let me know if there is any other suggestion.

    • Anonymous
      April 01, 2016
      I have tried using trace flag 4199 and SP1 on 2014 to improve the performance of a slow query but it didn’t help. The CPU,Memory all the resource utilization is reaching to the peak. Please let me know if there is any other suggestion.

  • Anonymous
    June 21, 2016
    We are having performance issues with 2014 compatibility that we did not have with 2012. Did Service Pack 1 fix the "bug" mentioned in this article for the Cardinality Estimator in 2014?

  • Anonymous
    September 19, 2016
    Does SQL 2014 SP2 also require enabling Flag 4199 ?

  • Anonymous
    October 05, 2016
    I have a server running SQL2014 (SP1 CU5) that appears to be having some issues with the new CE on certain queries (query hangs, no results returned). I have tried trace flag 4199 per the guidance in this post but it has not changed the behavior. I tried trace flags 9476 and 9481 per guidance from PFE and both of those flags did improve the situation, as did setting the DB to compat level 110. Any thoughts on why flag 4199 did not give us the same benefit as using either 9476 or 9481?

  • Anonymous
    October 21, 2016
    Even after installing SP2, we do not see performance improvements. However query runs faster with 1. 9481 flag in compatibility level 120. 2. compatibility level 110. Query performs worst with without any flag in compatibility level 120Any suggestion for better practice?

    • Anonymous
      November 28, 2016
      Hi all,I believe this article is only for specific issue in new cardinality estimator, for other engine behaviour we need to address the issue.

  • Anonymous
    December 19, 2016
    Hi Jack,Sorry for reaching to this article, we are at exact this behavior, but different parameters, we do have two sql 2014 instances with SP2 and almost same config, but newly built sql server couldnt get the plan identical to the prior sql 2014 sql server. 4199 did the trick and we are on the safe board. But 4199 is collection of multiple trace flag ( correct?), we have tried all the trace flags mentioned in the support article of 4199 , none resulted in the same query plan, are there any fixes which are undocumented ? if yes, can you please share for our knowledge ?, and thanks a lot for the traceflag :)