-
Notifications
You must be signed in to change notification settings - Fork 182
Description
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 = 3However, 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.