SQL Server – Detection of Implicit Conversions

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’.

Extra content in this blog post has come from Jonathan Kehayias’ blog Implicit Conversions that Cause Index Scans, and  Rob Farley’s blog.

 

Implicit Conversions

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.

 

Residual conversions

“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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s