Is your stored procedure slower than an plain query?

On occasion when a query is wrapped in a store procedure the execution time is horribly slow.  This is due to a common issue known as “parameter sniffing” in SQL Server.  A couple of solutions are commonly recommended;

  1. Create local variables inside the stored procedure and assign the store procedure variables to the local ones.
  2. Add WITH RECOMPILE to the stored procedure right before AS.

However, with one of my stored procedures neither of these options fixed the performance problem.  I discovered another method using the query hint OPTIMIZE FOR UNKNOWN.   This hint causes the query optimizer to use the statistical data instead of the procedures initial values when then query is compiled.

Add the OPTIMIZE FOR UNKNOWN query hint using the OPTION keyword after the WHERE clause.

SELECT  FundNumber ,
        DepartmentNumber ,
FROM    OrderedAccounts
WHERE   ( @Limit IS NULL
          OR RowNumber <= @Limit

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.