Long delay executing stored procedure

Question

When I execute my SQL query against a database on server production (8 cores),it takes about 7 seconds to get 2244 rows.

I have an stored procedure with the same previous SQL query that when I execute it against my database, it takes about 1 minute and 20 seconds to get the same 2244 rows.

I have a table-value function with the same SQL query that when I execute it against my database, it takes about 1 minute and 20 seconds to get the same 2244 rows.

So:
SQL Query: 7 seconds
Stored procedure: 1 minute and 20 seconds.
Table-Value function: 1 minute and 20 seconds.

Now the weirdest part:

The same scenario on a test server (4 cores) with the same database, i get the following scores:

SQL Query: 1 minute and 20 seconds.
Stored procedure: 7 seconds.
Table-Value function: 7 seconds.

Is there something that I'm forgetting, which causes that long delay?

Answer 1

After long test and research, I really think this is a parameter sniffing problem, because I have done the following tests:

Execute SQL query normally: 7 seconds.

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 * ...

Execute TVF like this: 7 seconds

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)

Execute TVF like this: 1 minute 20 seconds

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

Execute SP normally: 1 minute 20 seconds

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 * ...

Execute SP normally (with inside changed): 7 seconds

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 * ...

Thank you all for your help!

Answer 2

From my experience
1) Try to put your parameter into local variable before use.

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

to

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) Use recompile hint. This will get new query plan which is more suitable for the query based on parameter value.

exec dbo.usp_test 1 with recompile

Ref
: https://technet.microsoft.com/en-us/library/ms190439%28v=sql.105%29.aspx
: http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html