DBA Scripts: Get Implicit Conversions

An indexed lookup is scanning when it should seek, or CPU is climbing with no single query to blame, and the application sends its strings as nvarchar. This script lists every cached statement whose plan carries SQL Server’s implicit conversion warning, the ones that lost an index seek first.

Why Implicit Conversions Matter

When the 2 sides of a comparison have different types, SQL Server converts the one lower in data type precedence. nvarchar outranks varchar, so WHERE VarcharColumn = @NvarcharParam converts the column, not the parameter.

• Under a SQL collation (names that start SQL_, such as SQL_Latin1_General_CP1_CI_AS) that turns the index seek into a scan. Under a Windows collation such as Latin1_General_CI_AS it still seeks, because SQL collations compare non-Unicode and Unicode data with different rules.
• On a 100,000 row lab table under SQL_Latin1_General_CP1_CI_AS: 321 logical reads with an nvarchar parameter, 5 with varchar.
• The plan carries a PlanAffectingConvert warning with ConvertIssue="Seek Plan". That is what this script looks for.
• The fix is the parameter type, not the index.

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), which The Query Is Fast in SSMS but Slow From the Application walks through.

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 (PlanAffectingConvert), one row
              per cached statement. ConvertIssue "Seek Plan" means the conversion stopped an index
              seek; "Cardinality Estimate" means it may have skewed the row estimate. Most common
              cause: VARCHAR column compared to an NVARCHAR parameter under a SQL collation.
              Covers stored procedures and the parameterised statements applications send through
              sp_executesql. Reads every cached statement, so cost grows with the plan cache.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-implicit-conversions/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Medium
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @top INT = 50; -- rows returned; every cached statement is still read

SELECT TOP (@top)
    w.convert_issue,
    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(CONVERT(INT, pa.value)) AS database_name,
    OBJECT_NAME(qt.objectid, qt.dbid) AS object_name,
    LEFT(SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
              ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1), 500) AS query_text,
    w.convert_expression,
    qs.creation_time AS plan_cached_at,
    w.statement_plan 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_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS tqp
CROSS APPLY (
    SELECT CONVERT(INT, value) AS value
    FROM sys.dm_exec_plan_attributes(qs.plan_handle)
    WHERE attribute = N'dbid'
) AS pa
CROSS APPLY (SELECT TRY_CONVERT(XML, tqp.query_plan) AS statement_plan) AS x
CROSS APPLY (
    SELECT
        x.statement_plan.value('(//*:PlanAffectingConvert/@ConvertIssue)[1]', 'NVARCHAR(60)') AS convert_issue,
        x.statement_plan.value('(//*:PlanAffectingConvert/@Expression)[1]', 'NVARCHAR(400)') AS convert_expression,
        x.statement_plan
) AS w
WHERE tqp.query_plan LIKE N'%PlanAffectingConvert%'
  AND pa.value > 4
ORDER BY CASE WHEN w.convert_issue = N'Seek Plan' THEN 0 ELSE 1 END,
         qs.total_logical_reads DESC;

It reads every statement in sys.dm_exec_query_stats, keeps the ones whose own plan from sys.dm_exec_text_query_plan contains a PlanAffectingConvert warning, and lists Seek Plan warnings first, then by total logical reads. It covers stored procedures and the sp_executesql statements an application sends.

It reads the plan of every cached statement before it filters, so it costs more the bigger the plan cache, and @top only limits the rows it returns. On a busy instance with a large plan cache, run it off-peak.

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.sql
• powershell/wrappers/performance/queries/Get-ImplicitConversions.ps1

Example Output

The lab table has 100,000 rows and an indexed varchar(20) column under SQL_Latin1_General_CP1_CI_AS. I queried it 20 times each, 3 ways: a stored procedure with an nvarchar parameter, sp_executesql with the same parameter (the way .NET sends a string), and a procedure with a CAST in its select list. The capture shows 5 of the 11 columns for width.

• Rows 1 and 2 are the same lookup, one from the procedure and one from the application: 6,540 logical reads over 20 calls each, 327 a call, for a lookup that costs 5 with a matching parameter.
• object_name is NULL on the sp_executesql row because no procedure owns it, so the fix lives in the application code that sends it.
• Row 3 is a Cardinality Estimate warning from the CAST. It cost no seek, and it sorts last.

Understanding the Results

Common Causes

It nearly always starts in the application. System.Data.SqlClient and Microsoft.Data.SqlClient both send a SqlParameter built from a plain C# string as NVARCHAR, whatever the column type is, and Entity Framework and Dapper inherit that default unless the mapping says otherwise. Against a VARCHAR column (codes, emails, usernames) every parameterised query is mismatched from day one.

The other source is a table that grew: an old VARCHAR column, and a newer query written to the NVARCHAR convention used elsewhere in the table.

How to Fix Implicit Conversions

There are 2 fixes, and which one applies depends on whether the data 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;

In T-SQL, 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 needs Unicode). If the application is right to send Unicode and the column is what’s wrong, ALTER TABLE ... ALTER COLUMN to NVARCHAR instead. Test the storage first: NVARCHAR roughly doubles the on-disk size of non-Unicode data. This is the less common fix; most conversions this script finds trace back to the application’s NVARCHAR default.

Best Practices

• Match parameter types to column types explicitly in application code. Don’t rely on AddWithValue or its ORM equivalent to guess correctly.
• Pick VARCHAR or NVARCHAR per 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:

• The Query Is Fast in SSMS but Slow From the Application, the nvarchar-on-varchar case this script flags is one half of why the same query plans differently by client
• Query and Performance Tuning (hub)
• Query Performance Deep-Dive
• Statistics Health
• Top CPU Queries / Top CPU, I/O, and Duration Queries
• SQL Server High CPU, the incident page for CPU that climbs with no single query to blame
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

Nothing about an implicit conversion looks broken: the results are right, no error is raised, and the only trace is a warning in the plan. Run this script after a new feature ships against an existing table, and fix what it finds by matching the parameter type to the column.