| title | sys.database_automatic_tuning_options (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| description | Learn how to view automatic tuning options on a SQL Database | ||||
| ms.custom | |||||
| ms.date | 07/20/2017 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, sql-database | ||||
| ms.reviewer | |||||
| ms.technology | system-objects | ||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | 16b47d55-8019-41ff-ad34-1e0112178067 | ||||
| author | jovanpop-msft | ||||
| ms.author | jovanpop | ||||
| monikerRange | =azuresqldb-current||>=sql-server-2017||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDEtsql-appliesto-ss2017-asdb-xxxx-xxx-md]
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 [!INCLUDEssNoVersion] Enterprise edition |
Requires the VIEW DATABASE STATE permission.
Automatic Tuning
ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
sys.dm_db_tuning_recommendations (Transact-SQL)