1

SQL Server 2017 introduced the system view known as sys.dm_db_tuning_recommendations, which displays all detected plan regressions. Using this view, you can retrieve data, compare the last known good plan with the regressed plan, and obtain the script to manually resolve the regression. I came across this article which helps identify bad parameter sniffing using sys.dm_db_tuning_recommendations system view.

Article: https://straightpathsql.com/archives/2022/10/alerting-on-bad-parameter-sniffing-using-automatic-tuning/

This is very very helpful, unfortunately, it isn't available in SQL Server 2016 where Query Store was first introduced. Can I ask any help on how I can possibly query the same information using the DMV:

  • Sys.query_store_query
  • Sys.query_store_query_text
  • Sys.query_store_plan
  • Sys.query_store_runtime_stats

My goal is to identify regressed queries with more than 1 plan so I can compare the plans to choose which is the best plan to use. Basically the script format of the chart provided under regression queries but with more information.

Improve this question
3
  • 1
    To find regressed-queries I imagine you'd have to set-up your own query-performance-tracking system which would need to poll the sys,query_store... views and look for changes over time. TBH, I don't think that's worth anyone's time - it makes far better economic sense to buy an upgrade to SQL Server 2022 (assuming that (time (in hours) to build, test, verify, and use the query-regression tracker) * (dev cost per hour) > (cost of license upgrade from 2016 to 2022 ) Commented Aug 30, 2024 at 4:43
  • ...does your client/employer participate in Microsoft Software Assurance, btw? Commented Aug 30, 2024 at 4:44
  • Hi @Dai Yes, our company participates in Microsoft Software Assurance and Yes, you are correct that is exactly what I'm trying to do (set up my own query-performance-tracking system). Unfortunately, upgrading takes up a lot of planning and approvals, so it's not the immediate solution. Commented Aug 30, 2024 at 5:33
Related questions
4347
UPDATE from a SELECT
2204
How to check if a column exists in a SQL Server table
3327
How to add a column with a default value to an existing table in SQL Server?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.