从SSMS 2008迁移至SSMS 2016后数据库服务器性能骤降求解决方案

First, a quick clarification: You mentioned "SSMS 2008/2016" but it sounds like you're actually migrating the SQL Server Database Engine (not just the management tool). This distinction matters because performance issues stem from the database engine, not the SSMS client itself.

Since you’ve already tried common fixes like adjusting compatibility levels, recompiling procedures, and optimizing disks, let’s dive into targeted troubleshooting steps to get your performance aligned with the old server:

1. Compare Execution Plans (Critical First Step) #

The most impactful action is to capture the actual execution plan of the problematic stored procedure on both servers and compare them side-by-side. Look for key differences:

• Different join operators (e.g., nested loops vs. hash joins that are less efficient for your data)
• Missing index warnings on the 2016 server
• Variations in parallel execution (e.g., old server uses parallelism, new server runs serially)
• Large gaps between estimated and actual row counts (signals cardinality estimation issues)

To capture plans:

• In SSMS, enable "Include Actual Execution Plan" (Ctrl+M) before running the procedure
• Save plans as .sqlplan files and use SSMS’s built-in comparison tool (right-click a plan > Compare Showplan)

2. Test with the Legacy Cardinality Estimator #

SQL Server 2014 introduced a new cardinality estimator (CE) that’s enabled by default for compatibility level 120+. Even if you set compatibility level to 100 (2008), some edge cases might behave differently. Force the legacy CE for your procedure to test if this fixes performance:

EXEC YourProblematicStoredProc
OPTION (QUERYTRACEON 9481); -- Forces legacy CE for this single query execution

If this works, you can temporarily enable it at the database level:

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

Note: This is a workaround—aim to fix the root cause (e.g., update stats with full scans) long-term.

3. Align MAXDOP and Parallelism Settings #

SQL Server 2008 and 2016 have different default MAXDOP (maximum degree of parallelism) behaviors. For your 32-core server:

• Check the old server’s MAXDOP:

  sp_configure 'show advanced options', 1; RECONFIGURE;
  sp_configure 'max degree of parallelism';
  

• Match this setting on the 2016 server if the old value was optimized for your workload. For example, if the old MAXDOP was 8:

  sp_configure 'max degree of parallelism', 8; RECONFIGURE;
  

Also, check if the stored procedure uses hints like MAXDOP 1 that disable parallelism, or if there are server-level restrictions on parallel query execution.

4. Audit Tempdb Configuration #

Tempdb contention is a frequent culprit for slow ETL and stored procedure performance. Ensure your 2016 server’s tempdb follows best practices (and matches the old server where possible):

• Data file count: For 32 cores, use 8-16 equal-sized data files (avoid a single large file)
• Growth settings: Use fixed-size growth (e.g., 1GB) instead of percentage growth to prevent auto-growth thrashing
• Check for bottlenecks: Use these queries to spot tempdb waits:

  -- Check tempdb file space usage
  SELECT * FROM sys.dm_db_file_space_usage WHERE database_id = DB_ID('tempdb');
  
  -- Check for tempdb-related waits
  SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats 
  WHERE wait_type LIKE 'PAGEIOLATCH_%' OR wait_type LIKE 'LCK_M_%'
  ORDER BY wait_time_ms DESC;
  

5. Leverage Query Store (SQL Server 2016+) #

Query Store is a game-changer for post-migration performance issues. Enable it on your database:

ALTER DATABASE YourDatabase SET QUERY_STORE = ON;

Once enabled:

• Navigate to Query Store > Regressed Queries to locate your slow stored procedure
• Compare its execution plans over time (before/after migration)
• Force the good execution plan from the old server (if available in Query Store)
  This bypasses bad plans generated by the new engine until you can resolve the underlying issue.

6. Verify SQL Server Patch Levels #

SQL Server 2016 cumulative updates (CUs) fix many performance bugs. Ensure your 2016 server is running the latest CU (or at least a version that addresses query optimizer/ETL issues). Compare the patch level to your old SQL 2008 server—sometimes specific fixes need to be mirrored in the new environment.

7. Audit Scalar UDFs and Legacy Constructs #

SQL Server 2016 handles scalar user-defined functions (UDFs) differently than 2008. If your procedure uses scalar UDFs, they may cause row-by-row processing bottlenecks. Try:

• Rewriting scalar UDFs as inline table-valued functions (iTVFs)
• Replacing UDFs with built-in functions or computed columns where possible
  Also, check for legacy constructs like cursors or NOLOCK hints that might behave unexpectedly in 2016.

8. Validate SSIS Execution Environment #

Since you use SSIS for ETL, ensure the runtime configuration matches the old server:

• Check if the SSIS package runs in the same isolation level
• Verify OLEDB driver versions (test with the old driver, e.g., SQLNCLI10, if the new MSOLEDBSQL driver is causing issues)
• Use Performance Monitor (PerfMon) to track disk I/O latency, CPU usage, and memory pressure during runs—even optimized disks can have subtle throughput differences.

Final Notes #

Start with steps 1 (execution plan comparison) and 5 (Query Store)—these will give you the fastest insights into why performance is degraded. Once you identify the root cause (e.g., a bad execution plan, cardinality estimation misalignment), you can target fixes more precisely.

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