SQL Server 2017 has finally been released, and one of the most anticipated features is Automatic Tuning. In this blog post, we will dive into this exciting new feature and discuss its potential impact on performance optimization.

Automatic Tuning is a combination of two powerful capabilities: Automatic Plan Correction and Automatic Index Management. Let’s take a closer look at each of these features.

Automatic Plan Correction

SQL Server evaluates multiple execution plans before selecting the most optimal one for query execution. However, there are instances where the chosen plan may not be the best, resulting in poor query performance. This is known as query plan regression.

To address this issue, SQL Server applies Automatic Plan Correction. It identifies the historically best-performing execution plan for the query and automatically switches to it, improving overall performance. This feature saves DBAs from manually identifying and correcting plan regressions, allowing them to focus on other critical tasks.

Automatic Index Management

Previously available in Azure SQL Database, Automatic Index Management is now included in SQL Server 2017. This feature leverages the SQL Server engine’s understanding of workload patterns to create optimal indexes for queries. It also identifies duplicate and unused indexes, automatically dropping them to improve performance.

Imagine the scenario where a customer creates every single missing index and drops unused indexes based on a script. While this may seem like a proactive approach, it can lead to performance problems. Automatic Index Management eliminates the need for manual index management, ensuring that only the necessary indexes are maintained.

Benefits and Considerations

Automatic Tuning is an exciting addition to SQL Server, but it’s important to set realistic expectations. While it can significantly improve performance by addressing plan regressions and optimizing indexes, it does not eliminate the need for SQL Server DBAs.

SQL Server Performance Experts will still play a crucial role in monitoring and fine-tuning the database environment. Automatic Tuning allows them to focus on other high-impact areas, knowing that plan regressions and index management are being handled automatically.

It’s worth noting that upgrading to SQL Server 2017 and enabling Automatic Tuning does not guarantee the resolution of all performance issues. A holistic approach to performance optimization is still necessary, considering factors such as hardware, query design, and database configuration.

Conclusion

Automatic Tuning in SQL Server 2017 is a game-changer for performance optimization. By automating plan correction and index management, it simplifies the task of maintaining optimal performance. However, it should be seen as a valuable tool in the DBA’s arsenal rather than a complete solution.

What are your thoughts on Automatic Tuning? Do you believe it will significantly impact your SQL Server performance? Share your opinions in the comments below!