0

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

2

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 READ_WRITE.

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 ON.

  • select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN' returned actual_state of 0. Which confirmed as I thought this was switched off – SQLMIKE Jan 27 at 15:50
1

You can query one of the DMVs against the database you're interested in - sys.database_automatic_tuning_options.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.