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_sql_text 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