Using Extended Events to Capture Implicit Conversions

Using the appropriate data type to avoid conversions or implicit conversions is a fundamental approach to good T-SQL coding practices. Implicit conversions can lead to poor performance. However, other than looking at every execution plan in your system, is there a way to see if you are experiencing implicit conversions? Actually, yeah, it’s right there in Extended Events.

plan_affecting_convert

Built right into the Extended Events is an event that captures conversions that would affect execution plans, plan_affecting_convert. This event will show both CONVERT and CONVERT_IMPLICIT warnings that you would normally only see within an execution plan. You can capture this event with others. Capturing events together along with causality tracking makes it very easy to track queries that have the issue. Here’s one example of how you might capture implicit conversions along with the query:

CREATE EVENT SESSION ImplicitConversion
ON SERVER
    ADD EVENT sqlserver.plan_affecting_convert
    (WHERE (sqlserver.database_name = N'AdventureWorks2017')),
    ADD EVENT sqlserver.sql_batch_completed
    (WHERE (sqlserver.database_name = N'AdventureWorks2017')),
    ADD EVENT sqlserver.sql_batch_starting
    (WHERE (sqlserver.database_name = N'AdventureWorks2017'))
    ADD TARGET package0.event_file
    (SET filename = N'C:\PerfData\ImplicitConversion.xel')
WITH (TRACK_CAUSALITY = ON);

This set of events will capture the start of a batch and the completion of a batch as well as the plan_affecting_convert event. Because I set TRACK_CAUSALITY to ON, I can be sure to associate the implicit conversions with a specific query. You could easily modify this to work with statements or remote procedure calls.

Another way is to capture just the plan_affecting_convert event. Then use the Actions (also called Global Fields) to get the query. This session will do the trick:

CREATE EVENT SESSION ImplicitConversionOnly
ON SERVER
    ADD EVENT sqlserver.plan_affecting_convert
    (ACTION (sqlserver.sql_text)
     WHERE (sqlserver.equal_i_sql_unicode_string(sqlserver.database_name, N'AdventureWorks2017')))
    ADD TARGET package0.event_file
    (SET filename = N'C:\PerfData\ImplicitConversionOnly.xel');

Actions can be problematic if they are overused. Also, some actions have more overhead than others. However, in this case, it’s probably a smaller load than adding causality tracking.

You may still have queries that have CONVERT or CONVERT_IMPLICIT within them that won’t be picked up by this event. That’s because not all such conversions lead to problems within the plan. You may also see false positives where the optimizer fires the warning for conversions that don’t actually hurt performance.

Conclusion

As you can see, getting a handle on which of your queries may be experiencing issues because of implicit conversions is actually really easy with Extended Events. In fact, you can only capture this information with Extended Events. This ability is just one more reason why I so strongly recommend using Extended Events.

---

I teach an all day seminar where I show all sorts of tips and tricks with Extended Events and other SQL Server tools. I only have one more opportunity to share that information this year, in Munich on October 26, 2018. Go here now to join the class.

If you are running an event next year and you’d like to see this sort of class at your event, please get in touch. I’m starting to schedule next year right now.