Início / dba / Perguntas / 184111
Accepted
SpaceGhost440
Asked: 2017-08-23 11:14:41 +0800 CST

Analisar programaticamente os parâmetros de procedimentos armazenados

  • 772

Eu tenho tentado descobrir como verificar os parâmetros de procs armazenados do plano de execução. Muitas vezes vejo que a maioria das pessoas sugere usar o ParameterList dentro do xml do plano de execução. Isso funciona bem quando há apenas uma instrução que usa todos os parâmetros. Entretanto, este normalmente não é o caso. Os parâmetros do procedimento armazenado podem ser distribuídos em várias consultas ou não serem usados ​​em uma consulta.

Nos meus exemplos abaixo, gostaria de saber como obter os parâmetros distintos e seus valores do exemplo usando funções sql xml. Além disso, você notará que nem todos os parâmetros são enviados para ParameterLists.

/***********************************************************************/
/*EXAMPLE: you have to scan all of the selects parameter lists to match up the parameter names to the proc parameters */
/***********************************************************************/
IF OBJECT_ID (N'dbo.TestFooProc') IS NULL
   EXEC('CREATE PROCEDURE dbo.TestFooProc AS SET NOCOUNT ON;');
GO

ALTER PROCEDURE dbo.TestFooProc (
    @p1 int,
    @p2 int,
    @p3 int = 8675309
) AS 
BEGIN
    --distribute the parameters over multiple selects
    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p2;

    -- other simulated queries ...

    SELECT * FROM master.dbo.[spt_values] sv WHERE [sv].[number] = @p1;

END
GO

EXEC dbo.TestFooProc @p1 = 5, @p2 = 22

SELECT [db_name] = DB_NAME([eps].[database_id]), qt.[text], qp.[query_plan]
FROM  sys.[dm_exec_procedure_stats] eps
CROSS APPLY sys.[dm_exec_sql_text](eps.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(eps.plan_handle) AS qp
WHERE [eps].[database_id] = DB_ID() 
    AND qt.[text] LIKE '%dbo.TestFooProc%'


RETURN  -- CLEANUP

IF OBJECT_ID (N'dbo.TestFooProc') IS NOT NULL
   DROP PROCEDURE dbo.TestFooProc; 
GO
sql-server execution-plan

2 respostas

  1. Ok, depois de muito pesquisar sobre isso, determinei que isso nem sempre é possível. Detalharei os seguintes cenários que decompõem a capacidade de obter os parâmetros originais do plano de execução.

    • Se o parâmetro não for usado em uma cláusula where. Cenários:
      • O parâmetro é colocado em uma variável local como uma maneira de contornar o sniffing de parâmetros
      • O parâmetro é usado em um cálculo em uma variável local
    • Se o parâmetro for usado em uma cláusula where, mas uma função for usada nele
    • Se o parâmetro for usado em várias instruções sql no proc armazenado, ele aparecerá em várias listas de parâmetros.

    Eu só queria uma lista de todos os parâmetros originais passados ​​para o proc armazenado e seus valores da criação do plano original para que eu pudesse recriar a chamada, se necessário. Eu sei que poderia obter essas informações usando eventos estendidos ou criador de perfil, mas estava tentando extraí-las dos planos de consulta.

    • 0
  2. Best Answer

    Isso é o mais próximo que cheguei de extrair os parâmetros e seus valores do plano. No entanto, essa consulta não consegue obter os parâmetros 100% das vezes devido aos motivos listados acima.

    EDIT: Removida a consulta postada anterior. Esta versão agrupa todos os parâmetros e os encontra em qualquer nível no plano. Isso é o mais próximo do que eu queria originalmente. Também ignora se os parâmetros estão em um StmtSimple ou um StmtCond:

    ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT qs.[sql_handle], qt.[text], qp.[query_plan], [ParamXml] = CAST(fnParams.[ParamXml] AS xml)
FROM sys.[dm_exec_procedure_stats] qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
OUTER APPLY (
    SELECT [ParamXml] = (
        SELECT [@Parameter] = t1.[Column], [@Value] = t1.[Value] 
        FROM (
            SELECT [Column] =  CAST(stmts.stmt.value('@Column', 'varchar(8000)') AS varchar(8000)),  
                [Value] = CAST(stmts.stmt.value('@ParameterCompiledValue', 'varchar(8000)') AS varchar(8000))
            FROM qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements//ParameterList/ColumnReference') AS stmts(stmt)
            WHERE stmts.stmt.value('count(.)', 'int') > 0 
        ) t1 
        GROUP BY t1.[Column], t1.[Value]
        FOR XML PATH('Parameter'), ROOT('Parameters')
    )
) fnParams
    • 0

relate perguntas