SQL Server – How to View Query Store Settings for a Database

azure-sql-databasequery-storesql server

I see that you can use notion like the following to set the query store configuration:

ALTER DATABASE <database name>   
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

But how can I check to see what the current value for SIZE_BASED_CLEANUP_MODE is?

Best Answer

This can be found (near the end) in the sys.database_query_store_options DMV. 

SELECT size_based_cleanup_mode, size_based_cleanup_mode_desc
FROM sys.database_query_store_options
Related Question