Shredding Query Plan XML For Potential Skewed Estimates - Data Type Conversions

Question

I'm using the below T-SQL to check for root level estimate skews in execution plans by pulling the estimated rows from the execution plans and comparing to the last rows from the sys.dm_exec_query_stats DMV. Obviously I can't do this calculation due to the data type mismatch, converting from varchar to bigint won't work in this scenario. Is there any way around this?

SELECT  DB_NAME(qt.dbid) AS [Database], cp.objtype AS [Cached_Plan_Type],
    cp.usecounts AS [Use_Counts],
    qp.query_plan.value('(//@CardinalityEstimationModelVersion)[1]','INT') AS [CE_Version],
    qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)') AS [Estimated_Rows],
    qs.last_rows AS [Last Rows],
    --(qp.query_plan.value('(//@EstimateRows)[1]', 'varchar(128)') - qs.last_rows) AS [Estimate_Skew], 
    qs.total_logical_reads / qs.execution_count AS [Avg_Logical_Reads],
    CAST((qs.total_elapsed_time ) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Average Execution time(s)],
    CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU Time Average (s)],
    qt.text AS [SQL_Statement],
    qs.query_hash AS [QueryHash],
    qp.query_plan AS [QueryPlan]
FROM sys.dm_exec_cached_plans cp WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
    CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt
    INNER JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
WHERE   qt.text NOT LIKE '%sys.%'
ORDER BY [CPU Time Average (s)] DESC OPTION(RECOMPILE);
GO

Answer 1

If you can't do it in one query, then put the results from the first query into a #temporary table. Now do a second query to do the math.