Exploring Automatic Tuning Options in SQL Server

Automatic tuning in SQL Server is a powerful feature designed to help database administrators (DBAs) maintain optimal performance without the need for constant manual intervention. By leveraging built-in intelligence, SQL Server can automatically identify and resolve performance issues, making it an invaluable tool for both experienced and novice DBAs. In this blog post, we’ll delve into what automatic tuning options are, the SQL Server versions that support them, and how to use a simple query to check your database’s automatic tuning status.

What Are Automatic Tuning Options?

Automatic tuning options in SQL Server provide mechanisms for continuous performance improvement. The key features include:

  • Automatic Plan Correction: Automatically identifies and fixes query plan performance issues.
  • Automatic Index Management: Creates and drops indexes based on usage patterns and performance metrics.
  • Force Last Good Plan: Reverts to the last known good execution plan if a new plan regresses performance.

Supported SQL Server Versions

Automatic tuning is supported in SQL Server versions starting from SQL Server 2017. This feature is also available in Azure SQL Database and Azure SQL Managed Instance. For on-premises SQL Server, it’s crucial to ensure you’re running at least SQL Server 2017 to take advantage of these capabilities.

Checking Automatic Tuning Options

To check the automatic tuning options for your database, you can use the following query:

SELECT * FROM sys.database_automatic_tuning_options;

This query retrieves the current state and configuration of automatic tuning options for your database. Each option will have an associated state that can be either “DEFAULT”, “OFF”, or “ON”. Here’s a breakdown of what these states mean:

  • DEFAULT: The setting follows the instance-level configuration.
  • OFF: The feature is disabled.
  • ON: The feature is enabled and active.

How to Use the Query

  1. Connect to Your SQL Server Instance: Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
  2. Open a New Query Window: Navigate to the database you want to check and open a new query window.
  3. Run the Query: Copy and paste the query above into the query window and execute it.
  4. Review the Results: The result set will display various automatic tuning options along with their current state.

Here’s an example output:

option_nameactual_statedesired_statereasontime_of_last_modified
FORCE_LAST_GOOD_PLANONONMANUAL2023-06-10 14:23:15
CREATE_INDEXDEFAULTDEFAULT NULL
DROP_INDEXDEFAULTDEFAULT NULL
FORCE_QUERY_EXECUTION_PLANOFFOFFMANUAL2023-01-25 10:45:12

Enabling or Disabling Automatic Tuning Options

To change the state of an automatic tuning option, you can use the ALTER DATABASE command. For example, to enable the “FORCE_LAST_GOOD_PLAN” option, you would use:

ALTER DATABASE [YourDatabaseName] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Similarly, to disable it, you would set it to OFF:

ALTER DATABASE [YourDatabaseName]SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );

Automatic tuning in SQL Server is a feature that can significantly streamline the process of maintaining database performance. By understanding and utilizing automatic tuning options, you can ensure your SQL Server environment runs smoothly with minimal manual intervention. Be sure to check your current configuration using the provided query and adjust the settings as needed to align with your performance goals.

For comprehensive SQL Server management and support, consider Stedman Solutions’ managed services. With extensive SQL Server experience, we can help optimize your databases and ensure they are always running at peak performance. Visit Stedman Solutions and explore how we can assist with your SQL Server needs. Additionally, don’t forget to try our Database Health Monitor for continuous monitoring and alerting to keep your SQL Server environment in top shape.

Contact Info

Stedman Solutions, LLC.
PO Box 3175
Ferndale WA 98248

Phone: (360)610-7833

Similar Posts