Bug on execution

[egaultier]

Hi I downloaded your sp script this morming. Wednesday 26th june

@version = '4.7',
@version_date = '20240701';

Sqlserver Os version : Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5029376) - 14.0.3465.1 (X64) Jul 30 2023 15:31:58 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

When I execute the proc, I have this error :

EXEC DBATOOLS.dbo.sp_QuickieStore --@debug = 1, @database_name = 'Example', @sort_order = 'memory', @top = 10

Msg 50000, Level 11, State 1, Procedure DBATOOLS.dbo.sp_QuickieStore, Line 8537 [Batch Start Line 0] error while selecting final results with @expert mode = 0 and format_output = 1

No result return.

I make test on an other instance with different sqlserver version and collation. Same example

Thank you for your help.

Emmanuel

[erikdarlingdata]

@egaultier what is the debug output from the messages tab?

[egaultier]

`Msg 50000, Level 11, State 1, Procedure DBATOOLS.dbo.sp_QuickieStore, Line 8537 [Batch Start Line 0] error while selecting final results with @expert mode = 0 and format_output = 1 offending query:

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, qsp.force_failure_count, qsp.last_force_failure_reason_desc, qsp.plan_forcing_type_desc, w.top_waits, first_execution_time = CASE WHEN @timezone IS NULL THEN DATEADD ( MINUTE, @utc_minutes_original, qsrs.first_execution_time ) WHEN @timezone IS NOT NULL THEN qsrs.first_execution_time AT TIME ZONE @timezone END, ... Msg 235, Level 16, State 0, Line 4 Impossible de convertir une valeur char en money. La valeur char a une syntaxe incorrecte.

Completion time: 2024-06-26T15:24:33.7548459+02:00 `

[egaultier]

If I add the debug parameter.

output on message tab

`Starting analysis for database STW_GALLIANCE_PRD 780

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @query_store_exists = CASE WHEN EXISTS ( SELECT 1/0 FROM [STW_GALLIANCE_PRD].sys.database_query_store_options AS dqso WHERE ( dqso.actual_state = 0 OR dqso.actual_state IS NULL ) ) OR NOT EXISTS ( SELECT 1/0 FROM [STW_GALLIANCE_PRD].sys.database_query_store_options AS dqso ) THEN 0 ELSE 1 END OPTION(RECOMPILE);

1631

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT database_id = @database_id, desired_state_desc, actual_state_desc, readonly_reason = CASE dqso.readonly_reason WHEN 0 THEN 'None' WHEN 2 THEN 'Database in single user mode' WHEN 4 THEN 'Database is in emergency mode' WHEN 8 THEN 'Database is AG secondary' WHEN 65536 THEN 'Reached max size: ' + FORMAT(dqso.current_storage_size_mb, 'N0') + ' of ' + FORMAT(dqso.max_storage_size_mb, 'N0') + '.' WHEN 131072 THEN 'The number of different statements in Query Store has reached the internal memory limit' WHEN 262144 THEN 'Size of in-memory items waiting to be persisted on disk has reached the internal memory limit' WHEN 524288 THEN 'Database has reached disk size limit' ELSE 'WOAH' END, current_storage_size_mb, flush_interval_seconds, interval_length_minutes, max_storage_size_mb, stale_query_threshold_days, max_plans_per_query, query_capture_mode_desc, size_based_cleanup_mode_desc FROM [STW_GALLIANCE_PRD].sys.database_query_store_options AS dqso WHERE ( dqso.desired_state <> 4 OR dqso.readonly_reason <> 8 ) AND ( dqso.desired_state = 1 OR dqso.actual_state IN (1, 3) OR dqso.desired_state <> dqso.actual_state ) OPTION(RECOMPILE);

454

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @query_store_waits_enabled = CASE WHEN EXISTS ( SELECT 1/0 FROM [STW_GALLIANCE_PRD].sys.database_query_store_options AS dqso WHERE dqso.wait_stats_capture_mode = 1 ) THEN 1 ELSE 0 END OPTION(RECOMPILE);

973

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT DISTINCT qsp.plan_id FROM [STW_GALLIANCE_PRD].sys.query_store_plan AS qsp WHERE NOT EXISTS ( SELECT 1/0 FROM [STW_GALLIANCE_PRD].sys.query_store_query AS qsq JOIN [STW_GALLIANCE_PRD].sys.query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id WHERE qsq.query_id = qsp.query_id AND qsqt.query_sql_text NOT LIKE N'ALTER INDEX%' AND qsqt.query_sql_text NOT LIKE N'ALTER TABLE%' AND qsqt.query_sql_text NOT LIKE N'CREATE%INDEX%' AND qsqt.query_sql_text NOT LIKE N'CREATE STATISTICS%' AND qsqt.query_sql_text NOT LIKE N'UPDATE STATISTICS%' AND qsqt.query_sql_text NOT LIKE N'SELECT StatMan%' AND qsqt.query_sql_text NOT LIKE N'DBCC%' AND qsqt.query_sqltext NOT LIKE N'(@[]msparam%' ) OPTION(RECOMPILE);

561

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT TOP (@top) qsrs.plan_id FROM [STW_GALLIANCE_PRD].sys.query_store_runtime_stats AS qsrs WHERE 1 = 1 AND qsrs.last_execution_time >= @start_date AND qsrs.last_execution_time < @end_date AND NOT EXISTS ( SELECT 1/0 FROM #maintenance_plans AS mp WHERE mp.plan_id = qsrs.plan_id ) GROUP BY qsrs.plan_id ORDER BY MAX(qsrs.avg_query_max_used_memory) DESC OPTION(RECOMPILE, OPTIMIZE FOR (@top = 9223372036854775807));

3181

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @database_id, MAX(qsrs.runtime_stats_id), qsrs.plan_id, MAX(qsrs.runtime_stats_interval_id), MAX(qsrs.execution_type_desc), MIN(qsrs.first_execution_time), MAX(qsrs.last_execution_time), SUM(qsrs.count_executions), AVG((qsrs.avg_duration / 1000.)), MAX((qsrs.last_duration / 1000.)), MIN((qsrs.min_duration / 1000.)), MAX((qsrs.max_duration / 1000.)), AVG((qsrs.avg_cpu_time / 1000.)), MAX((qsrs.last_cpu_time / 1000.)), MIN((qsrs.min_cpu_time / 1000.)), MAX((qsrs.max_cpu_time / 1000.)), AVG(((qsrs.avg_logical_io_reads 8.) / 1024.)), MAX(((qsrs.last_logical_io_reads 8.) / 1024.)), MIN(((qsrs.min_logical_io_reads 8.) / 1024.)), MAX(((qsrs.max_logical_io_reads 8.) / 1024.)), AVG(((qsrs.avg_logical_io_writes 8.) / 1024.)), MAX(((qsrs.last_logical_io_writes 8.) / 1024.)), MIN(((qsrs.min_logical_io_writes 8.) / 1024.)), MAX(((qsrs.max_logical_io_writes 8.) / 1024.)), AVG(((qsrs.avg_physical_io_reads 8.) / 1024.)), MAX(((qsrs.last_physical_io_reads 8.) / 1024.)), MIN(((qsrs.min_physical_io_reads 8.) / 1024.)), MAX(((qsrs.max_physical_io_reads 8.) / 1024.)), AVG((qsrs.avg_clr_time / 1000.)), MAX((qsrs.last_clr_time / 1000.)), MIN((qsrs.min_clr_time / 1000.)), MAX((qsrs.max_clr_time / 1000.)), MAX(qsrs.last_dop), MIN(qsrs.min_dop), MAX(qsrs.max_dop), AVG(((qsrs.avg_query_max_used_memory 8.) / 1024.)), MAX(((qsrs.last_query_max_used_memory 8.) / 1024.)), MIN(((qsrs.min_query_max_used_memory 8.) / 1024.)), MAX(((qsrs.max_query_max_used_memory 8.) / 1024.)), AVG(qsrs.avg_rowcount), MAX(qsrs.last_rowcount), MIN(qsrs.min_rowcount), MAX(qsrs.max_rowcount), AVG(((qsrs.avg_num_physical_io_reads 8.) / 1024.)), MAX(((qsrs.last_num_physical_io_reads 8.) / 1024.)), MIN(((qsrs.min_num_physical_io_reads 8.) / 1024.)), MAX(((qsrs.max_num_physical_io_reads 8.) / 1024.)), AVG((qsrs.avg_log_bytes_used / 100000000.)), MAX((qsrs.last_log_bytes_used / 100000000.)), MIN((qsrs.min_log_bytes_used / 100000000.)), MAX((qsrs.max_log_bytes_used / 100000000.)), AVG(((qsrs.avg_tempdb_space_used 8) / 1024.)), MAX(((qsrs.last_tempdb_space_used 8) / 1024.)), MIN(((qsrs.min_tempdb_space_used 8) / 1024.)), MAX(((qsrs.max_tempdb_space_used 8) / 1024.)), context_settings = NULL FROM #distinct_plans AS dp CROSS APPLY ( SELECT TOP (@queries_top) qsrs.* FROM [STW_GALLIANCE_PRD].sys.query_store_runtime_stats AS qsrs WHERE qsrs.plan_id = dp.plan_id AND 1 = 1 AND qsrs.last_execution_time >= @start_date AND qsrs.last_execution_time < @end_date AND NOT EXISTS ( SELECT 1/0 FROM #maintenance_plans AS mp WHERE mp.plan_id = qsrs.plan_id ) ORDER BY qsrs.avg_query_max_used_memory DESC ) AS qsrs GROUP BY qsrs.plan_id OPTION(RECOMPILE, OPTIMIZE FOR (@queries_top = 9223372036854775807));

1614

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @database_id, qsp.plan_id, qsp.query_id, all_plan_ids = STUFF ( ( SELECT DISTINCT ', ' + RTRIM (qsp_plans.plan_id) FROM [STW_GALLIANCE_PRD].sys.query_store_plan AS qsp_plans WHERE qsp_plans.query_id = qsp.query_id FOR XML PATH(''), TYPE ).value('./text()[1]', 'varchar(max)'), 1, 2, '' ), qsp.plan_group_id, qsp.engine_version, qsp.compatibility_level, qsp.query_plan_hash, qsp.query_plan, qsp.is_online_index_plan, qsp.is_trivial_plan, qsp.is_parallel_plan, qsp.is_forced_plan, qsp.is_natively_compiled, qsp.force_failure_count, qsp.last_force_failure_reason_desc, qsp.count_compiles, qsp.initial_compile_start_time, qsp.last_compile_start_time, qsp.last_execution_time, (qsp.avg_compile_duration / 1000.), (qsp.last_compile_duration / 1000.), qsp.plan_forcing_type_desc, NULL, NULL, NULL FROM #query_store_runtime_stats AS qsrs CROSS APPLY ( SELECT TOP (@plans_top) qsp.* FROM [STW_GALLIANCE_PRD].sys.query_store_plan AS qsp WHERE qsp.plan_id = qsrs.plan_id AND qsp.is_online_index_plan = 0 ORDER BY qsp.last_execution_time DESC ) AS qsp WHERE qsrs.database_id = @database_id OPTION(RECOMPILE, OPTIMIZE FOR (@plans_top = 9223372036854775807));

1426

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @database_id, qsq.query_id, qsq.query_text_id, qsq.context_settings_id, qsq.object_id, qsq.batch_sql_handle, qsq.query_hash, qsq.is_internal_query, qsq.query_parameterization_type_desc, qsq.initial_compile_start_time, qsq.last_compile_start_time, qsq.last_execution_time, qsq.last_compile_batch_sql_handle, qsq.last_compile_batch_offset_start, qsq.last_compile_batch_offset_end, qsq.count_compiles, (qsq.avg_compile_duration / 1000.), (qsq.last_compile_duration / 1000.), (qsq.avg_bind_duration / 1000.), (qsq.last_bind_duration / 1000.), (qsq.avg_bind_cpu_time / 1000.), (qsq.last_bind_cpu_time / 1000.), (qsq.avg_optimize_duration / 1000.), (qsq.last_optimize_duration / 1000.), (qsq.avg_optimize_cpu_time / 1000.), (qsq.last_optimize_cpu_time / 1000.), ((qsq.avg_compile_memory_kb 8) / 1024.), ((qsq.last_compile_memory_kb 8) / 1024.), ((qsq.max_compile_memory_kb 8) / 1024.), qsq.is_clouddb_internal_query FROM #query_store_plan AS qsp CROSS APPLY ( SELECT TOP (1) qsq. FROM [STW_GALLIANCE_PRD].sys.query_store_query AS qsq WHERE qsq.query_id = qsp.query_id ORDER BY qsq.last_execution_time DESC ) AS qsq WHERE qsp.database_id = @database_id OPTION(RECOMPILE);

1562

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @database_id, qsqt.query_text_id, query_sql_text = ( SELECT [processing-instruction(query)] = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( qsqt.query_sql_text COLLATE Latin1_General_BIN2, NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'), NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'), NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),NCHAR(0),N'') FOR XML PATH(''), TYPE ), qsqt.statement_sql_handle, qsqt.is_part_of_encrypted_module, qsqt.has_restricted_text FROM #query_store_query AS qsq CROSS APPLY ( SELECT TOP (1) qsqt.* FROM [STW_GALLIANCE_PRD].sys.query_store_query_text AS qsqt WHERE qsqt.query_text_id = qsq.query_text_id ) AS qsqt WHERE qsq.database_id = @database_id OPTION(RECOMPILE);

1544

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @database_id, dqso.desired_state_desc, dqso.actual_state_desc, readonly_reason = CASE dqso.readonly_reason WHEN 0 THEN 'None' WHEN 2 THEN 'Database in single user mode' WHEN 4 THEN 'Database is in emergency mode' WHEN 8 THEN 'Database is AG secondary' WHEN 65536 THEN 'Reached max size: ' + FORMAT(dqso.current_storage_size_mb, 'N0') + ' of ' + FORMAT(dqso.max_storage_size_mb, 'N0') + '.' WHEN 131072 THEN 'The number of different statements in Query Store has reached the internal memory limit' WHEN 262144 THEN 'Size of in-memory items waiting to be persisted on disk has reached the internal memory limit' WHEN 524288 THEN 'Database has reached disk size limit' ELSE 'WOAH' END, dqso.current_storage_size_mb, dqso.flush_interval_seconds, dqso.interval_length_minutes, dqso.max_storage_size_mb, dqso.stale_query_threshold_days, dqso.max_plans_per_query, dqso.query_capture_mode_desc, NULL, NULL, NULL, NULL, dqso.size_based_cleanup_mode_desc, dqso.wait_stats_capture_mode_desc FROM [STW_GALLIANCE_PRD].sys.database_query_store_options AS dqso OPTION(RECOMPILE);

1082

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT @database_id, qsws.plan_id, qsws.wait_category_desc, total_query_wait_time_ms = SUM(qsws.total_query_wait_time_ms), avg_query_wait_time_ms = SUM(qsws.avg_query_wait_time_ms), last_query_wait_time_ms = SUM(qsws.last_query_wait_time_ms), min_query_wait_time_ms = SUM(qsws.min_query_wait_time_ms), max_query_wait_time_ms = SUM(qsws.max_query_wait_time_ms) FROM #query_store_runtime_stats AS qsrs CROSS APPLY ( SELECT TOP (5) qsws.* FROM [STW_GALLIANCE_PRD].sys.query_store_wait_stats AS qsws WHERE qsws.runtime_stats_interval_id = qsrs.runtime_stats_interval_id AND qsws.plan_id = qsrs.plan_id AND qsws.wait_category > 0 AND qsws.min_query_wait_time_ms > 0 ORDER BY qsws.avg_query_wait_time_ms DESC ) AS qsws WHERE qsrs.database_id = @database_id GROUP BY qsws.plan_id, qsws.wait_category_desc HAVING SUM(qsws.min_query_wait_time_ms) > 0. OPTION(RECOMPILE);

8469

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, qsp.force_failure_count, qsp.last_force_failure_reason_desc, qsp.plan_forcing_type_desc, w.top_waits, first_execution_time = CASE WHEN @timezone IS NULL THEN DATEADD ( MINUTE, @utc_minutes_original, qsrs.first_execution_time ) WHEN @timezone IS NOT NULL THEN qsrs.first_execution_time AT TIME ZONE @timezone END, first_execution_time_utc = qsrs.first_execution_time, last_execution_time = CASE WHEN @timezone IS NULL THEN DATEADD ( MINUTE, @utc_minutes_original, qsrs.last_execution_time ) WHEN @timezone IS NOT NULL THEN qsrs.last_execution_time AT TIME ZONE @timezone END, last_execution_time_utc = qsrs.last_execution_time, count_executions = FORMAT(qsrs.count_executions, 'N0'), executions_per_second = FORMAT(qsrs.executions_per_second, 'N0'), avg_duration_ms = FORMAT(qsrs.avg_duration_ms, 'N0'), total_duration_ms = FORMAT(qsrs.total_duration_ms, 'N0'), max_duration_ms = FORMAT(qsrs.max_duration_ms, 'N0'), avg_cpu_time_ms = FORMAT(qsrs.avg_cpu_time_ms, 'N0'), total_cpu_time_ms = FORMAT(qsrs.total_cpu_time_ms, 'N0'), max_cpu_time_ms = FORMAT(qsrs.max_cpu_time_ms, 'N0'), avg_logical_io_reads_mb = FORMAT(qsrs.avg_logical_io_reads_mb, 'N0'), total_logical_io_reads_mb = FORMAT(qsrs.total_logical_io_reads_mb, 'N0'), max_logical_io_reads_mb = FORMAT(qsrs.max_logical_io_reads_mb, 'N0'), avg_logical_io_writes_mb = FORMAT(qsrs.avg_logical_io_writes_mb, 'N0'), total_logical_io_writes_mb = FORMAT(qsrs.total_logical_io_writes_mb, 'N0'), max_logical_io_writes_mb = FORMAT(qsrs.max_logical_io_writes_mb, 'N0'), avg_physical_io_reads_mb = FORMAT(qsrs.avg_physical_io_reads_mb, 'N0'), total_physical_io_reads_mb = FORMAT(qsrs.total_physical_io_reads_mb, 'N0'), max_physical_io_reads_mb = FORMAT(qsrs.max_physical_io_reads_mb, 'N0'), avg_clr_time_ms = FORMAT(qsrs.avg_clr_time_ms, 'N0'), total_clr_time_ms = FORM AT(qsrs.total_clr_time_ms, 'N0'), max_clr_time_ms = FORMAT(qsrs.max_clr_time_ms, 'N0'), min_dop = FORMAT(qsrs.min_dop, 'N0'), max_dop = FORMAT(qsrs.max_dop, 'N0'), avg_query_max_used_memory_mb = FORMAT(qsrs.avg_query_max_used_memory_mb, 'N0'), total_query_max_used_memory_mb = FORMAT(qsrs.total_query_max_used_memory_mb, 'N0'), max_query_max_used_memory_mb = FORMAT(qsrs.max_query_max_used_memory_mb, 'N0'), avg_rowcount = FORMAT(qsrs.avg_rowcount, 'N0'), total_rowcount = FORMAT(qsrs.total_rowcount, 'N0'), max_rowcount = FORMAT(qsrs.max_rowcount, 'N0'), avg_num_physical_io_reads_mb = FORMAT(qsrs.avg_num_physical_io_reads_mb, 'N0'), total_num_physical_io_reads_mb = FORMAT(qsrs.total_num_physical_io_reads_mb, 'N0'), max_num_physical_io_reads_mb = FORMAT(qsrs.max_num_physical_io_reads_mb, 'N0'), avg_log_bytes_used_mb = FORMAT(qsrs.avg_log_bytes_used_mb, 'N0'), total_log_bytes_used_mb = FORMAT(qsrs.total_log_bytes_used_mb, 'N0'), max_log_bytes_used_mb = FORMAT(qsrs.max_log_bytes_used_mb, 'N0'), avg_tempdb_space_used_mb = FORMAT(qsrs.avg_tempdb_space_used_mb, 'N0'), total_tempdb_space_used_mb = FORMAT(qsrs.total_tempdb_space_used_mb, 'N0'), max_tempdb_space_used_mb = FORMAT(qsrs.max_tempdb_space_used_mb, 'N0'), qsrs.context_settings, n = ROW_NUMBER() OVER ( PARTITION BY qsrs.plan_id ORDER BY qsrs.avg_query_max_used_memory_mb DESC ) FROM #query_store_runtime_stats AS qsrs CROSS APPLY ( SELECT x. FROM ( SELECT qsp., pn = ROW_NUMBER() OVER ( PARTITION BY qsp.query_plan_hash ORDER BY qsp.last_execution_time DESC ) FROM #query_store_plan AS qsp WHERE qsp.plan_id = qsrs.plan_id AND qsp.database_id = qsrs.database_id ) AS x WHERE x.pn = 1 ) AS qsp CROSS APPLY ( SELECT TOP (1) qsqt. FROM #query_store_query AS qsq JOIN #query_store_query_text AS qsqt ON qsqt.query_text_id = qsq.query_text_id AND qsqt.database_id = qsq.database_id WHERE qsq.query_id = qsp.query_id AND qsq.database_id = qsp.database_id ORDER BY qsq.last_execution_time DESC ) AS qsqt CROSS APPLY ( SELECT TOP (1) qsq.* FROM #query_store_query AS qsq WHERE qsq.query_id = qsp.query_id AND qsq.database_id = qsp.database_id ORDER BY qsq.last_execution_time DESC ) AS qsq CROSS APPLY ( SELECT TOP (1) top_waits = STUFF ( ( SELECT TOP (5) ', ' + qsws.wait_category_desc + ' (' + FORMAT ( SUM ( CONVERT ( bigint, qsws.avg_query_wait_time_ms ) ), 'N0' ) + ' ms)' FROM #query_store_wait_stats AS qsws WHERE qsws.plan_id = qsrs.plan_id AND qsws.database_id = qsrs.database_id GROUP BY qsws.wait_category_desc ORDER BY SUM(qsws.avg_query_wait_time_ms) DESC FOR XML PATH(''), TYPE ).value('./text()[1]', 'varchar(max)'), 1, 2, '' ) ) AS w ) AS x WHERE x.n = 1 ORDER BY CONVERT(money, x.avg_query_max_used_memory_mb) DESC OPTION(RECOMPILE);

Msg 50000, Level 11, State 1, Procedure DBATOOLS.dbo.sp_QuickieStore, Line 8537 [Batch Start Line 0] error while selecting final results with @expert mode = 0 and format_output = 1 offending query:

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, qsp.force_failure_count, qsp.last_force_failure_reason_desc, qsp.plan_forcing_type_desc, w.top_waits, first_execution_time = CASE WHEN @timezone IS NULL THEN DATEADD ( MINUTE, @utc_minutes_original, qsrs.first_execution_time ) WHEN @timezone IS NOT NULL THEN qsrs.first_execution_time AT TIME ZONE @timezone END, ... Msg 235, Level 16, State 0, Line 4 Impossible de convertir une valeur char en money. La valeur char a une syntaxe incorrecte.

Completion time: 2024-06-26T15:25:50.2697927+02:00 `

[erikdarlingdata]

@egaultier Yep, this is a weird localization issue. I can't reproduce it on my end unless I change the language.

Here's a short repo:

/*Set language to English*/
SET LANGUAGE English;

/*Works*/
SELECT
    x.avg_query_max_used_memory_mb
FROM
(
SELECT TOP (10)
    avg_query_max_used_memory_mb = FORMAT(qsrs.avg_query_max_used_memory, 'N0')
FROM sys.query_store_runtime_stats AS qsrs
ORDER BY
   qsrs.avg_query_max_used_memory DESC
) AS x
ORDER BY
    CONVERT(money, x.avg_query_max_used_memory_mb) DESC;

/*Définir la langue sur le français*/
SET LANGUAGE French;
/*Ne marche pas*/
SELECT
    x.avg_query_max_used_memory_mb
FROM
(
SELECT TOP (10)
    avg_query_max_used_memory_mb = FORMAT(qsrs.avg_query_max_used_memory, 'N0')
FROM sys.query_store_runtime_stats AS qsrs
ORDER BY
   qsrs.avg_query_max_used_memory DESC
) AS x
ORDER BY
    CONVERT(money, x.avg_query_max_used_memory_mb) DESC;

/*Fonctionne*/
SELECT
    x.avg_query_max_used_memory_mb
FROM
(
SELECT TOP (10)
    avg_query_max_used_memory_mb = FORMAT(qsrs.avg_query_max_used_memory, 'N0')
FROM sys.query_store_runtime_stats AS qsrs
ORDER BY
   qsrs.avg_query_max_used_memory DESC
) AS x
ORDER BY
    TRY_PARSE(x.avg_query_max_used_memory_mb AS money) DESC;

I'll work on the code change, but for now you should be able to work around it by using SET LANGUAGE English; in the sp_QuickieStore SSMS tab.

[egaultier]

Hi Erik

Thank you for your quickly bug correction.

Now this OK on french instance.

have a good day.

Emmanuel