In SQL Server 2017 is it possible to tell if automatic tuning is switched on? This relates to tuning of the plan. So the database automatically monitors itself and if the plan gets worse it goes back to an old plan
Automatic query tuning requires that Query Store be enabled first, then you can enable Automatic Tuning.
To check Query Store,
select * from sys.database_query_store_options. You're looking for
actual_state_desc to be
Then check the tuning options with
select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN'. You're looking for
actual_state to be set to
You can query one of the DMVs against the database you're interested in -
SELECT * FROM sys.database_automatic_tuning_options
This will return whether or not the
FORCE_LAST_GOOD_PLAN is enabled. If you want to enabled it, then you can run:
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON ); GO
There is a handy blog post over on SQL Shack that goes over enabling Query Store and covers some of the features of it.