Poor performance on [collect].[query_stats] and indexes that helped.

[MegaCalkins]

Hi,

I noticed some query performance issues on the [collect].[query_stats] table. After reviewing the existing indexes and the missing index recommendations, I added the following two nonclustered indexes:

CREATE NONCLUSTERED INDEX [IX_query_stats_object_lookup]
ON [collect].[query_stats]
(
    [schema_name],
    [object_name],
    [object_type],
    [collection_time] DESC
)
INCLUDE
(
    [sql_handle]
);

CREATE NONCLUSTERED INDEX [IX_query_stats_execution_count_delta]
ON [collect].[query_stats]
(
    [execution_count_delta],
    [collection_time] DESC
)
INCLUDE
(
    [server_start_time],
    [sql_handle],
    [statement_start_offset],
    [statement_end_offset],
    [plan_handle],
    [creation_time],
    [last_execution_time],
    [execution_count],
    [total_worker_time],
    [total_physical_reads],
    [total_logical_writes],
    [total_logical_reads],
    [total_elapsed_time]
);

After adding these indexes, the affected queries performed noticeably better in my environment.

Based on this, I suggest considering these indexes for the default schema, or at least documenting them as recommended indexes for larger query_stats tables.

[erikdarlingdata]

Thanks for the report — glad those indexes helped in your environment.

Before adding anything to the default schema (or recommending them in docs), I'd like to see what's actually driving the cost so the fix matches the problem. Could you share:

1. The slow queries themselves — which view or stored procedure is performing poorly? If it's the Dashboard, knowing which screen/report would help.
2. Actual execution plans (not estimated) showing the bad behavior — ideally pre-index so I can see what access path the optimizer is picking and where time is going.
3. Plans after your two indexes are in place, for comparison.
4. Rough table size — collect.query_stats row count and how long you've been retaining data. That sets the bar for how much write amplification a wide INCLUDE list is worth.

A couple of things I want to verify with the plans:

• For the object-lookup case, whether a filtered version (WHERE object_type <> N'\''STATEMENT'\'') would give you the same benefit at a fraction of the size, since the majority of rows are ad-hoc statements with NULL schema_name/object_name.
• For the execution_count_delta index, whether the leading column is actually being seeked on, or whether the optimizer just liked having a covering index for the SELECT list. The INCLUDE list is wide enough that I want to make sure we'''re solving the real problem before adding it as a default.

If you can drop the plans as .sqlplan attachments (or paste into Paste The Plan) that'''d be perfect.

[MegaCalkins]

Hi.

I'll try to gather as much information as possible, but it might take some time, so I'll provide all the data and information I have so far.

I have had huge performance problems with reading from PerformanceMaonitor.mdf during running job PerformanceMonitor - Collection

After investigating the matter, I determined that the cause of the problem is the [collect].[calculate_deltas] procedure - more specifcally the following code block:

Query Stats Delta Calculation
        */
        ELSE IF @table_name = N'query_stats'
        BEGIN
            SET @sql = N'
            WITH 
                current_collection AS
            (
                SELECT
                    qs.*,
                    row_number = 
                        ROW_NUMBER() OVER 
                        (
                            PARTITION BY 
                                qs.sql_handle,
                                qs.statement_start_offset,
                                qs.statement_end_offset,
                                qs.plan_handle
                            ORDER BY 
                                qs.collection_time DESC
                        )
                FROM collect.query_stats AS qs
                WHERE qs.execution_count_delta IS NULL
            ), [...]

I have looked closer indexes on table [PerformanceMonitor].[collect].[query_stats] using procedure from BrentOzar with generated AI prompt : EXEC sp_BlitzIndex @DatabaseName = 'PerformanceMonitor', @SchemaName = 'collect', @TableName = 'query_stats' , @AI = 2
I have added 2 above indexes and things speed up and huge reads disappeard.

2 new indexes are small compared to clustered index what matters in the data reading proces.

I have only estimated plans (before, after) in this moment but I think they are pretty convincing:

Before - scans on PK (in my case 12 GB)
https://www.brentozar.com/pastetheplan/?id=HbYw3Eq07i

After - scans on much smaller index (in my case 411 MB)
https://www.brentozar.com/pastetheplan/?id=6TrtphMpY6

I hope this preliminary information will be helpful for any future work.

Greetings from Poland
Damian Cała

[erikdarlingdata]

Thanks Damian, this is really helpful — and it points at a real gap.

The bottleneck you found is on the collection side, in collect.calculate_deltas. The two CTEs in the query_stats branch filter on execution_count_delta IS NULL (current collection) and execution_count_delta IS NOT NULL (previous collection), and there's currently no index that supports either predicate — so both force a full scan of the clustered index. On your instance that means dragging the compressed query_text/query_plan_text LOB columns through the plan even though the delta math never touches them. That's why your covering index is ~411 MB vs the 12 GB PK: it's effectively a LOB-free copy of the rows.

One note so we don't cross wires: the index work that shipped in 2.8.0 (#835) added IX_query_stats_hash_lookup on (query_hash, database_name, collection_time DESC), but that was to kill an Eager Index Spool in the Dashboard grid lookups — it leads on query_hash and does nothing for this execution_count_delta predicate. So your finding is a separate, currently-unaddressed problem, not a duplicate of that one.

Before I settle on the default-schema shape, a couple of thoughts:

• For the current_collection side (IS NULL), a filtered index WHERE execution_count_delta IS NULL should give the same benefit at a tiny fraction of the size — at any moment only the newest unprocessed batch is NULL, and rows drop out of the index as the UPDATE fills the delta in. That's a much cheaper default than a full-width covering index.
• The previous_collection side (IS NOT NULL) is nearly the whole table, so a filtered index won't help there; the win is purely the narrower, no-LOB rows. I'd like an actual plan to confirm whether that half is a meaningful chunk of your reads or whether the IS NULL side dominates.
• Your IX_query_stats_object_lookup (leading on schema_name/object_name/object_type) doesn't map to calculate_deltas at all — those columns never appear in that procedure. That one looks like a separate missing-index suggestion for some other query. If you can tell which screen/report it came from, I'll look at it independently.

If/when you can grab actual plans (even just for one collection cycle), that'll let me confirm the IS NULL seek and size the filtered index properly. But the core finding is solid and I'll get a fix into an upgrade folder.

Thanks again — and greetings back to Poland.