Ever been burned by performance regression after a SQL Server version upgrade? I came across an article on the Microsoft.com website that brought back some bad memories after an upgrade to SQL Server 2014 from SQL Server 2012: Query Store Usage Scenarios
The article states that ‘prior to SQL Server 2014 (12.x), users were exposed to the risk of performance regression during the upgrade to the latest platform version’.
Duh!!! This ‘feature’ caused panic at my last job.
DBAs upgraded production SQL Servers to SQL 2014 after 6 months of testing in QA. As soon as the production servers were upgraded, all types of queries went awry with performance issues. Unfortunately, to add fuel to the fire, software code, data schema and stored procs were upgraded at the same time. So the DBA team, developers and QA scrambled for days fixing numerous queries and trying to explain what went wrong. DBAs blamed developers, developers blamed DBAs and QA, QA blamed lack of tools and DBAs. VPs were steaming!!!
Oh it was NOT a fun time. Eventually a few SQL server databases had to go to SQL Server 2012 compatibility mode. It was the worst disaster that I was part of after a SQL Server upgrade. So it’s nice to know that the disaster can be explained in the article 4 years later. Right? Hope my old bosses and VP’s are reading this!
The article points out these steps to prevent Query performance disasters in future SQL Server version upgrades.
1 – Upgrade SQL Server without changing the database compatibility level
2 – Enable Query Store to save workload
3 – Allow Query Store to capture queries and plans, and establishes a performance baseline with the source/previous database compatibility level
4 – Move to latest database compatibility level
5 – Use Query Store for analysis and regression fixes
The article is chock full of additional information and ways to use Query Store effectively to prevent a similar SQL Server upgrade disaster.
No excuses! Learn how to use Query Store before you upgrade your SQL Server version again!
Here’s additional helpful articles on Query Store.
Monitoring performance by using the Query Store
Best Practice with the Query Store