Finding Query Plan Regressions

Jovan Popovic shows how to find query plan regressions in SQL Server 2017:

In CTP2.0 version is added new system view sys.dm_db_tuning_recommendations that returns recommendations that you can apply to fix potential problems in your database. This view contains all identified potential performance issues in SQL queries that are caused by the SQL plan changes, and the correction scripts that you can apply. Every row in this view contains one recommendation that you can apply to fix the issue. Some of the information that are shown in this view are:

  • Id of the query, plan that caused regression, and the plan that that might be used instead of this plan.

  • Reason that describes what kind of regression is detected (e.g. CPU time for the query is changed from 17ms to 189ms)

  • T-SQL script that can be used to force the plan.

  • Information about the current plan, and previous plan that had better performance.

In the “surgical scalpel to chainsaw” range of query tuning options, this rates approximately guillotine.  I think it’ll be a very useful tool for finding issues, but it wouldn’t be wise to start lopping off all the heads just because the optimizer tells you to.  In this context, I imagine this DMV to be about as useful as the missing indexes DMV and for the same reasons.

Related Posts

SQL Server 2017 Finds Plan Regressions

Jovan Popovic shows off some automatic tuning functionality in SQL Server 2017: Plan change regression happens when SQL Database changes a plan for some T-SQL query, and the new plan has the worse performance than the previous one. SQL Server 2017 has Automatic Tuning feature that enables you to easily find plan change regressions and fix them. […]

Read More

Identifying Deprecated Features

Dave Mason provides a method for determining if you’re using deprecated functionality on your SQL Server instance: I’ve wanted to do some Event Notification testing for SQL Server deprecation events for quite some time. The thought process here is that I could send myself an alert to identify usage of SQL Server features that will […]

Read More

Categories

April 2017
MTWTFSS
« Mar May »
 12
3456789
10111213141516
17181920212223
24252627282930