Udostępnij za pośrednictwem

"You Shall Not Regress!!!" - How SQL Server 2017 prevents plan regressions?

  • Artykuł

SQL plan regression is a case when a T-SQL query that executed fine becomes much slower or uses more resources because the underlying SQL plan changed. SQL Server 2017 would not allow that kind of regressions if you enable new automatic tuning feature on your database.

What is plan regression?

Imagine that you execute the following query on your database:

 select avg([UnitPrice]*[Quantity])
from Sales.OrderLines
where PackageTypeID = @packagetypeid

SQL Database engine would produce a SQL plan that contains operators that will execute this query. One SQL plan for this query might look like:

query plan

However, there are other SQL plans that can be used to execute this T-SQL query. As an example, SQL Database engine might choose a plan with "Stream Aggregate (Aggregate)" instead of the "Hash Match (Aggregate)".

In the figure below you can see that this plan might have worse performance than the first one:

Plan regression

In SQL Server 2016 you need to monitor Query Store views or periodically open Query Store UI in SQL Server Management studio to identify these kind of regressions and manually force plan (1) when you notice this problem.

And then came SQL Server 2017...

SQL Server 2017 constantly monitors T-SQL queries and compares performance of the SQL plans when they are changed. SQL Server 2017 compares CPU time used by new SQL plan with the CPU time used by last good plan. If there is a significant difference between the plans, SQL Server 2017 can react and fix performance regression.

You can enable automatic tuning on your database and instruct database to force the last known good plan if it is better than the current plan, using the following statement:

 ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN=ON )

SQL Database engine will constantly monitor your workload, remember the last good plan that was successfully executed, identify plans that are changed, and compare performance of the new plan with the performance of the last known good plan. If there is some performance regression, Automatic tuning option will immediately force execution of the last known good plan instead of the regressed plan and fix the problem.

SQL Database engine will also constantly monitor forced last good plan and check is the plan actually better than the regressed plan. If performance comparison between forced plan and regressed plan don't prove that forcing added some benefits, last good plan will be automatically unforced and Query optimizer will create a new plan. Also, if any event impacts the forced plan (e.g. underlying schema or statistics are changed), SQL Database engine will unforce the plan and let query optimizer to find better plan.

NOTE: Only the plans that are managed by SQL Server database engine are monitored and reverted if they don't bring some significant performance impact. If you manually force the plans, you would need to monitor performance of your queries and decide would you keep them forced. SQL Server 2017 will not revert any explicit user decision to force some plan.

Conclusion

SQL Server 2017 will not allow query plan regression in your workload. New automatic tuning option monitors your queries and forces good plans whenever it finds big difference in CPU time.

This might be huge helper in your databases especially if you have a large number of queries in your workload that you need to monitor.

Find more information about Automatic tuning in SQL Server 2017 here.

Comments

  • Anonymous
    April 26, 2017
    The comment has been removed