RetiredDBA.com

RetiredDBA.com provides tidbits, ideas and insights to all things Microsoft SQL Server.

Home / SQL Server / Been burned by performance regression after a SQL Server version upgrade?

Been burned by performance regression after a SQL Server version upgrade?

/
/
/

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

This div height required for enabling the sticky sidebar

Advertisers



Programmer Solutions Inc Websites

BUSINESS
CITY JOB RESOURCES
MIDWEST JOB RESOURCES
SOUTH JOB RESOURCES
WEST JOB RESOURCES
Industrial Supply DealsChicago Job ResourceKansas Job ResourceAlabama Job ResourceAlaska Job Resource
Office Supply DealsWashington, DC Job ResourceIllinois Job ResourceArkansas Job ResourceArizona Job Resource
EDUCATION
SPECIAL JOB RESOURCES
Indiana Job ResourceDelaware Job ResourceCalifornia Job Resource
Back To School MallDiversity Job ResourceIowa Job ResourceFlorida Job ResourceColorado Job Resource
SHOPPING
First Job ResourceMichigan Job ResourceGeorgia Job ResourceHawaii Job Resource
Health Care and FitnessFreelance Job ResourceMinnesota Job ResourceKentucky Job ResourceIdaho Job Resource
Just Best GiftsIntern Job ResourceMissouri Job ResourceLouisiana Job ResourceMontana Job Resource
Just Clearance SalesSeasonal Job ResourceNebraska Job ResourceMaryland Job ResourceNevada Job Resource
Just Comparison ShoppingSenior Job ResourceNorth Dakota Job ResourceMississippi Job ResourceNew Mexico Job Resource
Just OutletsVolunteer Job ResourceOhio Job ResourceNorth Carolina Job ResourceOregon Job Resource
Luxury Matters
TARGETED JOB RESOURCES
South Dakota Job ResourceOklahoma Job ResourceUtah Job Resource
Tradin StuffBlue Collar Job ResourceWisconsin Job ResourceSouth Carolina Job ResourceWashington Job Resource
Value BuyersExecutive Job Resource
NORTHEAST JOB RESOURCES
Tennessee Job ResourceWyoming Job Resource
TECHNOLOGY
Finance Job ResourceConnecticut Job ResourceTexas Job Resource 
FrontPage HelpHealthcare Job ResourceMaine Job ResourceVirginia Job Resource 
MCDBA DirectoryIT Job ResourceMassachusetts Job ResourceWest Virginia Job Resource 
Retired DBAPink Collar Job ResourceNew Hampshire Job Resource  
SQL Server DirectorySports Job ResourceNew Jersey Job Resource  
Transact SQL DirectoryWhite Collar Job ResourceNew York Job Resource  
  Pennsylvania Job Resource  
  Rhode Island Job Resource  
  Vermont Job Resource