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)
6 Comments. Leave new
You have one error in the select. On the second line, it starts with “t. as [query text]”. You have forgotten to put in t.text as [Query Text].
Thanks, fixed the same.
Hey, thanks for the tip :) It will help us in great extent.
Small correction to the query to include column name for [Query Text]
Thanks once again :)
is it OK now?
Hi,
Thanks for the script, it’s highlighted some really interesting stuff which I hadn’t considered.
From analysing my results, I can see the majority of CONVERT_IMPLICIT’s occur as part of the output i.e. the SELECT clause. For example
DECLARE @MyParam smallint
SELECT @MyParam = ID — integer column
FROM Customer
WHERE ID = 10
Aside from being an obvious mistake, this causes:
..resulting in an additional Compute Scalar operation, with an associated cost.
So no extra reads, but a cost nonetheless.
For some reason this got cut out:
ScalarOperator ScalarString=”CONVERT_IMPLICIT(smallint,[TR4_UAT].[dbo].[Client].[ID],0)”