1

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
3
  • 1
    Use float instead. Nov 18, 2016 at 16:43
  • Thanks for pointing me in the right direction @sp_BlitzErik , got it now. May your front squats forever be clean and strong.
    – Fza
    Nov 18, 2016 at 16:56
  • 1
    No problem. There's actually a bit more involved version of your query as a feature request for BlitzCache over here: github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/… -- As for the front squats: don't tell anyone, I just do leg presses instead :) Nov 18, 2016 at 16:59

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.