These notes were taken whilst watching the fabulous PluralSight course “Why Physical Database Design Matters, Datatypes and Query Performance – Kimberly Tripp.”
TL;DR: Writing SQL statements which compare or join columns of different data types can cause performance problems. To locate when this happens, you can use two scripts. One to locate implicit conversions in comparisons/ordering and one to locate probe residual conversions typically found in ‘joins’.
When executing an SQL statement which tries to compare char, nvarchar, varchar, and some datetime types with each other SQL server may be prevented from using any indices and need to convert the data in the rows to perform the operation. This chart shows the danger zones – in Yellow. This is when an index scan is performed due to conversion rather than a more robust outcome (no conversion/index seek/obvious failure). The results may differ depending on your collation settings too. – But in general do not allow users to write ad-hoc statements and make sure that statements use the correct datatype!
The blog Finding Implicit Column Conversions in the Plan Cache details a script to help locate if any conversion has taken place recently in your plan cache. In testing I’ve also seen conversion from int to bigint be reported by this script.
“Probe Residual” is the name for conversions which happen implicitly during join execution (a residual converted column value is used). This is usually only a problem when foreign key relationships aren’t part of the integrity (e.g. soft keys), and the converted column has a large number of rows.
Sometimes these appear in the SQL Server Execution plan as scalar computations immediately before the join. If you hover over the join block and look for “Probe Residual”, you’ll know it has happened. However, there’s no need to dig into each query. You can just download and execute this script to find any probe residual conversions – thanks Kimberly! Just try joining an int field with a bigint field, and you’ll get a hit from this script.