Find All Queries with Implicit Conversion in SQL Server – Interview Question of the Week #107

Interview question: How can you find queries whose cached plans contain an implicit conversion?

Answer: Search the plan cache for CONVERT_IMPLICIT, then inspect the expensive plans. This finds candidates, not every query that has ever run and not proof that every conversion is harmful.

I first used this question while consulting. A new client wants evidence quickly. I can look at the cached plans without changing the workload, take a costly candidate to the developer, and check whether a small type correction actually improves it. That first demonstrated improvement often earns the trust needed for the harder tuning work.

Here is the cache search I use as a starting point. The plan and CPU columns make it possible to inspect a candidate before changing its SQL. CPU values are in microseconds. Run this with care on a busy instance because converting and searching plan XML has a cost of its own.

SELECT TOP (50)
    DB_NAME(st.dbid) AS database_name,
    st.text AS batch_text,
    qs.total_worker_time AS total_cpu_microseconds,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) AS average_cpu_microseconds,
    qs.max_worker_time AS max_cpu_microseconds,
    qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS average_elapsed_microseconds,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS average_logical_reads,
    qs.execution_count,
    qs.creation_time,
    qp.query_plan
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 N'%CONVERT_IMPLICIT%'
ORDER BY qs.total_worker_time DESC;

This query searches the instance cache. My original version also required st.dbid = DB_ID(), which restricted it to plans associated with the current database and could exclude rows with no database ID in the returned text. Add that filter when that narrower scope is what you need.

Inspecting this DMV requires VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, or VIEW SERVER STATE on earlier versions.

This is a search through cached plans for completed executions. An evicted plan or a query still in flight will not appear. A batch can contain more than one statement, so open the returned plan and identify the statement and expression with the conversion. DB_NAME(st.dbid) may be null for some cached text; do not treat that as evidence that the plan belongs to the current database.

What is the disadvantage of implicit conversion? The important case is a conversion on the indexed column in a predicate. It can interfere with an efficient seek or a useful cardinality estimate. A conversion of a constant or parameter can be harmless, and the presence of CONVERT_IMPLICIT alone does not establish a performance problem. Compare the actual predicate, plan, reads, and elapsed time before and after matching the parameter type to the column type.

If you want to see the consulting example in action, my GroupBy performance session shows why the conversation with the developer matters as much as the search query.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.