{"id":24520,"date":"2018-06-11T09:00:02","date_gmt":"2018-06-11T16:00:02","guid":{"rendered":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/?p=24520"},"modified":"2024-01-22T22:51:17","modified_gmt":"2024-01-23T06:51:17","slug":"sql-server-automatic-tuning-around-the-world","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2018\/06\/11\/sql-server-automatic-tuning-around-the-world\/","title":{"rendered":"SQL Server Automatic Tuning around the world\u2026"},"content":{"rendered":"

..and in the clouds\u2026<\/strong>When I first saw early builds of SQL Server 2017, one of the features that caught my eye immediately was Automatic Tuning<\/a> with an option called Automatic Plan Correction. SQL Server 2017 was released in October of 2017 right on the heels of one of my favorites releases, SQL Server 2016. In SQL Server 2016, we brought to the product a new feature called Query Store<\/a>. When Query Store is enabled for a database via ALTER DATABASE, the SQL Server engine will start collecting query performance telemetry in memory and system tables in the database. No longer do you need to poll Dynamic Management Views and store them into your own tables. This performance telemetry is collected by the SQL Server engine itself when queries are compiled and executed.<\/p>\n

Like many features we build into SQL Server, you can use T-SQL queries to find out the details of this performance data through a series of catalog views<\/a>. Query store opens up all types of cool performance insights and we have documented some of the key usage scenarios.<\/a> One of these scenarios is called a query plan regression<\/em> (also known as plan choice regressions<\/a>).<\/p>\n

Imagine this scenario. You have a stored procedure that takes a single integer parameter. This integer parameter is used in the WHERE clause of a SELECT statement in the stored procedure. The first time the stored procedure is compiled, the plan for this procedure is inserted into cache based on the value of the first execution of the procedure. And this plan may be a good plan for most users. Now for unexpected reasons, perhaps memory pressure, the plan is evicted from cache. Let\u2019s say a user then executes the procedure through an application but this time with a different integer parameter value. This could result in a different query plan that leads to poor performance. Compiling a plan for a stored procedure based on the parameter value is called parameter sniffing.<\/em> This concept is discussed in our Query Processing Architecture Guide<\/a> in the documentation (which in itself is a cool read). Parameter sniffing is designed to be a good thing, but in some situations where the data in the table associated with the parameter is skewed, a performance problem could occur.<\/p>\n

So, in SQL Server 2016, you can use our reports in SQL Server Management Studio or run queries against Query Store catalog views to see whether a query plan regression has caused a performance problem. Now comes along SQL Server 2017 with some automation. Why not bake into the engine some automation behind the rich telemetry of Query Store? Turns out the folks in our engineering team that own the Query Store feature were already working on these kinds of features in the cloud for Azure SQL Database. Using our cloud-first approach for engineering we started working on these features in Azure, tested and verified their functionality, and then brought them to SQL Server 2017.<\/p>\n

SQL Server 2017 on Windows<\/h2>\n

The approach we took for SQL Server 2017 for Automatic Tuning is recommended then automate<\/em>. There is no better way to show you how this works then with a demo. Watch this video on our SQL YouTube channel<\/a> about how Automatic Tuning works on SQL Server 2017 on Windows. I use the popular WideWorldImporters sample database<\/a> for this demonstration.<\/p>\n