Search code examples
sql-server-2017sql-query-store

Query Store is configured but none of my queries under any load show up

SQL Server 2017 Enterprise Query Store is showing no data at all but shows READ_ONLY as the actual mode

The one similar question in this forum has an answer that doesn't apply - none of the exclusions are present. I ran:

GO
ALTER DATABASE [MyDB] SET QUERY_STORE (OPERATION_MODE = READ_ONLY, INTERVAL_LENGTH_MINUTES = 5, QUERY_CAPTURE_MODE = AUTO)
GO

I also ran all these, having referenced the link below, DB context is MyDB:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-2017

ALTER DATABASE MyDB SET QUERY_STORE = ON;

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,   
    max_storage_size_mb, readonly_reason, interval_length_minutes,   
    stale_query_threshold_days, size_based_cleanup_mode_desc,   
    query_capture_mode_desc  
FROM sys.database_query_store_options; 

ALTER DATABASE MyDB SET QUERY_STORE CLEAR; 

-- Run together...
ALTER DATABASE MyDB SET QUERY_STORE = OFF;
GO
EXEC sp_query_store_consistency_check
GO
ALTER DATABASE MyDB SET QUERY_STORE = ON;
GO

No issues found. The SELECT returns matching Actual and Desired states.

I am a sysadmin role member, who actually sets up all 30+ production servers, and this is the only miscreant.

The server is under heavy load and I need internal-eyes on it, in addition to Solarwinds DPA. I've also run sp_blitzquerystore but it returns an empty rowset from the top query, and just the two priority 255 rows from the 2nd.

What on earth did I do wrong? Any clues, anyone, please?

Solution

  • I know this is an old post but for those who come here looking for answers: I do see you ran the query with OPERATION_MODE = READ_ONLY. This would put it into a read-only mode - a mode in which it only reads what is stored in the query store without collecting any additional information. There will be no information shown if the query store has never been in READ_WRITE mode.

    If it has been in READ_WRITE mode before and you are still not seeing anything, it is possible that the heavy load on the server is pushing query plans out of the cache.