Sample Header Ad - 728x90

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

1 vote
1 answer
177 views
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)','INT') AS [CE_Version], qp.query_plan.value('(//@EstimateRows)', 'varchar(128)') AS [Estimated_Rows], qs.last_rows AS [Last Rows], --(qp.query_plan.value('(//@EstimateRows)', '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
Asked by Fza (652 rep)
Nov 18, 2016, 04:35 PM
Last activity: Jul 15, 2025, 05:10 PM