SQL Server – How to Get EstimatedRows Values from Execution Plan Nodes

azure-sql-databaseexecution-plansql serversql-server-2016xml

When I execute the following code in an Azure SQL Database (v12) or SQL Server 2016 instance, I get values in the EstimatedRowsRead column, but not in the EstimatedRows column, and I'm not seeing why. Any ideas?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,total_elapsed_time =   SUM(total_elapsed_time)
            ,execution_count    =   SUM(execution_count)
      FROM  sys.dm_exec_query_stats
     GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle, qp.query_plan, ph.total_elapsed_time, ph.execution_count
      FROM  PlanHandles ph
            OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT  p.total_elapsed_time
        ,p.execution_count
        ,p.plan_handle
        ,p.query_plan
        ,NodeId                 =   s.i.value(N'@NodeId', N'INT') 
        ,EstimatedRowsRead      =   s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT')
        ,EstimatedRows          =   s.i.value(N'(@EstimatedRows)[1]', N'FLOAT')
  FROM  Plans p
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') q(n)
        CROSS APPLY n.nodes('.//RelOp[IndexScan and IndexScan/Object[@Schema!="[sys]"]]') s(i)
 WHERE  s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT') > 1.0;

Best Answer

The reason is a small typo but I agree that Estimatedrows would seem like the way to go.

In the execution plan's XML it is called Estimaterows instead of the expected EstimatedRows.

An example:

EstimateRows="12"

This should work:

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
,PlanHandles AS (
    SELECT  plan_handle
            ,total_elapsed_time =   SUM(total_elapsed_time)
            ,execution_count    =   SUM(execution_count)
      FROM  sys.dm_exec_query_stats
     GROUP  BY plan_handle
)
,Plans AS (
    SELECT  ph.plan_handle, qp.query_plan, ph.total_elapsed_time, ph.execution_count
      FROM  PlanHandles ph
            OUTER APPLY sys.dm_exec_query_plan(ph.plan_handle) qp
)
SELECT  p.total_elapsed_time
        ,p.execution_count
        ,p.plan_handle
        ,p.query_plan
        ,NodeId                 =   s.i.value(N'@NodeId', N'INT') 
        ,EstimatedRowsRead      =   s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT')
        ,EstimatedRows          =   s.i.value(N'(@EstimateRows)[1]', N'FLOAT')
  FROM  Plans p
        CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') q(n)
        CROSS APPLY n.nodes('.//RelOp[IndexScan and IndexScan/Object[@Schema!="[sys]"]]') s(i)
 WHERE  s.i.value(N'(@EstimatedRowsRead)[1]', N'FLOAT') > 1.0;

Result example

NodeId  EstimatedRowsRead   EstimatedRows
3         530                1
4         1,93078            1
9         12                 12
8         12                 12

Azure SQL now directly supports this

Azure SQL Database directly supports clearing the proc cache of the current user database without any hacks:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Additional Information

The following script (by Shannon Gowen) can be used to watch the process step-by-step:

-- run this script against a user database, not master
-- count number of plans currently in cache
select count(*) from sys.dm_exec_cached_plans;

-- Executing this statement will clear the procedure cache in the current database, which means that all queries will have to recompile.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

-- count number of plans in cache now, after they were cleared from cache
select count(*) from sys.dm_exec_cached_plans;

-- list available plans
select * from sys.dm_exec_cached_plans;

Sql-server – SQL Server 2005 get execution plan of a overnight stored procedure

So is there a way I can see the execution plan of the proc or the statement in particular?

You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into.

Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with the "subplans" for each query comprising the batch).

Note: Restarting sql server will flush out all dmv data.

Example :

-- Do not run it in PROD !!!!
-- free up the procedure cache for TESTING ONLY !!
dbcc freeproccache

-- create an SP with different queries 
create procedure usp_Kin_Test
as
select name, create_date from sys.objects
where type = 'P'
order by create_date desc

select name, OBJECT_ID from sys.procedures
order by name

-- execute the query
exec usp_Kin_Test

--- Check the DMV's for cached query plan

SELECT deqp.dbid
    ,deqp.objectid
    ,CAST(detqp.query_plan AS XML) AS singleStatementPlan
    ,deqp.query_plan AS batch_query_plan
    ,ROW_NUMBER() OVER (
        ORDER BY Statement_Start_offset
        ) AS query_position
    ,CASE 
        WHEN deqs.statement_start_offset = 0
            AND deqs.statement_end_offset = - 1
            THEN '-- see objectText column--'
        ELSE '-- query --' + CHAR(13) + CHAR(10) + SUBSTRING(execText.TEXT, deqs.statement_start_offset / 2, (
                    (
                        CASE 
                            WHEN deqs.statement_end_offset = - 1
                                THEN DATALENGTH(execText.TEXT)
                            ELSE deqs.statement_end_offset
                            END
                        ) - deqs.statement_start_offset
                    ) / 2)
        END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
WHERE execText.TEXT LIKE '%usp_Kin_Test%' -- CHANGE here for your stored procedure !!

enter image description here

Note : You can do many cool things by digging into Plan cache as described by Jonathan here. Also refer to DMVs for Query Plan Metadata

Best Answer

Related Solutions

Sql-server – How to remove a bad execution plan from Azure SQL Database

Azure SQL now directly supports this

Additional Information

Sql-server – SQL Server 2005 get execution plan of a overnight stored procedure

Related Question