Skip to content

sp_QuickieStore: @execution_count seems to care about plans, not queries #576

@ReeceGoding

Description

@ReeceGoding

Version of the script
This has probably always been a problem. I suspected that it was caused by fixing #526 , but I have found the same issue with an old version. I also suspect that the PR for fixing that issue broke the queries in the if/else chain that is shared with @having_clause's only user, although I can't prove that they're broken until I know what @execution_count is supposed to do.

What is the current behavior?
The documentation describes @execution_count as "the minimum number of executions a query must have". However, suppose that I run this 5 times and remove a zero from @foo each time.

DECLARE @Foo INT = 110000

SELECT *
FROM dbo.Posts
JOIN PostTypes
ON Posts.PostTypeId = PostTypes.Id
WHERE Posts.Id < @Foo
OPTION (RECOMPILE)

On my machine, I get two rows back from this

EXEC sp_QuickieStore @sort_order  = 'executions', @query_text_search = 'PostTypes'

They both show the same query ids, but each has a different plan id. The first has 3 executions and the second has 2. As documented, the following should return both rows because the query has five executions.

EXEC sp_QuickieStore @sort_order  = 'executions', @query_text_search = 'PostTypes', @execution_count = 3

However, it only returns the row with three executions. It seems that what @execution_count actually checks for is the execution count of the plan rather than the query. We start with

IF @execution_count IS NOT NULL
BEGIN
    SELECT
        @having_clause += N'HAVING
        SUM(qsrs.count_executions) >= @execution_count';
END;

and then use the HAVING clause for this much later

BEGIN
    SELECT
        @sql += N'
    SELECT TOP (@top)
        qsrs.plan_id
    FROM ' + @database_name_quoted + N'.sys.query_store_runtime_stats AS qsrs
    WHERE 1 = 1
    ' + @where_clause
      + N'
    GROUP BY
        qsrs.plan_id
    ' + @having_clause
      + N'
    ORDER BY
        MAX(' +
    CASE @sort_order
         WHEN 'cpu' THEN N'qsrs.avg_cpu_time'
         WHEN 'logical reads' THEN N'qsrs.avg_logical_io_reads'
         WHEN 'physical reads' THEN N'qsrs.avg_physical_io_reads'
         WHEN 'writes' THEN N'qsrs.avg_logical_io_writes'
         WHEN 'duration' THEN N'qsrs.avg_duration'
         WHEN 'memory' THEN N'qsrs.avg_query_max_used_memory'
         WHEN 'tempdb' THEN CASE WHEN @new = 1 THEN N'qsrs.avg_tempdb_space_used' ELSE N'qsrs.avg_cpu_time' END
         WHEN 'executions' THEN N'qsrs.count_executions'
         WHEN 'recent' THEN N'qsrs.last_execution_time'
         WHEN 'rows' THEN N'qsrs.avg_rowcount'
         ELSE N'qsrs.avg_cpu_time'
    END +
    N') DESC
    OPTION(RECOMPILE, OPTIMIZE FOR (@top = 9223372036854775807));' + @nc10;
END;

If the current behavior is a bug, please provide the steps to reproduce.
As above.

What is the expected behavior?
Either @execution_count should work as documented and care about queries in some way (query id or query hash?) or we should document what it actually does, which seems to be care about plan ids.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
See above.

IMPORTANT: If you're going to contribute code, please read the contributing guide first.
https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md
I can't. What @execution_count is supposed to do is Erik's decision.

Metadata

Metadata

Labels

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions