SQL Server 2012升级至2016后查询性能下降技术问询

Hey fellow dev, I totally feel your pain—upgrading from SQL Server 2012 to 2016 SP1 (the exact build you mentioned: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)) can throw some unexpected performance curveballs, like queries that used to fly in under a second suddenly taking 3-4 seconds to finish. Let’s break down what you can do about this:

You’re right that switching the database compatibility level back to 2012 is a quick band-aid. Here’s how to execute it:

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 110;

This forces the query optimizer to use the 2012-era behavior, which will get your fast queries back immediately. But keep in mind, this means you’re missing out on all the 2016 optimizer improvements and new features, so it’s only a temporary solution.

To stay on 2016 without sacrificing performance, you need to dig into why the queries slowed down in the first place:

• Compare Execution Plans: Capture the execution plan of the slow query in 2016 and contrast it with the fast plan from 2012. Look for key differences like:
  • A switch from an index seek to a full scan
  • Unexpected key lookups adding overhead
  • Changes in parallelism settings that hurt performance
    You can use SQL Server Management Studio’s "Include Actual Execution Plan" feature (Ctrl+M) to grab these plans easily.
• Update Outdated Statistics: Stale statistics are a top culprit for bad query plans post-upgrade. Run this for tables involved in the slow query:

  UPDATE STATISTICS [YourTableName] WITH FULLSCAN;
  

• Per-Query Hints (Instead of Global Changes): If you don’t want to roll back the entire database’s compatibility, you can force individual queries to use the 2012 optimizer behavior with a hint:

  SELECT * FROM YourTable WHERE YourCondition
  OPTION (QUERYTRACEON 9481);
  

  Or use the more readable hint for legacy cardinality estimation (a common source of plan shifts):

  SELECT * FROM YourTable WHERE YourCondition
  OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Tune or Rewrite Problem Queries: Some older queries rely on 2012 optimizer quirks. Try simplifying the query (e.g., avoid SELECT *, explicitly list columns), adjusting JOIN order, or adding/modifying indexes to align with the 2016 optimizer’s preferences.
• Leverage 2016’s Performance Features: Don’t forget that 2016 has tools to boost speed, like columnstore indexes for large tables or memory-optimized tables for high-throughput workloads. These might offset any slowdowns from other queries.

At the end of the day, rolling back compatibility is great for getting back up and running fast, but taking the time to diagnose and fix specific queries will let you take full advantage of all the improvements in SQL Server 2016.

内容的提问来源于stack exchange，提问作者Bagpuss