How to tell if a SQL Database has QUERY_STORE enabled?

Question

How can I tell if a Azure SQL Database has QUERY_STORE turned on?

You enable it with this command:

ALTER DATABASE <database_name> SET QUERY_STORE = ON;

I figure it should be simple to check the database for this, but I have not found the trick.

FYI, I tried this command on a database that had it enabled, but the command just returned null:

SELECT DATABASEPROPERTYEX ('<database_name>', 'QUERY_STORE')

Alvinaalvine

Answer 1

This DMV sys.database_query_store_options should allow you to determine if QUERY_STORE is enabled:

SELECT  desired_state_desc ,
        actual_state_desc ,
        readonly_reason, 
        current_storage_size_mb , 
        max_storage_size_mb ,
        max_plans_per_query 
FROM    sys.database_query_store_options ;

Description of Actual_state_Desc states :

OFF (0)

-Not Enabled

READ_ONLY (1)

Query Store may operate in read-only mode even if read-write was specified by the user. For example, that might happen if the database is in read-only mode or if Query Store size exceeded the quota

READ_WRITE (2)

Query store is on and it is capturing all queries

ERROR (3)

Extremely rarely, Query Store can end up in ERROR state because of internal errors. In case of memory corruption, Query Store can be recovered by requesting READ_WRITE mode explicitly, using the ALTER DATABASE SET QUERY_STORE statement. In case of corruption on the disk, data must be cleared before READ_WRITE mode is requested explicitly.

Eloyelreath

Thanks! Do you know what the difference is between desired_state_desc and actual_state_desc? Is there any scenario where the two columns would get stuck with different values? (e.g. we say we want to enable QUERY_STORE, but for some reason the DB is not able to fulfill the request) – Alvinaalvine

You are welcome -- #SOReadyToHelp. The only thing I can find concerning actual vs. desired says the following, in reference to column actual_state (which correlates with actual_state_desc) : "In some cases Query Store may operate in read-only mode even if read-write was specified by the user. For example that might happen if the database is in read-only mode or if Query Store size exceeded the quota." MSDN Link – Eloyelreath

Except for the model database. I find it odd that you can enable QS in model and it works. IOW new databases will automatically be created with QS enabled, and it shows up as enabled in the sys.databases view for model, however sys.query_store_options returns no rows for model, – Narcoanalysis

Answer 2

David's answer shows if the Query store is enabled for the current database - so you would need to loop through them.

This query shows if the Query store is enabled for all databases (but doesn't show any details).

SELECT 
    d.name,
    d.is_query_store_on
FROM sys.databases AS d 

Schedule

Answer 3

If you have to monitor a lot of databases for querystore and automatic tuning the code below may help. It checks all the databases and return a list with the database that worth attention (query store off/readonly and automatic tuning off). Works on SQLServer 2017+

drop table if exists #t

SELECT 
    name, 
    is_query_store_on,
    CAST(NULL AS nvarchar(255)) AS desired_state_desc,
    CAST(NULL AS nvarchar(255)) AS actual_state_desc,
    CAST(NULL AS nvarchar(255)) AS readonly_reason,
    CAST(NULL AS nvarchar(255)) AS autotuning_desired_state_desc,
    CAST(NULL AS nvarchar(255)) AS autotuning_actual_state_desc,
    CAST(NULL AS INT) AS current_storage_size_mb,
    CAST(NULL AS nvarchar(255)) AS command
INTO #t
FROM sys.databases 
where
    database_id>4;  -- only user databases
    
    EXEC sp_MSforeachdb N'USE [?];

                            UPDATE t SET 
                                desired_state_desc = qs.desired_state_desc,
                                actual_state_desc = qs.actual_state_desc,
                                readonly_reason = qs.readonly_reason,
                                current_storage_size_mb = qs.current_storage_size_mb
                            FROM #t AS t
                            CROSS APPLY sys.database_query_store_options AS qs
                            WHERE 
                                t.is_query_store_on=1
                                AND t.name=''?'';

                            UPDATE t SET 
                                autotuning_desired_state_desc = at.desired_state_desc,
                                autotuning_actual_state_desc = at.actual_state_desc
                            FROM #t AS t
                            CROSS APPLY sys.database_automatic_tuning_options AS at
                            WHERE 
                                t.is_query_store_on=1
                                AND t.name=''?'';


                            UPDATE #t set command = ''ALTER DATABASE [?] SET QUERY_STORE = ON;'' 
                                WHERE 
                                name =''?''
                                AND (
                                    is_query_store_on=0
                                    OR actual_state_desc<>desired_state_desc
                                )

                            UPDATE #t set command = ''ALTER DATABASE [?] SET AUTOMATIC_TUNING(FORCE_LAST_GOOD_PLAN=ON);'' 
                                WHERE 
                                name =''?''
                                AND (
                                    is_query_store_on=1
                                    AND autotuning_desired_state_desc<>autotuning_actual_state_desc
                                )
                        ';

select * from #t 
where command is not null
order by name asc

Duffer