Performance issues after moving a database to SQL Server 2014

Question

I've received a database that was previously on SQL Server 2008R2 but was just put on a SQL Server 2014 instance. There were no maintenance tasks run of any kind run on the database since 2014 (e.g. Rebuilding of indexes, updating statistics, etc.).

Once we ran update statistics as part of our regularly scheduled maintenance that we do on a set schedule, the performance of some queries has taken a massive hit to the point where some select statements will seem to never finish.

The queries have some CASE...WHEN statements in them, but I wouldn't expect there to be such a performance hit. Does anybody have any thoughts on what might cause such issues?

I've tried updating the compatibility level to 120 since it was on 100 when the database first came in but, that didn't make any difference on the performance.

Inculding links to executuion plans:

Slow: https://www.brentozar.com/pastetheplan/?id=Byf1Manvg

Fast: https://www.brentozar.com/pastetheplan/?id=SkJB762De

Answer 1

Ok, at this point I would be considering "manually" replacing statistics to tide me over. I haven't done this myself, but it should work in theory...

You can confirm that this would work as follows:
1. Restore the pre-upgrade database to your LIVE system and confirm that the query is fast in that database, and delete that database once done. (This test is to eliminate any additional environmental differences as the cause. If the query is slow, then the rest of this proposal may be useless.)
1a. Restore the pre-upgrade database to your test system and confirm that the query is fast. 2. Update statistics, and confirm that the query is slow (cancel after a couple minutes of course)
3. Set compat to 120 and confirm that the query is still slow
4. Set compat back to 100 and confirm that the query is still slow
5. Restore another copy of the pre-upgrade database (I'll refer to this as Rest2, and the earlier as Rest1)
6. Extract all statistics from Rest2 using the techniques at https://thomaslarock.com/2015/07/how-to-recreate-sql-server-statistics-in-a-different-environment/ for all tables involved in the problematic queries (or all tables if that's simpler)
7. Apply the statistics to Rest1, and see if query is now fast (you may need to dbcc freeproccache first).

If it works, then it's almost certainly safe to apply the statistics to your live database--just make sure you have ONLY statistics scripted. And also set its compat level to 100. You should then see the queries run fast (though you may need to dbcc freeproccache first--but consider the possible effect on live performance).

Note I'm assuming here (based on your original post) that you do not have statistics autoupdate turned on, and that your data changes slowly enough that your old statistics will do until you figure out how to get your workload working with compat 120 and/or updated statistics (and you may as well sort both at this point).

Answer 2

A few thoughts:

Did you perhaps use a different method for updating statistics than was used on 2008R2? The various methods can have very different results.

What was the compatibility level set to when the database was on 2008R2? It may have been lower than 100 since 100 is the minimum for 2014, and perhaps you've been affected by earlier statistics regeneration changes.

For the best odds of getting the previous behaviour back while you investigate, first set your compatibility level back to 100, and clear the query cache. (DBCC freeproccache--beware possible CPU impact on live system due to recompiles as queries are first rerun.) Then try to get the statistics back to something similar to their previous state (which should also trigger recompiles when appropriate).

(Sorry, I can't add comments yet, no downvotes please.)

Update:

To clarify why I said you should set compatibility level back to 100: That's because level 120 will enable the extensive changes to the cardinality estimator that SQL 2014 introduced, but level 100 will keep things as they were in SQL 2008R2.

But I'm curious as to the difference in query run time between the two plans you posted, as well as which system each was generated on. Because both plans have very similar estimated and actual rows throughout (which suggests that statistics are not the problem), and the plan differences I can see don't seem to be able to cause a massive difference in execution time.

Are you absolutely sure these queries were actually run with no problems before you updated statistics? If not, is it possible your SQL 2014 server has an insanely slow tempdb location which is killing the interim result set writes?

And are the queries you posted executed by the app exactly as posted, or are they run as part of stored procedures (which would make a major difference in the plans generated)? If part of stored procs, perhaps you simply have parameter sniffing issues, which will be alleviated if you add Option (Recompile) to the end of the large complex queries you've identified as problematic--at the cost of some CPU and response time and other downsides. https://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/ is a good place to start understanding the nuances.

Please do post your eventual solutions, as this is an interesting case.