Part of the DBA-Tools Project.
An implicit conversion is SQL Server quietly changing a data type to compare two values that don’t match, and it’s one of the few performance problems that will not throw an error, will not show up in a slow-query complaint, and will not appear anywhere in the object definition. The query runs, it returns the right rows, and everyone moves on. What doesn’t show up is that a perfectly good index just got skipped in favour of a scan, because the optimiser couldn’t seek on a column it had to convert first.
Most of the time this traces back to one specific mismatch: a VARCHAR column compared against an NVARCHAR value coming from the application. .NET’s SqlParameter defaults string parameters to NVARCHAR, so any app built against a VARCHAR schema is generating this pattern on every parameterised query that touches that column, all day, every day.
This script scans the plan cache for the specific warning SQL Server leaves behind when this happens, so you can find every query paying this tax without touching a single stored procedure.
Why Implicit Conversions Matter
Data type precedence is the root of it. When SQL Server compares two different types, it converts the lower-precedence one to match the higher-precedence one, per a fixed rule table. NVARCHAR outranks VARCHAR. That means:
WHERE VarcharColumn = @NvarcharParamconverts every value inVarcharColumn, not the parameter.- A conversion applied to a column disqualifies that column from an index seek on most non-trivial predicates, because the optimiser can no longer use the index’s sort order directly. It falls back to a scan.
- The scan reads far more pages than a seek would, which shows up as extra logical reads and extra CPU, multiplied by however many times that query runs per day.
- On a busy OLTP table this can be the difference between a query that costs a handful of logical reads and one that costs tens of thousands, with no code change ever showing up in a diff.
The insidious part is that the query still “works.” Nobody gets paged. It just costs more than it should, forever, until someone goes looking.
Common Symptoms
- A query against a well-indexed column runs as an index scan or a table scan in the execution plan, with no obvious reason why.
- CPU usage climbs on queries that look trivial by their WHERE clause.
- The yellow warning triangle appears on a SELECT operator in SSMS’s graphical plan, with a tooltip mentioning
CONVERT_IMPLICIT. - The same query performs differently depending on whether it’s called from the application (parameterised,
NVARCHAR) versus pasted into SSMS as a literal (often typed to match the column).
When to Run This Script
- Routine SQL Server health checks, especially after adding a new application feature that queries an existing table
- Investigating a query that scans an index it should be able to seek
- After a migration or ORM change (Entity Framework, Dapper, and most .NET data layers default string parameters to
NVARCHAR) - Whenever CPU usage is high but no single query stands out as obviously expensive in isolation
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-ImplicitConversions
Category : performance
Purpose : Scans the plan cache for implicit conversion warnings. These cause index range
scans instead of seeks and generate unnecessary CPU. Most common cause: VARCHAR
column compared to NVARCHAR parameter, or INT column compared to VARCHAR.
NOTE: scans plan XML — runs for 10–30 seconds on busy servers with large plan caches.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-implicit-conversions/)
Requires : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Medium
SET NOCOUNT ON;
DECLARE @top INT = 50; -- reduce if plan cache is very large
SELECT TOP (@top)
qs.total_logical_reads AS total_logical_reads,
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
CAST(qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000.0 AS DECIMAL(10,2))
AS avg_cpu_ms,
DB_NAME(qt.dbid) AS database_name,
OBJECT_NAME(qt.objectid, qt.dbid) AS object_name,
LEFT(qt.text, 500) AS query_text,
qs.creation_time AS plan_cached_at,
-- Extract the first PlanAffectingConvert expression from the plan XML
CAST(qp.query_plan AS NVARCHAR(MAX)) AS query_plan_xml
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%PlanAffectingConvert%'
AND qt.dbid IS NOT NULL
AND qt.dbid > 4
ORDER BY qs.total_logical_reads DESC;
This scans sys.dm_exec_query_stats for every cached plan whose XML contains a PlanAffectingConvert warning, joins back to the query text and the owning object, and ranks by total logical reads so the most expensive offenders sort to the top.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Scan the plan cache for implicit conversion warnings:
.\run.ps1 Get-ImplicitConversions
# To run against a remote sql server:
.\run.ps1 Get-ImplicitConversions -ServerInstance SQLSERVER01
This script lives in the repo at:
sql/performance/queries/Get-ImplicitConversions.sqlpowershell/wrappers/performance/queries/Get-ImplicitConversions.ps1
Example Output
Getting a genuine hit from this script means having a real VARCHAR-vs-NVARCHAR mismatch sitting in the plan cache, so this example was built the way an application actually triggers one: a VARCHAR(20) lookup column, indexed, queried through a stored procedure with an N'...' literal instead of a plain string. One row came back:
| total_logical_reads | execution_count | total_cpu_ms | avg_cpu_ms | database_name | object_name |
|---|---|---|---|---|---|
| 200 | 20 | 16 | 0.83 | RandomLab | LookupCustomerDemo |
The query_plan_xml column (not shown in the table above, it’s the full showplan) is where the proof lives: it contains a <Warnings><PlanAffectingConvert ConvertIssue="Seek Plan" .../></Warnings> node naming the exact column and the exact conversion SQL Server applied. That’s the same detail SSMS surfaces as the yellow warning triangle on the operator, and it’s the fastest way to confirm you’re looking at a real implicit conversion rather than a coincidental scan.
total_logical_reads and total_cpu_ms are cumulative since the plan was cached, so they keep growing with every execution, this is the main thing to review when ranking offenders by cost. object_name is null for ad hoc, unparameterised queries since they aren’t owned by a stored procedure or function; query_text is still populated in those cases.
Understanding the Results
There’s no severity band here, either a query has a PlanAffectingConvert warning in its plan or it doesn’t. What matters is ranking:
- High
total_logical_readswith highexecution_count— the priority list. A cheap-looking query that runs thousands of times a day pays this tax every single time. avg_cpu_msclimbing with row count — a sign the conversion is forcing a scan whose cost scales with table size, which gets worse as the table grows even if nothing else changes.object_namepopulated — the fix has a clear home: the stored procedure or function that owns the query.object_namenull — the query is ad hoc or dynamically built, so the fix lives in application code or an ORM mapping instead of a single stored procedure.
Common Causes
The pattern nearly always starts on the application side, not the database side. System.Data.SqlClient and Microsoft.Data.SqlClient both default a SqlParameter built from a plain C# string to NVARCHAR, regardless of what type the target column actually is. If the schema was designed with VARCHAR (common for ASCII-only data like codes, emails, or usernames), every parameterised query against it is silently mismatched from day one. ORMs inherit the same default, so Entity Framework and Dapper both reproduce it unless the mapping is told otherwise.
The other common source is a schema that grew organically: a column started as VARCHAR, a later feature needed Unicode support elsewhere in the same table, and a query written to match the new convention ended up comparing against the old column.
How to Fix Implicit Conversions
There are two valid fixes, and which one applies depends on whether the data genuinely needs Unicode:
Match the parameter type to the column (most common fix). If the column is VARCHAR and doesn’t need Unicode, force the application’s parameter type to match:
// Wrong - defaults to NVARCHAR regardless of column type
cmd.Parameters.AddWithValue("@CustomerCode", customerCode);
// Right - explicit VARCHAR to match the column
cmd.Parameters.Add("@CustomerCode", SqlDbType.VarChar, 20).Value = customerCode;
The equivalent fix in raw T-SQL is just as simple, drop the N prefix on the literal:
-- Forces a conversion on CustomerCode
SELECT CustomerId, CustomerCode, CustomerName
FROM dbo.CustomerLookupDemo
WHERE CustomerCode = N'CUST00042';
-- Seeks cleanly, no conversion
SELECT CustomerId, CustomerCode, CustomerName
FROM dbo.CustomerLookupDemo
WHERE CustomerCode = 'CUST00042';
Change the column to NVARCHAR (when the data genuinely needs Unicode). If the application is correctly sending Unicode-capable parameters and the column is the one that’s wrong, ALTER TABLE ... ALTER COLUMN to NVARCHAR instead, but test the storage impact first, NVARCHAR roughly doubles the on-disk size of the column for non-Unicode data. This is the less common fix in practice; most implicit conversions found by this script trace back to an over-eager NVARCHAR default on the application side, not a genuine Unicode requirement.
Best Practices
- Match parameter types to column types explicitly in application code, don’t rely on
AddWithValueor its ORM equivalent to guess correctly. - Pick
VARCHARorNVARCHARper column at design time based on whether the data needs Unicode, and be consistent about it across a table. - Run this script after any new feature ships against an existing table, mismatches are cheapest to fix before the query pattern is embedded in a dozen call sites.
- When reviewing a slow query, check the execution plan for the warning triangle before assuming the index is missing, sometimes the index is fine and the comparison is the problem.
Related Scripts
You may also find these scripts useful:
Frequently Asked Questions
What is an implicit conversion in SQL Server?
An implicit conversion is when SQL Server automatically changes the data type of one side of a comparison so both sides match, without the query explicitly asking for it. It happens silently, based on the fixed data type precedence rules, most commonly when a VARCHAR column is compared against an NVARCHAR value.
Why does an implicit conversion slow a query down?
Because the conversion is applied to the column, not the literal, the optimiser can no longer use that column’s index to seek directly. It falls back to scanning the index (or the table) and evaluating the comparison row by row, which costs far more logical reads and CPU than a seek.
How do I know if a query has an implicit conversion problem?
Check the execution plan for a yellow warning triangle on the SELECT or scan operator, the tooltip names the conversion. Or run this script, which scans the plan cache directly for the same PlanAffectingConvert warning across every cached plan at once.
Summary
Implicit conversions are one of the quieter performance costs in SQL Server, because nothing about them looks broken. The query returns correct results, no error is raised, and the only trace is a warning buried in the execution plan XML that most people never open. Running this script periodically, especially after a new feature ships against an existing table, catches the pattern before it’s baked into a dozen call sites across the application.
The fix is almost always small once found: match the parameter type to the column type, or change the literal’s prefix. What’s expensive is the discovery, not the repair, which is exactly what this script is for.
Leave a Reply