[PERFORMANCE] : Improve query performance of the grid content in QueryStore

[rferraton]

Which component(s) does this affect?

• Desktop App
• CLI Tool
• SSMS Extension
• Plan Analysis Rules
• Documentation

Problem Statement

Grid fetch is slow on slow machines because the query do things that could be avoided

Proposed Solution

Move from

Current query

DECLARE @rangeStart DATETIME = getdate()-20;
DECLARE @rangeEnd DATETIME = getdate()-10;

WITH plan_agg AS (
    SELECT
        rs.plan_id,
        SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_us,
        SUM(rs.avg_duration * rs.count_executions) AS total_duration_us,
        SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_reads,
        SUM(rs.avg_logical_io_writes * rs.count_executions) AS total_writes,
        SUM(rs.avg_physical_io_reads * rs.count_executions) AS total_physical_reads,
        SUM(rs.avg_query_max_used_memory * rs.count_executions) AS total_memory_pages,
        SUM(rs.count_executions) AS total_executions,
        MAX(rs.last_execution_time) AS last_execution_time
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_runtime_stats_interval rsi on rs.runtime_stats_interval_id=rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= @rangeStart AND rsi.end_time < @rangeEnd
    GROUP BY rs.plan_id
),
ranked AS (
    SELECT
        p.query_id,
        pa.plan_id,
        pa.total_cpu_us,
        pa.total_duration_us,
        pa.total_reads,
        pa.total_writes,
        pa.total_physical_reads,
        pa.total_memory_pages,
        pa.total_executions,
        pa.last_execution_time,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_cpu_us / pa.total_executions ELSE 0 END AS avg_cpu_us,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_duration_us / pa.total_executions ELSE 0 END AS avg_duration_us,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_reads / pa.total_executions ELSE 0 END AS avg_reads,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_writes / pa.total_executions ELSE 0 END AS avg_writes,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_physical_reads / pa.total_executions ELSE 0 END AS avg_physical_reads,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_memory_pages / pa.total_executions ELSE 0 END AS avg_memory_pages,
        ROW_NUMBER() OVER (PARTITION BY p.query_id ORDER BY pa.total_duration_us DESC) AS rn
    FROM plan_agg pa
    JOIN sys.query_store_plan p ON pa.plan_id = p.plan_id
    WHERE p.query_plan IS NOT NULL
)
SELECT TOP (25)
    r.query_id,
    r.plan_id,
    qt.query_sql_text,
    CAST(p.query_plan AS nvarchar(max)) AS query_plan,
    r.avg_cpu_us,
    r.avg_duration_us,
    r.avg_reads,
    r.avg_writes,
    r.avg_physical_reads,
    r.avg_memory_pages,
    r.total_executions,
    CAST(r.total_cpu_us AS bigint),
    CAST(r.total_duration_us AS bigint),
    CAST(r.total_reads AS bigint),
    CAST(r.total_writes AS bigint),
    CAST(r.total_physical_reads AS bigint),
    CAST(r.total_memory_pages AS bigint),
    r.last_execution_time,
    CONVERT(varchar(18), q.query_hash, 1),
    CONVERT(varchar(18), p.query_plan_hash, 1),
    CASE
        WHEN q.object_id <> 0
        THEN OBJECT_SCHEMA_NAME(q.object_id) + N'.' + OBJECT_NAME(q.object_id)
        ELSE N''
    END
FROM ranked r
JOIN sys.query_store_plan p ON r.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE 1 = 1 AND r.rn = 1
ORDER BY r.total_duration_us DESC
OPTION (LOOP JOIN);

New proposition

DECLARE @rangeStart DATETIME = getdate()-20;
DECLARE @rangeEnd DATETIME = getdate()-19;

drop table if exists #top_plans;

WITH plan_agg AS (
    SELECT
        rs.plan_id,
        SUM(rs.avg_cpu_time * rs.count_executions) AS total_cpu_us,
        SUM(rs.avg_duration * rs.count_executions) AS total_duration_us,
        SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_reads,
        SUM(rs.avg_logical_io_writes * rs.count_executions) AS total_writes,
        SUM(rs.avg_physical_io_reads * rs.count_executions) AS total_physical_reads,
        SUM(rs.avg_query_max_used_memory * rs.count_executions) AS total_memory_pages,
        SUM(rs.count_executions) AS total_executions,
        MAX(rs.last_execution_time) AS last_execution_time
    FROM sys.query_store_runtime_stats rs
    JOIN sys.query_store_runtime_stats_interval rsi on rs.runtime_stats_interval_id=rsi.runtime_stats_interval_id
    WHERE rsi.start_time >= @rangeStart AND rsi.end_time < @rangeEnd
    GROUP BY rs.plan_id
) ,
ranked AS (
    SELECT
        p.query_id,
        pa.plan_id,
        pa.total_cpu_us,
        pa.total_duration_us,
        pa.total_reads,
        pa.total_writes,
        pa.total_physical_reads,
        pa.total_memory_pages,
        pa.total_executions,
        pa.last_execution_time,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_cpu_us / pa.total_executions ELSE 0 END AS avg_cpu_us,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_duration_us / pa.total_executions ELSE 0 END AS avg_duration_us,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_reads / pa.total_executions ELSE 0 END AS avg_reads,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_writes / pa.total_executions ELSE 0 END AS avg_writes,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_physical_reads / pa.total_executions ELSE 0 END AS avg_physical_reads,
        CASE WHEN pa.total_executions > 0
             THEN pa.total_memory_pages / pa.total_executions ELSE 0 END AS avg_memory_pages,
        ROW_NUMBER() OVER (PARTITION BY p.query_id ORDER BY pa.total_duration_us DESC) AS rn
    FROM plan_agg pa
    JOIN sys.query_store_plan p ON pa.plan_id = p.plan_id
    --WHERE p.query_plan IS NOT NULL -- predicate on nvarchar(max) that lead to a slow implicit conversion
)
SELECT TOP (25)
    r.query_id,
    r.plan_id,
    r.avg_cpu_us,
    r.avg_duration_us,
    r.avg_reads,
    r.avg_writes,
    r.avg_physical_reads,
    r.avg_memory_pages,
    r.total_executions,
    CAST(r.total_cpu_us AS bigint) total_cpu_us,
    CAST(r.total_duration_us AS bigint) total_duration_us,
    CAST(r.total_reads AS bigint) total_reads,
    CAST(r.total_writes AS bigint) total_writes,
    CAST(r.total_physical_reads AS bigint) total_physical_reads,
    CAST(r.total_memory_pages AS bigint) total_memory_pages,
    r.last_execution_time
INTO  
#top_plans
FROM ranked r
WHERE 1 = 1 AND r.rn = 1
ORDER BY r.total_duration_us DESC

SELECT 
    topplans.query_id,
    topplans.plan_id,
    qt.query_sql_text,
    CAST(p.query_plan AS nvarchar(max)) AS query_plan,
    topplans.avg_cpu_us,
    topplans.avg_duration_us,
    topplans.avg_reads,
    topplans.avg_writes,
    topplans.avg_physical_reads,
    topplans.avg_memory_pages,
    topplans.total_executions,
    topplans.total_cpu_us,
    topplans.total_duration_us,
    topplans.total_reads,
    topplans.total_writes,
    topplans.total_physical_reads,
    topplans.total_memory_pages,
    topplans.last_execution_time,
    CONVERT(varchar(18), q.query_hash, 1) query_hash,
    CONVERT(varchar(18), p.query_plan_hash, 1) query_plan_hash,
    CASE
        WHEN q.object_id <> 0
        THEN OBJECT_SCHEMA_NAME(q.object_id) + N'.' + OBJECT_NAME(q.object_id)
        ELSE N''
    END objectname 
FROM #top_plans topplans
JOIN sys.query_store_plan p ON topplans.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id;

What was done :

• remove filter WHERE p.query_plan IS NOT NULL than generate a very slow implicit convert
• use a temp table to store top plans then join with query_text and return join for plan
• remove hint LOOP JOIN (3x faster...for some tests)

Results :

• Before 41s
• After : 3s

Use Case

• Slow machines or slow cloud

Alternatives Considered

No response

[rferraton]

i make some tests with real word QS to see if the improvment work correctly

[erikdarlingdata]

Fixed in PR #144 — merged to dev. All three of your proposed changes are in:

1. Removed WHERE p.query_plan IS NOT NULL (the implicit conversion on nvarchar(max) was the big hitter)
2. Two-phase temp table approach — rank with cheap columns first, join text/plan XML only for the TOP N winners
3. Removed OPTION (LOOP JOIN)

Thanks for the excellent analysis and the ready-to-go SQL, @rferraton — this made it straightforward to implement. The 41s → 3s improvement is significant, especially for cloud/lower-spec environments. This will be in the next nightly build.

[rferraton]

ON my side i implement it also , it improve perf on my side but not as expected 7s to 13s not 3s with the app compare to ssms.

May be some other stuff to check on the fetch size of the app.

Whatever , the fetch of waits was also quite "heavy" because it fetch waits for all plan_id and the application make it market. i try to write plan_id in a temp loop over the plan_id and get them using a where exists on the temp table... better
I

So finally i propose to add a progress bar for the QS grid (it need to be improve but you see the idea) :

• if the fetch is over 2s, display the progress bar
• for the user that still have slowness

I also note issues with lateral band of the selected period ...

[erikdarlingdata]

@rferraton I'll work on the query more. There are some tricks I can use from QuickieStore to improve things.

You can add this in if you want, of course. Who knows what someone might run into.

[rferraton]

OK @erikdarlingdata , i let you push the QuickieStore magic inside Performance Studio Metaqueries, you surely have much more experience on the topic than me.

I will retry and if needed, add a progress bar

[erikdarlingdata]

@rferraton I am in favor of the progress bar generally. Despite my best efforts, Query Store remains a nightmare to query efficiently at times.

[rferraton]

@erikdarlingdata : I see some slowlyness with internal TVF ... nothing for help here... May be QS performance improve along versions...

I worked on both the queries and the ux with progress bar and some other stuff.
I propose to PR, you will decide :

• cherry pick the progress bar
• refuse my pr and i will redo the progress bar : no problem

[erikdarlingdata]

@rferraton I think #145 should help as much as it can. You're right about those internal tables.