[Trace Parser Agent] - SQL text silently truncated to 4,000 characters during import, hiding query differences

[dimontei]

Trace Parser Agent: SQL text silently truncated to 4,000 characters during import, hiding query differences in Web Version

Description

When comparing fast and slow traces containing long SQL statements, the agent receives incomplete SQL text and cannot inspect trailing clauses such as JOIN, WHERE, ORDER BY, and query hints.

The observed statements ended at the same point in their shared SELECT list. Their important difference occurred later in the statement:

ORDER BY T1.PURCHID DESC

versus:

ORDER BY T1.CREATEDDATETIME DESC

Full statements were recovered independently from the original ETL files. This demonstrates that the missing text was available in the source traces; it was not necessarily missing from the capture.

An earlier hypothesis attributed the truncation to an ETW capture limitation. Source inspection instead identified explicit truncation in the importer, as detailed below.

Actual behavior

• Long SQL statements lose everything after the first 4,000 characters during import.
• Statements with identical prefixes but different suffixes can appear identical to the agent.
• The agent reports that the SQL is truncated, but may still infer conclusions from other statements rather than retrieving the missing evidence.
• Reimporting after fixing truncation may leave existing truncated records unchanged.

Confirmed code findings

Source inspected September 25, 2026:
TraceParserWeb/TraceParserFunction/SqlImporter.cs

1. The bulk import path explicitly limits statements to 4,000 characters

BulkInsertDimensions() calls:

BulkUpsertHashDim(
    conn,
    dims.GetAllQueryStatements(),
    "QueryStatements",
    "QueryStatementHash",
    "Statement",
    4000,
    false);

BulkUpsertHashDim() then truncates each value:

var truncated = text.Length > maxLen ? text[..maxLen] : text;
dt.Rows.Add(hash, truncated);

It also creates a matching fixed-length staging column:

cmd.CommandText =
    $"CREATE TABLE #HashStage (HashVal BIGINT PRIMARY KEY, TextVal NVARCHAR({maxLen}))";

For query statements, this becomes NVARCHAR(4000).

2. The single-statement helper also truncates SQL

EnsureQueryStatement() contains:

cmd.Parameters.AddWithValue(
    "@s",
    stmt.Length > 4000 ? stmt[..4000] : stmt);

Both paths need correction.

3. Existing records are not repaired on reimport

The bulk path uses INSERT ... WHERE NOT EXISTS, while the single-statement helper uses IF NOT EXISTS.

Because hashes are computed before truncation, reimporting the full statement can find the existing hash and skip insertion, leaving the truncated text in place.

4. Original SQL casing is also lost

InMemoryDimensions.EnsureQueryStatement() and the single-statement helper uppercase the entire statement before hashing/storage:

stmt = stmt.ToUpperInvariant();

This also changes string literals and potentially case-sensitive identifiers. Original SQL should be preserved separately from any normalized comparison representation.

Steps to reproduce

1. Import two SQL statements longer than 4,000 characters, sharing the same first 4,000 characters but differing in their final ORDER BY clause.
2. Confirm that the original input contains both complete statements.
3. Inspect the corresponding QueryStatements.Statement values and their lengths.
4. Retrieve them through the MCP tools and ask the agent to compare them.
5. Observe that the distinguishing suffixes are missing.

Use synthetic statements or sanitized traces for a publicly shareable reproduction.

Proposed fix

1. Preserve full SQL throughout ingestion. Remove statement slicing in both import paths. Use NVARCHAR(MAX) for query-statement staging and ensure the destination column supports full text. Use an explicit SqlDbType.NVarChar parameter with size -1 for the single-statement path. Review schema dependencies before migration.
2. Preserve original text. Keep normalization separate from the raw statement. Plan hash compatibility and migration carefully so existing trace references remain valid.
3. Provide a repair/backfill path. Recover existing truncated values from original traces and update the matching records with validated complete text. Increasing column size alone cannot restore lost suffixes.
4. Make completeness explicit. Expose captured length, stored length, and known truncation status. Length equal to 4,000 should indicate suspected legacy truncation, not prove truncation.
5. Support efficient full-text retrieval. Return compact previews for discovery, with a dedicated full-statement or chunked retrieval tool. Include length and integrity metadata so clients can detect incomplete responses.
6. Prevent unsupported agent conclusions. If either statement is incomplete, the agent should state that comparison is inconclusive and retrieve the full text before claiming equivalence or identifying clause differences.

Acceptance criteria

• Statements below, at, and above 4,000 characters round-trip without losing text.
• Two statements sharing a 4,000-character prefix retain distinct suffixes through database storage and MCP retrieval.
• SQL literals, Unicode, and original casing are preserved.
• A documented recovery process repairs previously truncated records without breaking trace references.
• End-to-end comparison identifies an ORDER BY difference located beyond character 4,000.
• Incomplete SQL produces an explicit limitation rather than an inferred definitive diagnosis.