sys.database_automatic_tuning_options (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2017)yesAzure 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. Refer to ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) for available options.
desired_state smallint Indicates the desired operation mode for Automatic Tuning option, explicitly set by user.
0 = OFF
1 = ON
desired_state_desc nvarchar(60) Textual description of the desired operation mode of Automatic Tuning option.
OFF
ON
actual_state smallint Indicates the operation mode of Automatic Tuning option.
0 = OFF
1 = ON
actual_state_desc nvarchar(60) Textual description of the actual operation mode of Automatic Tuning option.
OFF
ON
reason smallint Indicates why actual and desired states are different.
2 = DISABLED
11 = QUERY_STORE_OFF
12 = QUERY_STORE_READ_ONLY
13 = NOT_SUPPORTED
reason_desc nvarchar(60) Textual description of the reason why actual and desired states are different.
DISABLED = Option is disabled by system
QUERY_STORE_OFF = Query Store is turned off
QUERY_STORE_READ_ONLY = Query Store is in read-only mode
NOT_SUPPORTED = Available only in SQL Server Enterprise edition

Permissions

Requires the VIEW DATABASE STATE permission.

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)