Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

Question

We recently upgraded from SQL Server 2008R2 to SQL Server 2014 SP1 + CU4.

After a couple of weeks, there were problems with execution plans not properly estimating row counts. The problem got so bad at one point that the decision was made to revert back to the old cardinality estimator by enabling the 9481 traceflag and UPDATEing statistics again. When I say "got so bad" I'm referring to the execution times for queries increasing by a magnitude of 10 in some cases.

Using traceflag 9481 has resolved the problem but this can't be the solution can it?

Searching Google has shown some taking the old cardinality estimator route and others using a combination of 2312 and 4199 to use the new estimator.

So after upgrading from 2008R2 to 2014, what combination of traceflags (if any) and other steps should we be taking?

Thanks, Craig

Update April 26th 9am

The 4199 traceflag does not turn on the new Cardinality Estimator. I had to use the 2312 traceflag instead.

With the 4199 traceflag, the version was still on 70. The answer from Chris Wood reminded me of an article from Brent Ozar I'd also read at some point. Still waiting to see if the execution times improve.

Answer 1

Based on my experience with such issue and as mentioned in This Blogs.msdn article

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.

So I suggest to suppress regression you enable it.

Trace flag 9481 solved your problem because if forced SQL Server to use old optimizer. So basically you are using SQL Server 2014 but not new CE which comes with it.

Trace flag 4199 makes sure the optimizer is using all changes/fixes made to SQL Server optimizer since SQL Server 2005.

From this support article

Trace flag 4199 was used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form. Starting with SQL Server 2016 RTM, the database COMPATIBILITY_LEVEL setting will be used enable trace flag 4199-related hotfixes on-by-default. This article describes the mechanics and policy of how plan-affecting hotfixes will be delivered for SQL Server 2016 and later versions.

There are three ways to do it depending on need.

1. By enabling the trace flag in a batch (by using DBCC TRACEON command) right before the target query, and then disabling the trace flag (by using DBCC TRACEOFF command) right after the query.

2. Starting with Microsoft SQL Server 2005 Service Pack 2 (SP2) and Microsoft SQL Server 2008, the query-level option "QUERYTRACEON" is available. This option lets you to enable a plan-affecting trace flag only during single-query compilation.

3. You enable it in starup parameters.

The first and second point will only make sure query/batch runs with 4199 trace flag while third point makes sure any query which runs sees this trace flag enabled.

NOTE: Enabling trace flag requires sysadmin permission so if you ask your developer to use it in query for instance he might not be able to assuming developers have limited access.

I would suggest you to use querytraceon or dbcc traceon to see if queries are performing up to mark.

Answer 2

One suggestion that I saw when the new CE was announced came from Brent Ozar. You needed to test this out before you went live with 2014. Run on 2014 but with the level indicating 2012 and benchmark the times and plans. Then change to 2014 level and see which plans changed. From there you attempted to tune the bad plans.

Trace flag 4199 has been around for a long time and we use in for 2008R2 and 2012. If you could have benchmarked you would have had to use the same trace flags for both runs.

Answer 3

You could disable it on a per database level by changing the compatibility level; assuming they don't use SQL 2014 specific language features.

But ultimately I think you need to determine which queries have bad execution plans and why. To do this you could capture a replay trace and start replaying it on a test bed with and without the trace flag / compat level, and picking through the plan cache (or running an XE session during replay to determine how long queries take, then looking for differences).

Once you know the enemy you will be able to come up with a proper solution (not sure if SO is a great place for that but if not then try the SQL Sentry Plan Explorer forum which is pretty much made for this kind of analysis).

I do have to ask though, what are you using to recalc your stats, what options are you using, and how often does it run? Just wondering if the underlying problem is poor maintenance (just in case, but assuming not).