Início / dba / Perguntas / 229551
Accepted
Mark Freeman
Asked: 2019-02-13 12:31:07 +0800 CST

Como obter valores EstimatedRows de nós do plano de execução do SQL Server

  • 772

Quando executo o código a seguir em uma instância do Banco de Dados SQL do Azure (v12) ou SQL Server 2016, recebo valores na coluna EstimatedRowsRead , mas não na coluna EstimatedRows , e não estou vendo o motivo. Alguma ideia?

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;
sql-server sql-server-2016

1 respostas

  1. Best Answer

    O motivo é um pequeno erro de digitação, mas concordo que Estimatedrows parece ser o caminho a seguir.

    No XML do plano de execução é chamado Estimaterowsem vez do esperado EstimatedRows.

    Um exemplo:

    insira a descrição da imagem aqui

    EstimateRows="12"

    Isso deve funcionar:

    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;

    Exemplo de resultado

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

relate perguntas