sys.database_automatic_tuning_options (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2017)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns the Automatic Tuning options for this database.

Column name Data type Description
name nvarchar(128) The name of the automatic tuning option, for example, FORCE_LAST_GOOD_PLAN
desired_state smallint Indicates the desired operation mode for Automatic Tuning option, explicitly set by user.
desired_state_desc nvarchar(60) Textual description of the desired operation mode of Automatic Tuning option:
OFF (0)
ON (1)
actual_state smallint Indicates the operation mode of Automatic Tuning option.
actual_state_desc nvarchar(60) Textual description of the actual operation mode of Automatic Tuning option.
OFF (0)
ON (1)
reason smallint Indicates why actual and desired states are different.
reason_desc nvarchar(60) Textual description of the reason why actual and desired states are different.

Permissions

Requires the VIEW DATABASE STATE permission.

Remarks

The values in reason and reason_desc columns might be:

reason reason_desc Description
2 DISABLED Option is disabled by system.
11 QUERY_STORE_OFF Query Store is turned off.
12 QUERY_STORE_READ_ONLY Query Store is in read-only mode.
13 NOT_SUPPORTED Available only in Enterprise edition of SQL Server.

See Also

Automatic Tuning
ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
sys.dm_db_tuning_recommendations (Transact-SQL)