Implicit Conversions – Introduction

In this blog post, I’ll explain and demonstrate how to use Extended Events to detect Implicit Conversions that affect execution plans on your SQL Server instance. But before we even start with Extended Events, let’s briefly recall what an implicit conversion is, when it happens and what negative impact it can possibly have on your execution plans.

An implicit conversion occurs when SQL Server needs to automatically cast different data types in expressions (for example JOIN conditions, WHERE clause etc.) when comparing data. No doubt, implicit conversions have negative impact on your SQL Server instance. Namely, increase of CPU usage, indexes won’t be used as efficiently as they possibly could be (an index scan can happen even if there is a covering index in place and you would expect to observe an index seek operation), increase of I/O, etc.

Example

We have a very simple query that retrieves data from the dbo.DimEmployee table (from the AdventureWorksDW2017 database). We also have a covering index in place, so I would expect to see the Index Seek operation in the execution plan:

Checking the actual execution plan, we can see that SQL Server performed Index Scan (instead of expected Index Seek):

and in the Properties, we can also see the Warning related to the ConvertIssue: SeekPlan

This implicit conversion is purely caused by data types mismatch. The EmployeeNationalIDAlternateKey column that is used in the WHERE clause has a data type NVARCHAR(15) and is compared to the INT value 830150469.

We can fix this query just by using the correct data type in the WHERE clause. See the adjusted query below:

and now, when the correct data type is used, we can see that SQL Server performed an Index Seek (using the same covering index as in the previous example):

Well, we have just fixed one query, but imagine that there are hundreds or even thousands of queries running frequently on your SQL Server instance. One of the options for detecting which queries are causing implicit conversions is to use Extended Events.

Detect Implicit Conversions using Extended Events

First and foremost, we’ll need to create a new xEvent session with plan_affecting_convert event. To get more details about a query, let’s also include client_app_name, database_name, sql_text and plan_handle actions. See the T-SQL script below:

If we want to start the xEvent session, we can execute the following command (or you can just start it from SSMS, under Extended Events):

Now, if we execute the query that is causing an implicit conversion against the AdventureWorksDW2017 database (for example the query I showed you at the beginning of this blog post), it should automatically be captured by our xEvent.

Open the file under the DetectImlicitConversions session:

and review all the details about the queries that are causing this issue:

If you want to stop the xEvent session, you can just execute the following command (or just stop it in SSMS, under Extended Events):

I would recommend you use this event and actions in your DEV and Test environments to capture all the problematic queries before you release your solution to Production environment. Some of the actions can have more overhead so make sure you test and monitor the performance impact on your server.

The source code of the xEvent Session is available on GitHub.

If you are not allowed to use Extended Events in your environment (for whatever reason), there is an alternative. I would suggest you read this blog post written by Jonathan Kehayias, where you can find a T-SQL script that queries the plan cache and retrieves query plans that have an implicit conversion.

Sources

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

https://www.scarydba.com/2018/10/15/using-extended-events-to-capture-implicit-conversions/

https://www.sqlskills.com/blogs/jonathan/finding-implicit-column-conversions-in-the-plan-cache/