Skip to content

sp_QuickieStore: Invalid sort orders that looks like waits fail at runtime #601

@ReeceGoding

Description

@ReeceGoding

Version of the script
Current dev branch.

What is the current behavior?
EXEC sp_QuickieStore @sort_order = 'io waits'; does this

The sort order (io waits) you chose is so out of this world that I'm using cpu instead
current dynamic activity
error while selecting final results with @expert mode = 0 and format_output = 1
current dynamic sql:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
x.*
FROM
(
SELECT
source = 'runtime_stats',
database_name = DB_NAME(qsrs.database_id),
qsp.query_id,
qsrs.plan_id,
qsp.all_plan_ids,
qsrs.execution_type_desc,
qsq.object_name,
qsqt.query_sql_text,
query_plan =
CASE
WHEN TRY_CAST(qsp.query_plan AS xml) IS NOT NULL
THEN TRY_CAST(qsp.query_plan AS xml)
WHEN TRY_CAST(qsp.query_plan AS xml) IS NULL
THEN
(
SELECT
[processing-instruction(query_plan)] =
N'-- ' + NCHAR(13) + NCHAR(10) +
N'-- This is a huge query plan.' + NCHAR(13) + NCHAR(10) +
N'-- Remove the headers and footers, save it as a .sqlplan file, and re-open it.' + NCHAR(13) + NCHAR(10) +
NCHAR(13) + NCHAR(10) +
REPLACE(qsp.query_plan, N'<RelOp', NCHAR(13) + NCHAR(10) + N'<RelOp') +
NCHAR(13) + NCHAR(10) COLLATE Latin1_General_Bin2
FOR XML
PATH(N''),
TYPE
)
END,
qsp.compatibility_level,

                    force_failure_count = qsp.force_failure_count,
                    last_force_failure_reason_desc = qsp.last_force_failure_reason_desc,
                    has_query_feedback = CASE WHEN EXISTS (SELECT 1/0 FROM #query_store_plan_feedback AS qspf WHERE qspf.plan_id = qsp.plan_id) THEN 'Yes' ELSE 'No' END,
                    has_query_store_hints = CASE WHEN EXISTS (SELECT 1/0 FROM #query_store_query_hints AS qsqh WHERE qsqh.query_id = qsp.query_id) THEN 'Yes' ELSE 'No' END,
                    has_plan_variants = CASE WHEN EXISTS (SELECT ...

If the current behavior is a bug, please provide the steps to reproduce.
EXEC sp_QuickieStore @sort_order = 'io waits';

What is the expected behavior?
Properly default to CPU.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Pretty sure it worked on 5.1.

IMPORTANT: If you're going to contribute code, please read the contributing guide first.
https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md
Sure.

Metadata

Metadata

Assignees

Labels

bugSomething isn't workingsp_QuickieStoreFor the loving of Query Store

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions