CODEWITHSUNDEEP
sql-serverazureazure-sql-databasesql-query-store
Zain Rizvi
Zain Rizvi

Reputation: 24636

How to tell if a SQL Database has QUERY_STORE enabled?

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')

Upvotes: 12

Views: 16596

Answers (3)

umbio
umbio

Reputation: 11

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

Upvotes: 0

Zikato
Zikato

Reputation: 605

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

Upvotes: 11

David Tansey
David Tansey

Reputation: 6023

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.

Upvotes: 19

Related Questions