If Dog Slow Trace Files in SQL Server Profiler are slowing you down – try today’s new and improved SQL Server Automatic Query Tuning and show DETA out the door.
In SQL Server 2016 the Query Store was introduced and made a huge difference in assisting in monitoring and understanding what’s happening in SQL. If you are running SQL Server 2016 and you have enabled the Query Store, a feature that captures compile time and run time statistics of T-SQL statements being executed, the data being collected and held for analysis can help spot execution plans that have regressed due to any number of reasons. Furthermore, we can identify a previous execution plan that performs better and force any future statement executions to utilize the most effective execution plan. We can then identify what caused the regression and resolve the issue so that we are then able to un-force the execution plan thus allowing the DB engine to make the correct decisions for the statement being executed.
With the release of SQL Server 2017, this is now taken a step further and the DB engine can look at information captured in the Query Store and use it in making decisions when regression is detected. It will automatically force the last good plan.
Enable Automatic Tuning on a User Database:
That’s it for setup. With this configuration the system will keep track of statement executions and determine if an execution plan needs to be forced. An execution plan may be forced if there has been some form of regression in the statement being executed. This powerful feature makes life easier for the upwardly mobile DBAs. If we can understand When, how and why the execution plan regressed – we can use the when and why, to determine how to rectify the situation before un-forcing a forced execution plan.
Like all New and Improved Features, it is critical to identify possible untoward outcomes by testing in a non-production environment, then get consensus approval from all stake holders for those changes to be implemented on production systems.