A queue polling query. Simple status filter. 800 executions.
5,301,021 logical reads per execution —> After the fix: 3
WEEK 02 - IMPLICIT CONVERSION
THE SYMPTOM
The query appeared in the top 10 queries by CPU consumption.
It should have touched a handful of records.
It was scanning millions instead.
THE EXECUTION PLAN
The execution plan showed a warning on the SELECT node.
Hovering over it revealed this:
Warnings
Type conversion in expression (CONVERT_IMPLICIT(nvarchar(20),
[msg_queue].[msg_status],0)) may affect "CardinalityEstimate"
in query plan choiceTHE PATTERN
The column msg_status is VARCHAR(20).
The application was sending the parameter as NVARCHAR(4000).
-- Column definition
msg_status VARCHAR(20) NOT NULL
-- Parameter sent by Hibernate
@P0 nvarchar(4000) = N'NEW'When the types don't match, SQL Server converts
the lower-precedence type to the higher one.
NVARCHAR sits above VARCHAR in the precedence hierarchy.
So SQL Server converted the column, not the parameter.
Every single row.
A function applied to a column breaks sargability.
The index on msg_status became unusable for a seek.
THE FIX
Hibernate sends string parameters as NVARCHAR by default.
One property in the JDBC connection string changes that:
sendStringParametersAsUnicode=falseParameter type now matches column type.
CONVERT_IMPLICIT gone.
SQL Server seeks directly to the matching rows.
THE NUMBERS
Metric | Before | After |
|---|---|---|
Parameter type | nvarchar(4000) | varchar(20) |
Logical reads per execution | 5,301,021 | 3 |
Reduction | 99.99% |
THE LESSON
The column was VARCHAR. The parameter was NVARCHAR.
That mismatch alone was enough to prevent a seek
on an existing, correct index.
When you see CONVERT_IMPLICIT in a plan warning,
check the parameter type first.
Make it match the column. Nothing else needed.
One more thing: if you don't see a missing index suggestion
in the plan, don't stop there.
The implicit conversion may hide the real access path problem.
Fix the type mismatch first.
Only then re-check the plan: a missing index suggestion
may appear after the predicate becomes sargable again.
Pier Giuseppe Mandosi
SQL Server Performance Diaries