[BUG] `report.daily_summary_v2.metric_value` renders `worst_query_hash` as garbled text

[argpna]

Component

SQL collection scripts

Performance Monitor Version

3.2.0

SQL Server Version

SQL Server 2022 CU (docker image mcr.microsoft.com/mssql/server:2022-latest)

Windows Version

N/A - reproduced via Docker. The bug is independent of the client/host OS

Describe the Bug

report.daily_summary_v2 pivots mixed-type metrics into a single metric_value sql_variant column so callers can SELECT sort_order, metric_name, metric_value FROM report.daily_summary_v2 ORDER BY sort_order. The worst_query_hash row boxes the raw binary(8) query_hash (collect.query_stats.query_hash is declared binary(8), not varbinary) directly:

metric_name = N'worst_query_hash',
metric_value = CONVERT(sql_variant, wq.query_hash)

Any caller that follows the view's documented usage and converts metric_value straight to a string type gets unreadable output instead of a hash, because SQL Server's binary-to-char conversion reinterprets the raw bytes as UTF-16 code units.

Every other row in the view is a scalar (date/int/nvarchar) and converts to a sane string. Only the worst_query_hash row is affected, since it's the one row carrying a binary payload.

Steps to Reproduce

docker pull mcr.microsoft.com/mssql/server:2022-latest

docker run -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD='NotStrong!Passw0rd' \
  --name sqltest -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest

# short pause so database service can start up
sleep 15

Reproduce the view's own pattern with a literal hash:

docker exec -it sqltest /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
  -P 'NotStrong!Passw0rd' -C -Q "
DECLARE @query_hash binary(8) = 0x1F7A7FB76757088B;
DECLARE @metric_value sql_variant = CONVERT(sql_variant, @query_hash);
SELECT metric_value = CONVERT(nvarchar(500), @metric_value);"

Confirm the base type SQL Server sees:

docker exec -it sqltest /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
  -P 'NotStrong!Passw0rd' -C -Q "
DECLARE @query_hash binary(8) = 0x1F7A7FB76757088B;
DECLARE @metric_value sql_variant = CONVERT(sql_variant, @query_hash);
SELECT base_type = SQL_VARIANT_PROPERTY(@metric_value, 'BaseType');"

Expected: binary. Note for anyone patching a caller-side workaround instead of the view itself: SQL_VARIANT_PROPERTY(..., 'BaseType') reports binary here, not varbinary - matching query_hash's actual declared column type - so a workaround CASE needs to check for binary (and varbinary, for safety against a future column-type change) rather than assuming varbinary.

Compare against the fixed expression (convert to hex before boxing):

docker exec -it sqltest /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
  -P 'NotStrong!Passw0rd' -C -Q "
DECLARE @query_hash binary(8) = 0x1F7A7FB76757088B;
DECLARE @metric_value sql_variant = CONVERT(sql_variant, CONVERT(nvarchar(20), @query_hash, 1));
SELECT metric_value = CONVERT(nvarchar(500), @metric_value);"

Cleanup:

docker rm -f sqltest

Expected Behavior

metric_value
-------------------
0x1F7A7FB76757088B

A readable hex hash, matching every other scalar row in the view.

Actual Behavior

metric_value
------------
稟띿坧謈

The raw bytes get reinterpreted as UTF-16 code units instead of rendering as hex.

Error Messages / Log Output

NA - no error is raised; the conversion silently produces garbled text.

Screenshots

NA

Additional Context

Checked Dashboard/Services/DatabaseService.Overview.cs's GetDailySummaryAsync - it queries report.daily_summary (v1), which has no query_hash/worst_query_* columns at all, so the WPF app itself never hits this. daily_summary_v2 appears to only be consumed by callers that follow the view's own documented ad hoc usage (as my Grafana Daily Summary panel does downstream).

Fix: convert wq.query_hash to a hex nvarchar before boxing it into sql_variant, matching the style already used elsewhere in the codebase for query_hash display:

metric_value = CONVERT(sql_variant, CONVERT(nvarchar(20), wq.query_hash, 1))

Applied as a one-line change to the worst_query_hash projection in install/47_create_reporting_views.sql (report.daily_summary_v2 view definition), idempotent via CREATE OR ALTER VIEW.

[erikdarlingdata]

Fixed by your PR #1667 (commit 49cf527), merged to dev — the view now converts the hash to its hex string form before boxing into sql_variant. The CHANGELOG entry for it is riding in #1672. Thanks for the fix and the thorough repro!