Looking for a script to get list of Regressing Queries similar to Query Store

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  May 27, 2020 at 4:11 pm

  #3757773

  is there a script that someone may have already written /published somewhere

  to get the list of degrading/regressing performance wise  objects  similar to query store REGRESSING queries REPORT?

  To get some kind of a report similar to what Query Store has: Regressing Queries report.

  People who need to see such info once in a while cannot be allowed to physically access Query Store in production, so I am looking to find a script or write a Stored Proc that will output some version of the Query Store regressing queries list/report.

  Thanks.

  Likes to play Chess

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  May 27, 2020 at 9:37 pm

  #3757875

  You may want to look at the DMV sys.dm_db_tuning_recommendations which was added in SQL Server 2017 and see if you can throw together what you need using that DMV. When the engine hits regression in a plan, it writes out to that DMV. Read up on it in the documentation. This is also a good read on that DMV and their is a good example of querying it towards the end of the article:

  SQL Server Automatic Tuning and sys.dm_db_tuning_recommendations

  Sue