参数嗅探故障排除

Question

在完成TSQL中完成某些方面需要一些帮助。我正在收集SQL Profiler跟踪数据以获取特定的存储过程执行，并且基本上我试图从TextData列中剥离参数值以下数据集的示例，我需要将参数值划分为

exec test
exec test @aa=10
exec test @aa=10,@bb=10
exec test @aa=10,@bb=10,@cc=100
exec test @aa=10,@bb=1000,@cc=1

因此，输出表看起来像

aa   bb     cc
10   Null  NUll
10    10   NULL
10    10   100
10   1000   1

我只是想找出传递给SP的常见参数，因此，如果还有其他简单的方法，请告诉我。

Answer 1

我可以思考的几种方法。

1.使用计划缓存

select query_plan from sys.dm_exec_cached_plans cp
cross apply
sys.dm_exec_text_query_plan(cp.plan_handle,default,default)
where objtype='proc' and object_name(objectid)='usp_test'

现在，上述查询计划是XML，它将包含编译时使用的所有值。此过程非常cuber，您只会得到编译值。但是由于计划的缓存无效，因此可能由于许多原因而发生，这可能会导致许多情况。原因，您将随着时间的推移获得新价值

2.修改您存储的proc以插入其他表格中的所有参数值，例如以下

create proc usp_test
(
@a int=1,
@b int =2
)
as
begin
insert into sometable 
select @a,@b,getdate()
end

除上述方式外，我无法考虑获得传递的参数值（不包括您正在运行的跟踪）

如果您想通过收集所有参数值来解决参数嗅探的故障排除，那么这可能不是准确的方式

Answer 2

我了解是要克服参数嗅探问题。

您可以这样做，

create proc usp_test
(
@aa int,
@bb int ,
@cc int
)
as
begin
    DECLARE @aa1 INT
    SET @aa1 = @aa
    DECLARE @bb1 INT
    SET @bb1 = @bb
    DECLARE @cc1 INT
    SET @cc1 = @cc

select col1,col2 from testtable
    where col1=@aa1 and col2=@bb1
end

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

https://blogs.technet.microsoft.com/mdegre/2011/11/06/what-is-parameter-sniffing/