Implicit Conversion in SQL Server

Implicit conversions are one of the highest-leverage performance problems hiding in SQL Server workloads. They happen when the optimizer has to convert a value from one data type to another at runtime — usually because a column type and a parameter type don’t match — and the conversion can silently disable index seeks and turn a fast query into a full table scan. The damage compounds under load: the same query that ran in milliseconds against a few thousand rows starts taking seconds against a few million.

The hard part is that nothing about the query text gives the problem away. There’s no error, no warning at compile time, and the conversion itself is so fast that no individual call looks slow. You have to inspect the query plan or audit your column types proactively to find them.

Audit Your Data Types

The following query shows a count of each data type in use across all user tables. This is a useful starting point for spotting mismatches that lead to implicit conversions.

1
2
3
4
5
6
7
8

SELECT
    t.name AS TypeName,
    COUNT(t.name) AS CountOfType
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id = t.user_type_id
JOIN sys.tables AS tab ON tab.object_id = c.object_id
WHERE tab.is_ms_shipped = 0
GROUP BY t.name

Further Reading

• Exploring the Plan Cache – Warnings – finding conversion warnings inside cached query plans
• Finding Implicit Column Conversions in the Plan Cache – Jonathan Kehayias’s approach to mining the plan cache for implicit conversions
• Implicit Conversion Costs – measuring the real performance impact
• Do SQL Server User Defined Datatypes (UDT) Affect Performance? – whether UDTs introduce hidden conversion overhead