Início / dba / Perguntas / 103688
Accepted
Craig Efrein
Asked: 2015-06-11 00:19:35 +0800 CST

Rastreando os parâmetros de um procedimento armazenado após a execução

  • 772

Pediram-me para identificar um problema de permissões com um procedimento armazenado. Esse procedimento armazenado se comporta de duas maneiras possíveis, dependendo de quais valores são usados ​​para seus parâmetros.

exec ps_my_stored_procedure @a=1, @b=2, @c=3

é tratado de forma muito diferente de

exec ps_my_stored_procedure @a=5, @b=7, @c=0

Você poderia dizer que ps_my_stored_procedureé dividido logicamente em dois processos completamente separados.

Usando dm_exec_procedure_statse dm_exec_query_stats, posso encontrar o plano de execução que mostra o SQL do procedimento armazenado usado. Não consegui, no entanto, recuperar como os parâmetros foram definidos e com quais valores.

É possível usar dm_exec_procedure_statse dm_exec_query_statsquaisquer outras visualizações de gerenciamento para reconstruir a execução do procedimento armazenado que mostra os valores usados ​​para seus parâmetros.

O que eu realmente gostaria é de encontrar no cache, é a execução real do procedimento armazenado para que eu possa executá-lo como está usando EXECUTE AS LOGIN = 'someone'para resolver os problemas de permissão

sql-server stored-procedures

2 respostas

  1. Costumo usar a seguinte consulta retirada de http://www.sommarskog.se/query-plan-mysteries.html#otherreasons para retornar os valores detectados. Outra maneira seria abrir o plano no SQL Sentry Plan Explorer e selecionar a guia de parâmetros. Tanto o método XML quanto o método do explorador de plano mostrarão os valores de tempo de execução assumindo que você está visualizando o plano de execução real.

    DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)

SELECT @dbname  = '',
       @procname = '' -- enter proc name

; WITH basedata AS (
   SELECT qs.plan_handle,
            qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT plan_handle,stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)

SELECT set_options AS [SET]
        , n.stmt_start AS Pos
        , n.Statement
       , CR.c.value('@Column', 'nvarchar(128)') AS Parameter
       , CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value]
       , CAST (query_plan AS xml) AS [Query plan]
       , n.plan_handle
FROM   next_level n
CROSS  APPLY   
        n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter
    • 11
  2. Best Answer

    Se você tiver o plano de execução, poderá visualizá-lo como xml e procurar valores de parâmetros compilados

    Por exemplo, este é um trecho real de um plano de execução de uma das consultas em meu cache de plano:

       <ParameterList>
      <ColumnReference Column="@P69" ParameterCompiledValue="'2015-06-10 00:00:00.0000000'" />
       <ColumnReference Column="@P68" ParameterCompiledValue="'somestring'" />
   </ParameterList>

    Se seus planos forem reutilizados, ele mostrará o valor no momento da compilação.

    • 5

relate perguntas