Queries in the plan cache where SQL Server has to convert a column’s data type (usually varchar against nvarchar), which can turn a seek into a scan and ruin row estimates. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.
/*
12_Implicit_Conversions.sql - queries in the plan cache where SQL Server has to convert a column's data type,
which can stop it using an index (scan instead of seek) and ruin its row estimates.
Clive Roberts DBA, 10 October 2026. Read-only. Server-wide. SQL Server 2016 or later.
THE USUAL CAUSE
The column is varchar but the query (or the application's parameter) is nvarchar, so SQL Server converts the
COLUMN on every row: WHERE AccountCode = N'ABC123' or an ORM sending every string as nvarchar(4000).
Data type precedence decides which side gets converted; when it's the column side, the index can't be
sought and the histogram can't be used properly.
HOW TO READ IT
issue 'Seek Plan' = the conversion stopped an index seek; 'Cardinality Estimate' = it made the row
estimate unreliable. Both appear as yellow warnings on the plan's root operator in SSMS.
expression the conversion itself, e.g. CONVERT_IMPLICIT(nvarchar(50),[Sales].[dbo].[Orders].[AccountCode],0):
the column being converted and the type it's converted to.
column_type the column's real type (looked up in its database), so you can see the mismatch.
parameters the parameter types the query was compiled with (the application's side of the mismatch).
REMEDIES (cheapest first)
1. Make the query/parameter match the column: N'...' -> '...', or in the app declare the parameter as
varchar(n) (e.g. ADO.NET SqlDbType.VarChar with a length; Entity Framework: IsUnicode(false) / HasColumnType("varchar(50)")).
2. If the column is wrong (it should hold Unicode), change the column type - a schema change, test it.
3. Short term, for a hot query you can't change: a computed column of the converted expression, indexed.
Conversions that only appear in the SELECT list (not WHERE/JOIN) are harmless - this script lists only
plan-affecting ones.
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @TopPlans int = 500; -- most CPU-expensive cached plans to examine (scanning plan XML is costly; raise with care)
DECLARE @IncludeSystemDbs bit = 0; -- 1 = also look at queries run in master/msdb/model
IF OBJECT_ID('tempdb..#conv') IS NOT NULL DROP TABLE #conv;
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
top_plans AS (
SELECT TOP (@TopPlans) qs.plan_handle, MAX(qs.sql_handle) AS sql_handle, SUM(qs.execution_count) AS execution_count,
SUM(qs.total_worker_time) AS total_worker_time, SUM(qs.total_logical_reads) AS total_logical_reads, MAX(qs.last_execution_time) AS last_execution_time,
MAX(pa.dbid) AS dbid
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle) WHERE attribute = N'dbid') AS pa
WHERE @IncludeSystemDbs = 1 OR pa.dbid > 4
GROUP BY qs.plan_handle ORDER BY SUM(qs.total_worker_time) DESC)
SELECT tp.plan_handle, tp.sql_handle, tp.dbid, tp.execution_count, tp.total_worker_time, tp.total_logical_reads, tp.last_execution_time,
w.c.value('@ConvertIssue', 'nvarchar(60)') AS issue,
w.c.value('@Expression', 'nvarchar(4000)') AS expression,
qp.query_plan.query('for $p in //ParameterList/ColumnReference return concat($p/@Column, " ", $p/@ParameterDataType, "; ")').value('.', 'nvarchar(2000)') AS parameters
INTO #conv
FROM top_plans AS tp
CROSS APPLY sys.dm_exec_query_plan(tp.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//Warnings/PlanAffectingConvert') AS w(c)
WHERE qp.query_plan IS NOT NULL;
-- Split "CONVERT_IMPLICIT(type,[db].[schema].[table].[column],style)" into its parts
ALTER TABLE #conv ADD converted_to nvarchar(200), col_db sysname NULL, col_schema sysname NULL, col_table sysname NULL, col_name sysname NULL, column_type nvarchar(200) NULL;
UPDATE #conv SET converted_to = SUBSTRING(expression, CHARINDEX(N'(', expression) + 1, CHARINDEX(N',', expression) - CHARINDEX(N'(', expression) - 1)
WHERE expression LIKE N'CONVERT_IMPLICIT(%,%';
UPDATE c SET col_db = PARSENAME(x.ref, 4), col_schema = PARSENAME(x.ref, 3), col_table = PARSENAME(x.ref, 2), col_name = PARSENAME(x.ref, 1)
FROM #conv AS c
CROSS APPLY (SELECT SUBSTRING(c.expression, CHARINDEX(N',', c.expression) + 1,
LEN(c.expression) - CHARINDEX(N',', c.expression) - CHARINDEX(N',', REVERSE(c.expression))) AS ref) AS x
WHERE c.expression LIKE N'CONVERT_IMPLICIT(%,[[]%].[[]%],%';
-- Look up each converted column's real type in its own database
DECLARE @db sysname, @sql nvarchar(max);
DECLARE d CURSOR LOCAL FAST_FORWARD FOR SELECT DISTINCT col_db FROM #conv WHERE col_db IS NOT NULL AND DB_ID(col_db) IS NOT NULL AND HAS_DBACCESS(col_db) = 1;
OPEN d; FETCH NEXT FROM d INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ' + QUOTENAME(@db) + N';
UPDATE c SET column_type = TYPE_NAME(col.user_type_id)
+ CASE WHEN TYPE_NAME(col.user_type_id) IN (N''varchar'', N''char'', N''varbinary'', N''binary'') THEN N''('' + CASE WHEN col.max_length = -1 THEN N''max'' ELSE CONVERT(nvarchar(10), col.max_length) END + N'')''
WHEN TYPE_NAME(col.user_type_id) IN (N''nvarchar'', N''nchar'') THEN N''('' + CASE WHEN col.max_length = -1 THEN N''max'' ELSE CONVERT(nvarchar(10), col.max_length / 2) END + N'')''
WHEN TYPE_NAME(col.user_type_id) IN (N''decimal'', N''numeric'') THEN N''('' + CONVERT(nvarchar(10), col.precision) + N'','' + CONVERT(nvarchar(10), col.scale) + N'')''
ELSE N'''' END
FROM #conv AS c
JOIN sys.columns AS col ON col.object_id = OBJECT_ID(QUOTENAME(c.col_schema) + N''.'' + QUOTENAME(c.col_table)) AND col.name = c.col_name
WHERE c.col_db = DB_NAME();';
BEGIN TRY EXEC (@sql); END TRY BEGIN CATCH END CATCH;
FETCH NEXT FROM d INTO @db;
END;
CLOSE d; DEALLOCATE d;
-- Result 1: worst affected queries
SELECT TOP (100) DB_NAME(c.dbid) AS [database], OBJECT_NAME(st.objectid, st.dbid) AS [object],
c.issue, c.col_schema + N'.' + c.col_table + N'.' + c.col_name AS converted_column, c.column_type, c.converted_to,
RTRIM(c.parameters) AS parameters, c.expression,
c.execution_count, c.total_worker_time / 1000 AS total_cpu_ms, c.total_logical_reads, c.last_execution_time,
LEFT(st.text, 4000) AS query_text
FROM #conv AS c CROSS APPLY sys.dm_exec_sql_text(c.sql_handle) AS st
WHERE st.text NOT LIKE N'%sys.dm[_]%' -- leave out monitoring queries (DMVs), including this one
ORDER BY c.total_worker_time DESC;
-- Result 2: the same, grouped by column - fix the worst column once and many queries benefit
SELECT col_db AS [database], col_schema + N'.' + col_table + N'.' + col_name AS converted_column, MAX(column_type) AS column_type,
STUFF((SELECT DISTINCT N', ' + c2.converted_to FROM #conv AS c2
WHERE c2.col_db = c.col_db AND c2.col_schema = c.col_schema AND c2.col_table = c.col_table AND c2.col_name = c.col_name
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'') AS converted_to,
COUNT(DISTINCT plan_handle) AS plans, SUM(execution_count) AS executions, SUM(total_worker_time) / 1000 AS total_cpu_ms
FROM #conv AS c WHERE col_name IS NOT NULL
GROUP BY col_db, col_schema, col_table, col_name
ORDER BY total_cpu_ms DESC;
DROP TABLE #conv;