5

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.

enter image description here

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.

|improve this question
  • Trace flag 4199 has no affect on CE, I have mentioned that in my answer – Shanky Apr 26 '16 at 9:12
  • Hi @Shanky , I am going to take a step back from this problem for now. So far, unless the cardinality estimator is in v70, the execution times are too long. I'm going with your other suggestion to use querytraceon with the queries that suffered most from v120. Still if the statistics are updated using v70 and not v120, how can I be sure the execution plans created for my tests are accurate? – Craig Efrein Apr 26 '16 at 9:18
  • Will it be possible for you to change compatibility level of database to 110, i.e for SQL Server 2012. Doing this will force SQL Server to use old optimizer. Regarding execution plan as you said you run one query with querytraceon hint for old optimizer and other without it and compare the execution plans. – Shanky Apr 26 '16 at 9:29
2

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.

|improve this answer
  • hello Shanky and thanks for the assist. So if I understand you correctly, there may be corrections to the cardinality estimator but they won't be effective unless I enable traceflag 4199? – Craig Efrein Apr 25 '16 at 12:40
  • Yes you are correct. Starting from 2005 Sp3 trace flag 4199 was one trace flag that would include all fixes. just to add in SQL Server 2016, trace flag 4199 hotfixes that are made to previous releases of SQL Server will become enabled under database COMPATIBILITY_LEVEL 130 without trace flag 4199 enabled. – Shanky Apr 25 '16 at 12:48
  • So I'm back to square one after switching over to 4199 and updating statistics again. Execution times were up and the CPU was once again maxing out. A deeper analysis of what's going on will be necessary. One of my coworkers jokingly said that we'll probably end up using the 2014 cardinality estimator with the 2016 version of MSSQL. – Craig Efrein Apr 25 '16 at 18:51
  • Can you change compatibility level of database to 110 and see if this works – Shanky Apr 26 '16 at 3:30
1

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.

|improve this answer
  • Thanks Chris, I too had read that article but thought that the 4199 traceflag would implement the new estimator. – Craig Efrein Apr 26 '16 at 7:05
0

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).

|improve this answer
  • Hello Cody, the statistics are being updated using a FULLSCAN. Considering the thousands of complex queries involved, it won't be possible to analyze all of their execution plans one by one – Craig Efrein Apr 25 '16 at 9:08
  • How often are you updating them and how big are the databases causing the issue? (I also have to tell you, it sounds crazy, but there are edge case queries and histograms where SAMPLE will beat FULLSCAN). But this aside taking a replay trace and replaying it elsewhere is going to be key no matter how many thousands of complex queries you have. It's really essential and not difficult; and replaying you'll be able to see 1:1 the outliers. The only thing that will break it is if it's doing bulk inserts from disk files or something transitory like that. – Cody Konior Apr 25 '16 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.