SQL SERVER: Find high cost execution plans with implicit conversions

FindImplicitConversions.sql

/*
    Find cached query plans containing implicit conversions (CONVERT_IMPLICIT)
    Sorted by total subtree cost (descending), then total elapsed time (descending)
    
    Targets the plan cache via sys.dm_exec_query_stats + sys.dm_exec_query_plan
    Parses the XML query plan for CONVERT_IMPLICIT warnings and expressions
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH XMLNAMESPACES (
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
, PlanConversions AS (
    SELECT
        qs.plan_handle
       ,qs.sql_handle
       ,qs.statement_start_offset
       ,qs.statement_end_offset
       ,qs.creation_time
       ,qs.last_execution_time
       ,qs.execution_count
       ,qs.total_worker_time
       ,qs.total_elapsed_time
       ,qs.total_logical_reads
       ,qs.total_logical_writes
       ,qp.query_plan
       -- Extract the total subtree cost from the root RelOp
       ,qp.query_plan.value(
            '(//RelOp/@EstimatedTotalSubtreeCost)[1]', 'float'
        ) AS estimated_subtree_cost
       -- Count how many CONVERT_IMPLICIT expressions exist in the plan
       ,qp.query_plan.value(
            'count(//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")])', 'int'
        ) AS implicit_conversion_count
       -- Pull the first few CONVERT_IMPLICIT expressions for quick review
       ,qp.query_plan.value(
            '(//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]/@ScalarString)[1]', 'nvarchar(max)'
        ) AS sample_conversion_1
       ,qp.query_plan.value(
            '(//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]/@ScalarString)[2]', 'nvarchar(max)'
        ) AS sample_conversion_2
       -- Check for plan-level warnings (PlanAffectingConvert)
       ,CASE
            WHEN qp.query_plan.exist('//Warnings/PlanAffectingConvert') = 1
            THEN 1
            ELSE 0
        END AS has_plan_affecting_convert
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
    WHERE qp.query_plan IS NOT NULL
      AND qp.query_plan.exist('//ScalarOperator[contains(@ScalarString, "CONVERT_IMPLICIT")]') = 1
)
SELECT
    pc.estimated_subtree_cost
   ,pc.implicit_conversion_count
   ,pc.has_plan_affecting_convert
   ,pc.execution_count
   ,pc.total_elapsed_time / 1000                          AS total_elapsed_ms
   ,CASE
        WHEN pc.execution_count > 0
        THEN pc.total_elapsed_time / pc.execution_count / 1000.0
        ELSE 0
    END                                                    AS avg_elapsed_ms
   ,pc.total_worker_time / 1000                            AS total_cpu_ms
   ,CASE
        WHEN pc.execution_count > 0
        THEN pc.total_worker_time / pc.execution_count / 1000.0
        ELSE 0
    END                                                    AS avg_cpu_ms
   ,pc.total_logical_reads
   ,CASE
        WHEN pc.execution_count > 0
        THEN pc.total_logical_reads / pc.execution_count
        ELSE 0
    END                                                    AS avg_logical_reads
   ,pc.total_logical_writes
   ,pc.creation_time                                       AS plan_created
   ,pc.last_execution_time
   ,pc.sample_conversion_1
   ,pc.sample_conversion_2
   -- Extract the SQL text for the offending statement
   ,SUBSTRING(
        st.text
       ,(pc.statement_start_offset / 2) + 1
       ,CASE pc.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE (pc.statement_end_offset - pc.statement_start_offset) / 2
        END + 1
    )                                                      AS statement_text
   ,DB_NAME(st.dbid)                                       AS database_name
   ,OBJECT_NAME(st.objectid, st.dbid)                      AS object_name
   ,pc.query_plan                                          AS click_to_view_plan
FROM PlanConversions AS pc
CROSS APPLY sys.dm_exec_sql_text(pc.sql_handle) AS st
WHERE [pc].[estimated_subtree_cost] > 30
ORDER BY
    pc.estimated_subtree_cost DESC
   ,pc.total_elapsed_time DESC;