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