SQL Server Automatic Tuning around the world…
..and in the clouds…When I first saw early builds of SQL Server 2017, one of the features that caught my eye immediately was Automatic Tuning 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. 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.
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. Query store opens up all types of cool performance insights and we have documented some of the key usage scenarios. One of these scenarios is called a query plan regression (also known as plan choice regressions).
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’s 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. This concept is discussed in our Query Processing Architecture Guide 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.
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.
SQL Server 2017 on Windows
The approach we took for SQL Server 2017 for Automatic Tuning is recommended then automate. There is no better way to show you how this works then with a demo. Watch this video on our SQL YouTube channel about how Automatic Tuning works on SQL Server 2017 on Windows. I use the popular WideWorldImporters sample database for this demonstration.
Want to run the demo yourself? Download it from my GitHub repo.
Almost everyone I’ve shown this demo to has been amazed. My recommendation to use this feature is the following:
- Enable Query Store and configure it to your needs. See our documentation on best practices.
- Monitor any recommendations we have for you by examining the Dynamic Management View (DMV), dm_db_tuning_recommendations.
- If you are comfortable with our recommendations, experiment with turning on Automatic Plan Correction using this T-SQL syntax.
ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
- Whether you just look at recommendations or use automation, I always recommend you find the cause of the query plan regression problem and take more long-term corrective action.
SQL Server 2017 on Linux
One of the key points I’ve been making to our customers about SQL Server on Linux is that the core database engine is the same as on Windows. Since Automatic Tuning is built into the core database engine, this feature works exactly the same on SQL Server on Linux.
As you watched my demo of Automatic Tuning for SQL Server on Windows, I used the Windows Performance Monitor to show the workload performance when automatic tuning corrects a query plan regression problem. But what about Linux? Windows Performance Monitor will not work with that platform.
Turns out I have some smart people working with me at Microsoft. I got help from Pedro Lopes from our Tiger Team and the engineering team who built SQL Operations Studio to show automatic tuning using that tool.
- Pedro helped me build scripts that would query the DMV, sys.dm_os_performance_counters, which runs on SQL Server on Linux as it does on Windows (because it is built into the core engine). These scripts store results from this DMV in a temp table, and then query the temp table.
- The folks at SQL Operations Studio showed me how to collect the result set from the temp table in a manner that would allow me to show the data in a Time Series chart in the tool. Kind of like a static perfmon chart of SQL Server counters.
The result is the same demo for Automatic Tuning I did for SQL Server on Windows but run against SQL Server on Linux. How did I get the WideWorldImporters sample database to work against Linux for this demo? I just downloaded it from the web into my Linux server and restored it!
The following is an example of the chart from SQL Operations Studio after Automatic Tuning corrected a query plan regression problem from the demo:
Check out this demo of Automatic Tuning on SQL Server on Linux from this video on our SQL Server YouTube channel.
You can grab the demo scripts here.
Azure SQL Database Managed Instance
I’ve now tuned the world of SQL Server instances on Windows and Linux. Why not tune the cloud as well? I have been speaking at many customer events boasting that we have a great consistency story with SQL Server including the cloud. This story includes features, functionality, tools, and the T-SQL language across SQL Server and our Azure Data Services. One of these services that was just announced is Azure SQL Database Managed Instance. One of the promises about this service is that I can just take a database backup from SQL Server and restore it to Azure (can you see a pattern here for compatibility? Backup, Restore, and done!).
A quick search on the web led me to this article. Seems pretty simple. Watch the video to see how I restored my WideWorldImporters backup and then ran the same Automatic Tuning demo as I did for SQL Server on Linux.
Automatic Tuning in the real world
Does this feature really make a difference? Do any customers use this? I recently heard from one of the leading SQL Community leaders, Tracy Boggiano from Channel Advisor, on the topic. “…auto plan correction in query store is awesome we are seeing a 20% reduction on CPU utilization on 98% of our servers”. Automatic Tuning is not just a shiny new feature. It works!
I think I accomplished my mission. I wanted to see how Automatic Tuning works with SQL Server everywhere it runs. And because of our great compatibility story I was able to do this with SQL Server on Windows, Linux, and in the clouds with Azure SQL Database Managed Instance all with the same database backup.
If you want to read more about Automatic Tuning check out our documentation.
Subscribe to our new SQL Server YouTube channel to keep up with more great videos and demos about SQL Server.
