长时间延迟执行存储过程

Question

当我对服务器生产环境(8核)上的数据库执行SQL查询时，大约需要7秒才能获得2244行。

我有一个带有相同SQL查询的存储过程，当我对我的数据库执行它时，大约需要1分20秒才能获得相同的2244行。

我有一个具有相同SQL查询的表值函数，当我对我的数据库执行它时，大约需要1分20秒才能获得相同的2244行。

所以：

SQL查询:7秒

存储过程:1分20秒。

表值函数:1分20秒。

现在最奇怪的部分是：

在具有相同数据库的测试服务器(4核)上使用相同的场景，我得到以下分数：

SQL查询:1分20秒。

存储过程:7秒。

表值函数:7秒。

是不是我忘记了什么，导致了这么长的延迟？

Answer 1

根据我的经验

1)尝试在使用前将参数放入局部变量中。

create procedure [dbo].[usp_test](@Id varchar(20))
as
begin
  select * from Test
  where Id = @Id
end

到

alter procedure [dbo].[usp_test](@Id varchar(20))
as
begin
  declare @local_id varchar(20) = @Id

  select * from Test
  where Id = @local_id
end

2)使用重新编译提示。这将得到更适合基于参数值的查询的新的查询计划。

exec dbo.usp_test 1 with recompile

参考

：https://technet.microsoft.com/en-us/library/ms190439%28v=sql.105%29.aspx

：http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

Answer 2

经过长时间的测试和研究，我真的认为这是一个参数嗅探问题，因为我做了以下测试：

正常执行SQL查询:7秒。

declare @field_1 int = 1
declare @field_2 NVARCHAR(MAX) = null
declare @field_3 NVARCHAR(MAX) = null
declare @field_4 NVARCHAR(MAX) = null
declare @field_5 NVARCHAR(MAX) = null
declare @field_6 NVARCHAR(MAX) = null
declare @field_7 NVARCHAR(MAX) = null
declare @field_8 DATE = dateadd(month, -1, getdate())
declare @field_9 DATE = getdate()
select * ...

像这样执行TVF :7秒

declare @field_1 int = 1
declare @field_2 NVARCHAR(MAX) = null
declare @field_3 NVARCHAR(MAX) = null
declare @field_4 NVARCHAR(MAX) = null
declare @field_5 NVARCHAR(MAX) = null
declare @field_6 NVARCHAR(MAX) = null
declare @field_7 NVARCHAR(MAX) = null
declare @field_8 DATE = dateadd(month, -1, getdate())
declare @field_9 DATE = getdate()

select *
from fn_generar_reporte_cred(@field_1, @field_2, @field_3, @field_4, @field_5,
 @field_6, @field_7, @field_8, @field_9)

执行TVF :1分20秒

select *
from fn_generar_reporte_cred(1, null, null, null, null, null, null, dateadd(month, -1, getdate()), getdate())

正常执行SP :1分20秒

CREATE PROCEDURE [dbo].[pa_reporte_cred](
    @field_1 INT,
    @field_2 NVARCHAR(MAX),
    @field_3 NVARCHAR(MAX),
    @field_4 NVARCHAR(MAX),
    @field_5 NVARCHAR(MAX),
    @field_6 NVARCHAR(MAX),
    @field_7 NVARCHAR(MAX),
    @field_8 DATE,
    @field_9 DATE
) AS
BEGIN
SELECT * ...

正常执行SP (内部更改)：7秒

CREATE PROCEDURE [dbo].[pa_reporte_cred_ss](
    @field_1_ss INT,
    @field_2_ss NVARCHAR(MAX),
    @field_3_ss NVARCHAR(MAX),
    @field_4_ss NVARCHAR(MAX),
    @field_5_ss NVARCHAR(MAX),
    @field_6_ss NVARCHAR(MAX),
    @field_7_ss NVARCHAR(MAX),
    @field_8_ss DATE,
    @field_9_ss DATE
) AS
BEGIN
declare @field_1 int
declare @field_2 NVARCHAR(MAX)
declare @field_3 NVARCHAR(MAX)
declare @field_4 NVARCHAR(MAX)
declare @field_5 NVARCHAR(MAX)
declare @field_6 NVARCHAR(MAX)
declare @field_7 NVARCHAR(MAX)
declare @field_8 DATE
declare @field_9 DATE
SELECT @field_1 = @field_1_ss, @field_2 = @field_2_ss, @field_3 = @field_3_ss,
    @field_4 = @field_4_ss, @field_5 = @field_5_ss, @field_6 = @field_6_ss,
    @field_7 = @field_7_ss, @field_8 = @field_8_ss, @field_9 = @field_9_ss
SELECT * ...

感谢大家的帮助！