Query Store takes a very long time to load duration

I'm running Query Store to make a DB perform better.
It's been working fine until now..
When i try to load the Top durations of last day it took me 26 min to load the screen.

The SQL version im running is:
Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64) Oct 14 2021 00:47:52 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor)

The query pops out in the QS:

/*
This query text was retrieved from showplan XML, and may be truncated.
*/

SELECT TOP (@results_row_count)
    p.query_id query_id,
    q.object_id object_id,
    ISNULL(OBJECT_NAME(q.object_id),'') object_name,
    qt.query_sql_text query_sql_text,
    ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
    SUM(rs.count_executions) count_executions,
    COUNT(distinct p.plan_id) num_plans
FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
    JOIN sys.query_store_query q ON q.query_id = p.query_id
    JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time = 1
ORDER BY total_duration DESC

The execution plan shows a 96% cost of a Clustered Index Scan on the object [plan_persist_runtime_stats].

The following execution plans are from different servers:

• First Execution Plan (completed in 26 mins)

• Second Execution Plan (stopped after 15 mins)

• Third Execution Plan (stopped after 16 mins)

I didn't configure the Query Store via script, just via the properties.
But below my setup:

```
ALTER DATABASE [] SET QUERY_STORE (
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 15),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 60,
SIZE

The core problem is that the T-SQL code that drives the SSMS GUI reports is not efficient. Unfortunately, Microsoft has a long history of including inefficient T-SQL in parts of SSMS. The problem is further compounded by the query store data model and the code that makes up the DMVs. There are unusual performance gotchas in many places. For example, join elimination may not work as expected with some query store DMVs.

I'll back up my criticism by doing a minimal effort rewrite of the query text that you captured. As is, the original code took 6 seconds to execute against one of my production databases:

Note that the following rewrite only works against the database that I developed it against due to the hardcoded filter values for runtime_stats_interval_id:

DECLARE
@results_row_count INT = 100,
@interval_end_time datetimeoffset = '2021-12-08 03:00:00',
@interval_start_time datetimeoffset = '2021-12-07 03:00:00';
 
SELECT *, qt.query_sql_text query_sql_text
FROM
(
       SELECT TOP (@results_row_count)
              p.query_id query_id,
              q.object_id object_id,
              ISNULL(OBJECT_NAME(q.object_id),'') object_name,
              q.query_text_id,
              ROUND(CONVERT(float, SUM(rs.avg_duration*rs.count_executions))*0.001,2) total_duration,
              SUM(rs.count_executions) count_executions,
              COUNT(distinct p.plan_id) num_plans
       FROM sys.query_store_runtime_stats rs
              JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
              JOIN sys.query_store_query q ON q.query_id = p.query_id   
       WHERE NOT (rs.first_execution_time > @interval_end_time OR rs.last_execution_time = 1
       ORDER BY total_duration DESC
) q1
INNER JOIN sys.query_store_query_text qt ON q1.query_text_id = qt.query_text_id;

The new code returns the same results but finishes in about 600 milliseconds:

Of course, the above isn't terribly helpful to you. It's just a sign that there is a defect with SSMS that Microsoft could address with code changes on their end. I can think of the following options for resolving your problem:

• Give up on using some of the SSMS query store GUI reports and run custom T-SQL instead. Some members of the community have written code to do that and they make it freely available. Quickie Store is one such example.

• It is somewhat unlikely, but your performance issue may be caused by parameter sniffing. When you open the query store GUI it immediately runs a report over an hour date range. It may be that SQL Server cached a query plan for that one hour range that doesn't perform well for a 24 hour date range. I've seen this happen before on systems that were significantly busier at certain times of the day. You can test for a parameter sniffing problem by removing the query plan that you identified from the cache.

• There is an answer on this site that solves SSMS query store GUI performance issues by using plan guides.

• You could try opening a support ticket with Microsoft if you have a support contract with them. You could also try leaving feedback about slow performance on the community feedback forum.

On an unrelated note, I noticed in your question that you don't have wait stats collection enabled. I encourage you to enable that if it's possible to do so. I find it to be a powerful tool for troubleshooting why queries timed out. Unfortunately, it does break some of the GUI reports.

StackExchange Database Administrators Q#303563, answer score: 5