E
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, https://dba.stackexchange.com/questions/216485/why-doesnt-join-elimination-work-with-sys-query-store-plan 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_durationrs.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 < @interval_start_time)
AND rs.runtime_stats_interval_id BETWEEN 11534 and 11558 -- code omitted to do this mapping but it isn't hard
GROUP BY p.query_id, q.query_text_id, q.object_id
--HAVING COUNT(distinct p.plan_id) >= 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. https://github.com/erikdarlingdata/DarlingData/tree/master#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 https://dba.stackexchange.com/a/230893/ 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 https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0 .
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 https://www.erikdarlingdata.com/sql-server/t-sql-protip-watch-those-tops-without-order-by/ .
