Microsoft Sql Server stored procedures – don’t trust in sql server optimization

 

 

It is an understood thing that Microsoft SQL Server (as many dbms) makes optimizations in the background for the user to try make queries faster. Well, it’s a nice and good thing and of course if we spend less time for optimization we got more time for beer. We could be happy at this point, but let see what happens when using stored procedures.

Sql server generates a so-called ‘execution plans’ and executes queries by them. It stores these plans and decides by them which indexes will be used at the next running.

The problem starts at stored procedures that contains optional parameters. In that time we don’t need to give all of the parameters. In that case we should need different query plans with different given parameters.

When sql server executes a stored procedure it makes a single query plan for each stored procedures based on the given parameters at the first run. The server uses this plan for all runs and generates new query plan only in this cases:

executing stored procedure with WITH RECOMPILE option

making schema changes on any objects depending on stored procedures

executing sp_recompile on a table referenced by the stored procedure

on cross database operations restoring any dependent object

making a given number of server activities

…

Let’s see a simple example for query plan caching (using northwind sample database):

 

create procedure [dbo].[GetCustomersStatic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    set @companyName = '%' + @companyName + '%'  
    select CustomerID, CompanyName
    from Customers
    where
        (@customerID is null or CustomerID = @customerID)
        and (@companyName is null or CompanyName like @companyName)
end

go

exec dbo.GetCustomersStatic @companyName = N'a'
exec dbo.GetCustomersStatic @customerID = N'BONAP'

Let’s see the actual execution plans:

As we can see sql server uses the same query plan for both executing: it uses the index for company name when user searches by ID.

 

The best solution that I found and has well expressed had written by Erland Sommarskog (Sql server MVP). He says we can solve it this ways:

Static sql. Use WITH RECOMPILE option when executing stored procedure. At this time we make sql server to recompile the stored procedure and it must generate a brand new query plan. In that case the problem is given when we call the procedure many times. Recompile needs processor time and many recompiles causes performance problems.

-- Static sql with recompile
exec dbo.GetCustomersStatic @companyName = N'a' with recompile
exec dbo.GetCustomersStatic @customerID = N'BONAP' with recompile

2.: Dynamic sql. Create a query string by hand dynamically and run it with sp_executesl:

CREATE procedure [dbo].[GetCustomersDynamic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    declare @query nvarchar(max)
            , @paramTypes nvarchar(100)

    set @paramTypes = N'@customerID nchar(5), @companyName nvarchar(40)'
    set @query = N'
    select CustomerID, CompanyName
    from Customers
    where 1 = 1'
    
    if @customerID is not null
        set @query = @query + N'
        and CustomerID = @customerID'
        
    if @companyName is not null
    begin
        set @companyName = '%' + @companyName + '%'
        
        set @query = @query + N'
        and CompanyName like @companyName'
    end
    
    exec sp_executesql @query, @paramTypes, @customerID, @companyName
end

go

-- Dynamic sql
exec dbo.GetCustomersDynamic @customerID = N'BONAP'
exec dbo.GetCustomersDynamic @companyName = N'a'

The complete source code is:

USE [Northwind]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:09 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersStatic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersStatic]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[GetCustomersStatic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    set @companyName = '%' + @companyName + '%'  
    select CustomerID, CompanyName
    from Customers
    where
        (@customerID is null or CustomerID = @customerID)
        and (@companyName is null or CompanyName like @companyName)
end


GO


GO

/****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:08:22 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersDynamic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersDynamic]
GO

/****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:08:22 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[GetCustomersDynamic]
    @customerID nchar(5)        = null
    , @companyName nvarchar(40)    = null    -- both parameters are nullable
as begin
    declare @query nvarchar(max)
            , @paramTypes nvarchar(100)

    set @paramTypes = N'@customerID nchar(5), @companyName nvarchar(40)'
    set @query = N'
    select CustomerID, CompanyName
    from Customers
    where 1 = 1'
    
    if @customerID is not null
        set @query = @query + N'
        and CustomerID = @customerID'
        
    if @companyName is not null
    begin
        set @companyName = '%' + @companyName + '%'
        
        set @query = @query + N'
        and CompanyName like @companyName'
    end
    
    exec sp_executesql @query, @paramTypes, @customerID, @companyName
end
GO


go

exec dbo.GetCustomersStatic @companyName = N'a'
exec dbo.GetCustomersStatic @customerID = N'BONAP'


-- Solutions

-- Static sql with recompile
exec dbo.GetCustomersStatic @companyName = N'a' with recompile
exec dbo.GetCustomersStatic @customerID = N'BONAP' with recompile

-- Dynamic sql
exec dbo.GetCustomersDynamic @customerID = N'BONAP'
exec dbo.GetCustomersDynamic @companyName = N'a'

GO

/****** Object:  StoredProcedure [dbo].[GetCustomersStatic]    Script Date: 12/30/2010 15:09:29 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersStatic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersStatic]
GO


GO

/****** Object:  StoredProcedure [dbo].[GetCustomersDynamic]    Script Date: 12/30/2010 15:09:46 ******/
  IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomersDynamic]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomersDynamic]
GO