sys-database-automatic-tuning-options-transact-sql.md

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-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	database_automatic_tuning_options_tsql database_automatic_tuning_options sys.database_automatic_tuning_options_tsql sys.database_automatic_tuning_options
dev_langs	TSQL
helpviewer_keywords	database_automatic_tuning_options catalog view sys.database_automatic_tuning_options catalog view
ms.assetid	16b47d55-8019-41ff-ad34-1e0112178067
caps.latest.revision	24
author	jovanpop-msft
ms.author	jovanpop
manager	jhubbard

sys.database_automatic_tuning_options (Transact-SQL)

[!INCLUDEtsql-appliesto-ssvNxt-asdb-xxxx-xxx]

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)