The index on order_code had existed for two years. The query filtered on exactly one value. And the plan showed a full clustered index scan reading four million rows to return eleven. The developer's parameter was typed nvarchar, the column was varchar, and data type precedence did the rest: SQL Server converted four million rows one at a time to compare them against a parameter it could have converted once. Nothing errored. Nothing warned at the application layer. The query simply got a hundred times slower the week an ORM changed hands.
CONVERT_IMPLICIT is my least favorite plan operator because it hides in plans that look fine at a glance, and because it is almost never the database's fault. It is the application's type hygiene surfacing as a database performance problem, and fixing it for good requires knowing exactly where the conversion enters.
Why does CONVERT_IMPLICIT turn seeks into scans?
SQL Server's data type precedence is a strict hierarchy: when two compared types differ, the lower-precedence type converts to the higher, and nvarchar outranks varchar, datetime outranks strings, float outranks int. The precedence rules decide which side of the comparison converts, and the loser is whichever operand has the lower-precedence type — in the case that fills my inbox, the column. WHERE order_code = @p with @p typed nvarchar becomes WHERE CONVERT_IMPLICIT(nvarchar, order_code) = @p, and an index on order_code is now an index over a computed expression the seek cannot navigate. When the column holds the higher-precedence type the parameter converts instead and the seek usually survives, which is why the damage concentrates exactly where the column loses. The engine scans, converts every value, and filters late. The bigger the table, the more spectacular the damage, because the work grows with row count while the plan looks identical in a screenshot.
To be fair, the optimizer can sometimes salvage a seek through a dynamic-range rewrite, building a seekable range from the converted value; you see it for some type pairs like int against bigint. Do not count on it. For the classic nvarchar-parameter-against-varchar-column case the scan is the norm, and even when a seek survives, the converted predicate can bypass the histogram and degrade the row estimates too. The plan tries to warn you: the SELECT operator's properties carry a PlanAffectingConvert warning, and the predicate shows CONVERT_IMPLICIT in the seek or residual. Almost nobody opens those properties.
How do I find every offender in the plan cache?
The plan cache is XML, and the warning is a string in that XML, so the blunt instrument works: search cached plans for the conversion warning, rank by total CPU, and the worst offenders sort themselves to the top. This is the query I run on any server I inherit:
SELECT TOP 25 qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.total_logical_reads / qs.execution_count AS avg_reads,
SUBSTRING(st.text, 1, 200) AS statement_start
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE CONVERT(nvarchar(max), qp.query_plan) LIKE '%PlanAffectingConvert%'
ORDER BY qs.total_worker_time DESC;On systems with Query Store enabled, and it should be enabled on everything you own, the hunt is cleaner: find the queries whose reads jumped after an application deploy, then inspect their plan XML for the same warning. The regressed-query workflow from my notes on hunting regressions with Query Store applies verbatim, with the conversion as the smoking gun instead of a plan-shape change. Either way the tell in the data is identical: a query with execution_count in the millions and logical reads per execution in the hundreds of thousands is scanning something on every call, and on an OLTP table that something is usually a conversion.
Why do ORMs cause this so reliably?
Because the most convenient parameter API infers types, and inference picks the safe Unicode type for every string. The classic offender is ADO.NET's AddWithValue: hand it a .NET string and you get nvarchar, with the length derived from the value's length, which also fragments the plan cache into near-duplicate entries for nvarchar(7), nvarchar(12), and so on, each compiled separately. Entity Framework and Dapper do better when the mapping declares the column type and fall back to nvarchar when it does not. The application code looks identical in every case. Only the wire type changes, and the wire type is what the optimizer sees.
The datetime variant fails differently and sneakier. Sending a date as a string in a localized format forces a conversion whose result depends on the session's DATEFORMAT and language settings, and I have watched the same query seek in one session and scan in another because two application servers ran under different default languages. Send datetime parameters as datetime. When a literal must be a string, use the unambiguous forms: yyyymmdd, or the full ISO 8601 with the T separator. That is a five-minute code-review item that has paid for itself more often than any index I have ever built.
Should I fix it in the schema or in the query?
In the query and the application first, because the schema is innocent until proven guilty. Typing the parameter to match the column, varchar(50) for a varchar(50) column, datetime for datetime, costs nothing and restores the seek immediately. When the parameter type genuinely cannot change, a shared library you do not control, convert the parameter rather than the column: CAST(@p AS varchar(50)) on the right side of the predicate keeps the column naked and the index navigable. What you never do is convert the column side and staple an index hint over the top. That is painting over rust, and the rust wins.
When the mismatch is load-bearing, the column really is varchar and a dozen services really do send nvarchar, the schema-layer fix is a persisted computed column holding the converted value, indexed, with the queries pointed at it. It is honest technical debt: you pay storage for a second representation because the type negotiation is beyond your control. And when you get to design the column, type the data as it actually is; half the implicit conversions I meet trace back to an nvarchar column that has never held a non-ASCII character, or a varchar column that has.
-- the application-layer fix: type the parameter to match the column
DECLARE @code varchar(50) = 'A-1042'; -- not nvarchar(50)
SELECT order_id, order_date
FROM Sales.Orders
WHERE order_code = @code; -- seek restored
-- the schema-layer fix when the wire type cannot change
ALTER TABLE Sales.Orders
ADD order_code_nv AS CONVERT(nvarchar(50), order_code) PERSISTED;
CREATE INDEX IX_Orders_OrderCodeNv ON Sales.Orders(order_code_nv);What about the estimation damage nobody mentions?
Even when a conversion does not force a scan, it poisons cardinality estimation, because the histogram is over the column's native type and the predicate arrives in a different one. The optimizer falls back to density-vector guesses or fixed selectivity assumptions, and the estimate can land orders of magnitude off in either direction. That is how an implicit conversion becomes the upstream cause of bad join orders and wrong-sized memory grants on plans that look like statistics problems. I once chased a "stale statistics" issue for a full day before noticing the CONVERT_IMPLICIT that made the statistics irrelevant: no histogram refresh fixes an estimate that never reads the histogram.
The durable defense is boring hygiene: explicit parameter types in the data layer, unambiguous date formats, column types that match the data, and a monthly pass of the plan-cache query above. None of it is clever. All of it is cheaper than the incident review.
Watching conversion damage with MonPG when SQL Server support ships
The signals here are plan-level: queries whose reads-per-execution jump after a deploy, plans carrying PlanAffectingConvert warnings, and cache bloat from parameter-length variants, all visible in the plan cache and Query Store if someone looks. MonPG monitors PostgreSQL in production today; SQL Server support is in active development, and surfacing plan-level warnings like these is exactly the kind of thing it should do without being asked. Status is on the SQL Server monitoring (coming soon) page. Until then, the plan-cache query above, run monthly, is the whole program.