如何從 SQL Server 執行計劃節點獲取 EstimatedRows 值

當我在 Azure SQL 數據庫 (v12) 或 SQL Server 2016 實例中執行以下程式碼時，我在 EstimatedRowsRead 列中獲得值**，但在EstimatedRows**列中沒有，我不明白為什麼。有任何想法嗎？

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;

原因是一個小錯字，但我同意 Estimatedrows 似乎是要走的路。

在執行計劃的 XML 中，它被呼叫Estimaterows而不是預期的EstimatedRows.

一個例子：

EstimateRows="12"

這應該有效：

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;

結果範例

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

引用自：https://dba.stackexchange.com/questions/229551