Find All Queries with Implicit Conversion in SQL Server – Interview Question of the Week #107

Question: How do I find out how many queries have an Implicit Conversion in SQL Server?

Answer: The source of this question is very interesting.  However, we discuss that, let us first see the query.

 -- (c) https://blog.sqlauthority.com
SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], 
t.text AS [Query Text],
qs.total_worker_time AS [Total Worker Time], 
qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
qs.max_worker_time AS [Max Worker Time], 
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 
qs.max_elapsed_time AS [Max Elapsed Time],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads], 
qs.execution_count AS [Execution Count], 
qs.creation_time AS [Creation Time],
qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE ('%CONVERT_IMPLICIT%')
 AND t.[dbid] = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

The last column of this query also displays the execution plan from the cache.

If you want to see this query in action, you can read my blog post with video here: 3 Common Mistakes to Kill SQL Server Performance

The matter of fact, more than the interview question, this is a very popular question, I receive when going to SQL Server Consulting. As a consultant, I need to make money and to make money, I need to provide high-quality output to my customers. It is also very critical that my customer start trusting me immediately as that is the root element of the long term relationship.

There are many different ways to answer this question. For example, to find the answer to this question one can depend on extended events, profiler, traces etc. However, I use a bit different method to get started to find how many queries are doing the implicit conversion.

Instead of going to any other method where I have to make changes in the server, I directly run the following query and the query returns all the necessary information for me to get started. I always take the first query and along with their developer start fixing it. As soon as I fix their most expensive query and able to show them the performance improvement, I have noticed that my customer immediately starts trusting me.

Here is one question which you should be thinking when reading this blog post-

What is the disadvantage of Implicit Conversion for any query?

Whenever any query has to go through implicit conversion on any column, it leads to poor performance because it will have to convert all the rows from that single column before the comparison.

Additionally, please note that the query listed above is from SQL Server Cache. This means, if due to any reason, the cache is cleared, the result will change. Though this is a limitation, this is a great starting point for any performance tuning exercise. You may connect with me on twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)