I've scheduled job with statistics update on 100GB database (some of them were really old, from several months to 2-3 years), job ended succesfully after 20minutes at 1AM.

Now i checked Query Store (after 15 hours ~) and there are several regressed queries with markedly worse execution plans. Average times are significantly worse than one day before update stats.

What should i do now? Do you think DBCC FREEPROCCACHE could help? Thanks a lot

PS: SQL Server is version 2019, but database has Compatibility Level set on 100 (2008). Idk why, this is our new customer. This will be changed soon.

  • 3
    well, changing the compatibility level to something newer will cause the engine to use the new cardinality estimating engine so your execution plans will change once again. I'd change to something newer, THEN focus on tuning. Dec 9 '20 at 15:35
  • Are auto stats updates enabled in the database? Did the manual update stats job update all stats, or only out-of-date stats? Got any trace flags globally enabled in the instance (trace flags 4199 for optimizer hotfixes and 2371 for stats update threshold come to mind as relevant due to the very low compat level but others perhaps as well)? Have you evaluated whether parameter sniffing is affecting these queries (parameterized queries, stored procedures)?
    – sqL_handLe
    Dec 9 '20 at 16:21
  • 1
    Oops one more - have you tried forcing plans for any of these queries via Query Store?
    – sqL_handLe
    Dec 9 '20 at 16:25
  • 1
    Auto stats updates are enabled. Manual update of stats was done with full scan and updated all stats in database (checked in sys.stats JOIN sys.stats_columns ...) I didn't tried forcing plans in Query Store yet.
    – lear1
    Dec 9 '20 at 18:56
  • 1
    Customer upgraded to SQL Server 2019 from 2008 and didn't change Compatibility Level. They even didn't know there is need to change something. My task is optimize it, because their application is slow. I recommended them to change compatibility level to 150 (was tested on DEV server and no problem) but i must wait to maintenance window. Meanwhile i created Maintenance plan (reoragnize indexes + update stats) and that's why i am here.
    – lear1
    Dec 9 '20 at 21:35

These are the steps I would take:

  1. First of all download Microsoft Migration Assistant and try to spot incompatibilities between compatibility level 2008 and 2012.
  2. Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide.
  3. Spot the incompatible queries using the Query Tuning Assistant .
  4. If everything is alright now move to 2012 compatibility level first and check if all the applications are still working. If the database is old I suppose the applications bight be old too. The applications may contain deprecated command not compatible with later version of SQL Server
  5. If the previous step worked now move to compatibility level 2016 and activate Query Store. Use that to monitor your database for a good week or two and check if everything is alright. At this point 99% of your queries should run faster and 1% should be completely blocked. Query Store is your friend and you will use it to debug blocking and waits.
  6. Now that everything is fixed moving to compatibility level 2019 should be a glass of water as there are not many changes between 2016 and 2019

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.