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

Published Jan 15 2019 04:38 PM 554 Views
Microsoft
First published on MSDN on Jun 16, 2015

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.


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.

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)

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

%3CLINGO-SUB%20id%3D%22lingo-sub-318591%22%20slang%3D%22en-US%22%3EIdentifying%20SQL%20Server%202014%20New%20Cardinality%20Estimator%20issues%20and%20Service%20Pack%201%20improvement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318591%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jun%2016%2C%202015%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIn%20a%20previous%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fpsssql%2Farchive%2F2014%2F04%2F01%2Fsql-server-2014-s-new-cardinality-estimator-part-1.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20blog%20%3C%2FA%3E%20%2C%20I%20talked%20about%20SQL%202014%E2%80%99s%20new%20Cardinality%20Estimator%20(new%20CE)%20and%20trace%20flags%209481%20and%202312%20can%20be%20used%20to%20control%20which%20version%20of%20Cardinality%20Estimator%20is%20used.%3C%2FP%3E%0A%20%20%3CP%3EIn%20this%20blog%2C%20I%20will%20modify%20a%20real%20scenario%20customer%20hit%20to%20show%20you%20that%20how%20you%20can%20use%20these%20trace%20flags%20to%20spot%20issues%20related%20to%20new%20CE%20and%20how%20SQL%202014%20SP1%20can%20help.%3C%2FP%3E%0A%20%20%3CH3%20id%3D%22toc-hId-1477863730%22%20id%3D%22toc-hId-1507351983%22%3EProblem%3C%2FH3%3E%0A%20%20%3CP%3ECustomer%20reported%20to%20us%20that%20a%20particular%20query%20ran%20slowly%20on%20SQL%202014.%26nbsp%3B%20After%20troubleshooting%2C%20we%20determined%20that%20new%20Cardinality%20Estimator%20grossly%20over-estimated.%26nbsp%3B%20We%20compared%20the%20plans%20using%20trace%20flag%202312%20(new%20CE)%20and%209481%20(old%20CE).%3C%2FP%3E%0A%20%20%3CP%3EHere%20is%20a%20simplified%20repro%20without%20revealing%20customer%E2%80%99s%20table%20and%20data.%26nbsp%3B%20Customer%E2%80%99s%20query%20is%20more%20complex.%26nbsp%3B%20But%20the%20issue%20is%20the%20over-estimation%20of%20a%20particular%20join%20that%20produced%20a%20bad%20query%20plan%20overall.%3C%2FP%3E%0A%20%20%3CP%3EThe%20script%20below%20inserts%201%20million%20rows%20in%20table%20t.%26nbsp%3B%20c1%20is%20primary%20key.%26nbsp%3B%20c2%20has%20duplicates(%20each%20number%20repeats%20100%20times).%3C%2FP%3E%0A%20%20%3CP%3Ecreate%20table%20t%20(c1%20int%20primary%20key%2C%20c2%20int)%20%3CBR%20%2F%3E%20go%20%3CBR%20%2F%3E%20set%20nocount%20on%3C%2FP%3E%0A%20%20%3CP%3Ego%20%3CBR%20%2F%3E%20begin%20tran%20%3CBR%20%2F%3E%20declare%20%40i%20int%20%3CBR%20%2F%3E%20set%20%40i%20%3D%200%20%3CBR%20%2F%3E%20while%20%40i%20%26lt%3B%201000000%20%3CBR%20%2F%3E%20begin%3C%2FP%3E%0A%20%20%3CP%3Einsert%20into%20t%20values%20(%40i%2C%20%40i%20%25%2010000)%20%3CBR%20%2F%3E%20set%20%40i%20%3D%20%40i%20%2B1%3C%2FP%3E%0A%20%20%3CP%3Eend%20%3CBR%20%2F%3E%20commit%20tran%20%3CBR%20%2F%3E%20go%20%3CBR%20%2F%3E%20create%20index%20ix%20on%20t(c2)%20%3CBR%20%2F%3E%20go%20%3CBR%20%2F%3E%20update%20statistics%20t%20%3CBR%20%2F%3E%20go%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EHow%20many%20rows%20do%20you%20think%20the%20following%20query%20will%20return%3F%26nbsp%3B%20It%20will%20return%20100%20rows.%26nbsp%3B%20Note%20that%20I%20used%20querytraceon%202312%20to%20force%20new%20CE%20on%20SQL%202014.%26nbsp%3B%20But%20if%20your%20database%20compatibility%20level%20is%20120%2C%20you%20will%20get%20new%20CE%20without%20having%20to%20use%20the%20trace%20flag.%26nbsp%3B%20Again%2C%20the%20previous%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fpsssql%2Farchive%2F2014%2F04%2F01%2Fsql-server-2014-s-new-cardinality-estimator-part-1.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20blog%20%3C%2FA%3E%20has%20instructions%20how%20to%20control%20new%20and%20old%20CE%20with%20trace%20flags%20and%20database%20compatibility%20level.%3C%2FP%3E%0A%20%20%3CP%3Eselect%20t1.c2%20from%20t%20t1%20join%20t%20t2%20on%20t1.c2%20%3D%20t2.c2%20%3CBR%20%2F%3E%20where%20t1.c1%20%3D%200%20option%20(querytraceon%202312)%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EDo%20you%20think%20the%20query%20below%20will%20return%20more%20rows%20or%20less%20rows%20than%20the%20query%20above%3F%26nbsp%3B%20Note%20that%20I%20added%20an%20AND%20clause%20%E2%80%9Ct1.c1%20%26lt%3B%26gt%3B%20t2.c1%E2%80%9D.%26nbsp%3B%26nbsp%3B%20This%20should%20have%20made%20it%20more%20restrictive%20and%20return%20no%20more%20rows%20than%20the%20previous%20query.%26nbsp%3B%20It%20actually%20returns%2099%20rows%20because%20there%20is%20one%20row%20filtered%20out%20by%20t1.c1%20%26lt%3B%26gt%3B%20t2.c1.%3C%2FP%3E%0A%20%20%3CP%3Eselect%20t1.c2%20from%20t%20t1%20join%20t%20t2%20on%20t1.c2%20%3D%20t2.c2%20and%20t1.c1%20%26lt%3B%26gt%3B%20t2.c1%20%3CBR%20%2F%3E%20where%20t1.c1%20%3D%200%20option%20(querytraceon%202312)%3C%2FP%3E%0A%20%20%3CP%3ELet%E2%80%99s%20take%20a%20look%20at%20optimizer%20estimates.%3C%2FP%3E%0A%20%20%3CP%3EThe%20first%20query%20has%20very%20accurate%20estimate.%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68325i8D6D668A1030F759%22%20%2F%3E%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3ETake%20a%20look%20at%20the%20estimate%20below%20for%20secondary%20query.%26nbsp%3B%20The%20query%20has%2099%20rows%20returned%20but%20the%20estimate%20is%201%2C000%2C000%20rows.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20The%20difference%20is%20that%20I%20added%20one%20more%20AND%20predicate%20in%20the%20secondary%20query%20(t1.c1%20%26lt%3B%26gt%3B%20t2.c1).%26nbsp%3B%20It%20should%20have%20cut%20down%20the%20estimate.%26nbsp%3B%20But%20it%20actually%20made%20it%20much%20larger.%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68326i554B2CEADA48F4A8%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ENote%20that%20the%20same%20query%20has%20fairly%20low%20estimate%20by%20forcing%20old%20CE%20with%20trace%20flag%209481%3C%2FP%3E%0A%20%20%3CP%3Eselect%20t1.c2%20from%20t%20t1%20join%20t%20t2%20on%20t1.c2%20%3D%20t2.c2%20and%20t1.c1%20%26lt%3B%26gt%3B%20t2.c1%20%3CBR%20%2F%3E%20where%20t1.c1%20%3D%200%20option%20(querytraceon%209481)%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68327i3EB3F4D7CFD5BB27%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EIt%20is%20this%20behavior%20that%20made%20customer%E2%80%99s%20original%20query%20much%20slower.%20This%20is%20a%20bug%20in%20new%20Cardinality%20Estimator.%3C%2FP%3E%0A%20%20%3CP%3ECustomer%20called%20in%20for%20help%20tuning%20the%20query.%26nbsp%3B%20First%20we%20had%20them%20revert%20to%20old%20Cardinality%20Estimator%20by%20trace%20flag%209481%20which%20made%20query%20fast.%26nbsp%3B%26nbsp%3B%20We%20knew%20that%20we%20had%20quite%20a%20few%20fixes%20in%20this%20space%20and%20asked%20customer%20to%20apply%20SP1%20on%20a%20test%20machine.%26nbsp%3B%20But%20the%20query%20was%20still%20slow.%26nbsp%3B%26nbsp%3B%20So%20we%20went%20ahead%20and%20collected%20statistics%20clone%20and%20started%20to%20look%20at%20query%20in%20house.%26nbsp%3B%26nbsp%3B%20We%20were%20able%20to%20reproduce%20the%20issue%20where%20new%20Cardinality%20Estimator%20had%20very%20high%20estimate%20but%20old%20Cardinality%20Estimator%20has%20low%20estimate%20even%20on%20SP1%3C%2FP%3E%0A%20%20%3CP%3EWe%20thought%20it%E2%80%99s%20a%20new%20bug%20after%20SP1.%26nbsp%3B%20But%20as%20we%20looked%20at%20the%20fix%20more%20closely%2C%20it%20required%20trace%20flag%26nbsp%3B%204199%20to%20be%20enabled.%26nbsp%3B%20In%20fact%20all%20optimizer%20fixes%20require%204199%20to%20activate.%26nbsp%3B%26nbsp%3B%26nbsp%3B%20After%20enabling%204199%2C%20SP1%20was%20able%20to%20estimate%20correctly.%26nbsp%3B%20Customer%20tested%20their%20original%20query%20on%20SP1%20with%20trace%20flag%204199%20and%20it%20ran%20fast.%3C%2FP%3E%0A%20%20%3CH3%20id%3D%22toc-hId--1074293231%22%20id%3D%22toc-hId--1044804978%22%3ESolution%3C%2FH3%3E%0A%20%20%3CP%3EYou%20need%20to%20apply%20SP1%20but%20you%20must%20also%20enable%20trace%20flag%204199%20in%20order%20to%20activate%20the%20fix.%3C%2FP%3E%0A%20%20%3CP%3ESQL%20Server%202014%20Service%20Pack%201%20made%20various%20fixes%20on%20new%20Cardinality%20Estimator%20(new%20CE).%26nbsp%3B%20The%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fkb%2F3058865%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20release%20notes%20%3C%2FA%3E%20also%20documents%20the%20fixes.%3C%2FP%3E%0A%20%20%3CP%3EJack%20Li%20%7CSenior%20Escalation%20Engineer%20%7C%20Microsoft%20SQL%20Server%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftwitter.com%2Fjackli8898%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Etwitter%20%3C%2FA%3E%20%7C%20%3CA%20href%3D%22http%3A%2F%2Fdiagmanager.codeplex.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20pssdiag%20%3C%2FA%3E%20%7C%20%3CA%20href%3D%22http%3A%2F%2Fsqlnexus.codeplex.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20Sql%20Nexus%3C%2FA%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318591%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jun%2016%2C%202015%20%26nbsp%3B%20In%20a%20previous%20blog%2C%20I%20talked%20about%20SQL%202014%E2%80%99s%20new%20Cardinality%20Estimator%20(new%20CE)%20and%20trace%20flags%209481%20and%202312%20can%20be%20used%20to%20control%20which%20version%20of%20Cardinality%20Estimator%20is%20used.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318591%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%202014%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 04:38 PM
Updated by: