CODEWITHSUNDEEP
sqlsql-serversql-server-2012
kudlur
kudlur

Reputation: 1213

SQL Server 2012 stored procedure runs slow

I have a SQL Server stored procedure and it runs fine on SQL Server 2008 R2.

When I try to run it on SQL Server 2012, it takes very very long time to run.

But if I create local variables inside the stored procedure and copy the values of input parameters into those local variables and use them instead of input parameters, query runs and returns result faster than on SQL Server 2008 R2 database (please note both 2008 R2 and 2012 servers run on the same box).

Could you please shed some light on what is going on here?

Upvotes: 1

Views: 145

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

By creating local variables and rebinding values you disable parameter sniffing:

Parameter sniffing is the process whereby SQL Server creates an optimal plan for a stored procedure by using the calling parameters that are passed the first time a stored procedure is executed

Every subsequent call to the same store procedure with the same parameters will also get an optimal plan, whereas calls with different parameter values may not always get an optimal plan

Run slows:

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS
BEGIN
  SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @StartDate AND @StopDate; 
END

Run fast (because it has to calculate new execution plan each time):

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS
BEGIN
  DECLARE @StartDate DATETIME = @BeginDate;
  DECLARE @StopDate DATETIME = @EndDate;

  SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @StartDate AND @StopDate; 
END

The case is SQL Server optimizer cannot reuse cached plan and evaluate it each time.

This is the same as you use WITH RECOMPILE:

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
WITH RECOMPILE
AS
BEGIN
  SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @StartDate AND @StopDate; 
END

using query hint:

CREATE PROC [dbo].[DisplayBillingInfo]
  @BeginDate DATETIME,
  @EndDate DATETIME
AS
BEGIN
  SELECT BillingDate, BillingAmt
  FROM BillingInfo
  WHERE BillingDate between @StartDate AND @StopDate
  OPTION(RECOMPILE);
  -- OPTION (OPTIMIZE FOR (@StartDate UNKNOWN, @StopDate UNKNOWN))
END

Upvotes: 7

Related Questions