主页 / dba / 问题 / 103688
Accepted
Craig Efrein
Asked: 2015-06-11 00:19:35 +0800 CST

执行后跟踪存储过程的参数

  • 772

我被要求确定存储过程的权限问题。此存储过程有两种可能的行为方式，具体取决于其参数使用的值。

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

处理方式与

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

您可以说这ps_my_stored_procedure在逻辑上分为两个完全独立的过程。

使用dm_exec_procedure_statsand dm_exec_query_stats，我可以找到显示使用的存储过程的 SQL 的执行计划。但是，我无法恢复参数的定义方式和值。

是否可以使用dm_exec_procedure_stats和dm_exec_query_stats任何其他管理视图来重建显示用于其参数的值的存储过程的执行。

我真正想要的是在缓存中找到存储过程的实际执行，以便我可以按原样执行它EXECUTE AS LOGIN = 'someone'来解决权限问题

sql-server stored-procedures

2 个回答

  1. 我经常使用从http://www.sommarskog.se/query-plan-mysteries.html#otherreasons获取的以下查询来返回嗅探值。另一种方法是在SQL Sentry Plan Explorer中打开计划并选择参数选项卡。假设您正在查看实际执行计划，XML 方法和计划资源管理器方法都将显示运行时值。

    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

    如果您有执行计划，您可以将其查看为 xml，并搜索已编译的参数值

    例如，这是来自我的计划缓存中的一个查询的执行计划的实际片段：

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

    如果您的计划被重用，它将在编译时显示该值。

    • 5

相关问题