Speedier Execution of Stored Procedures in SQL Server

I started a new job about six months ago, and with that job I inherited a mission-critical SQL Server 2005 database with over 800 stored procedures and tens of millions of records.  I also inherited what appeared to be a growing problem:  certain of the more complex stored procedures, when called from our custom in-house software, would cause long hang-ups for dozens of users.  As our database grew larger and larger, these hang-ups were becoming more frequent and lasting longer.  And yet those same queries from the stored procs, with the same parameter values, ran quickly and without problems if I used Query Analyzer in the SQL Server Management Studio.

Hours of research finally paid off, and I was introduced to a phenomenon known as "parameter sniffing."  Now, I'm relatively new to SQL Server and T-SQL, and so I know this is a much more complex issue than I'm able to comprehend at present.  Nevertheless, as a "young" database administrator writing to other "young" DBAs, let me do my best to explain parameter sniffing, while going easy on the heavy terminology.

When you first create a stored procedure (SP), SQL Server makes what is known as a "query plan" based on how your SP is expected to execute.  It's a way for SQL Server to anticipate and therefore optimize the SP's execution.  As Martha Stewart would say, it's a good thing.  

Now introduce parameters into the mix.  Parameters (or passed variables) allow you to use a single SP to execute multiple similar queries.  But when your SP accepts one or more parameters, there is much more potential for the SP's actual execution to vary from the query plan SQL Server used (and stored) at the time of writing the stored procedure (more accurately, last time it was compiled or recompiled).

That's where parameter sniffing comes in.  To the best of my understanding, and to keep the explanation simple, parameter sniffing is a SQL Server feature that considers an SP's parameter values and from there tries to optimize execution by estimating how many rows will be processed at each stage of the query plan.  

Again, in many cases, this is a good thing.  However, the more complex your SP, and the more widely your parameter values range from what was in use when the SP was created, the more potential there is for parameter sniffing to actually slow down an SP's execution rather than speed it up.

This seemed to be exactly the problem my company faced.  Many of the key processes in our home-grown software relied upon highly complex stored procedures, SPs whose execution behavior varied widely depending on user input (input that became SP parameters).

So how does one go about disabling parameter sniffing in such a situation? While there are a few strategies available (e.g. using the "Optimise For" query hint), there is one approach which is fairly quick and easy to do, and most importantly, easy to achieve as a "young DBA". Simply declare a fresh variable for each of your parameters and transfer all parameter values to these new variables; throughout the rest of the stored proc, use only these new variables.  So, for example, if you're starting with an SP like this one:

CREATE PROCEDURE [dbo] .[StoredProcName]
                      @Param1 AS varchar (25)
                       
                        AS
                       
                      SELECT * FROM MyTable WHERE MyName = @Param1
                      

Select all Open in new window

You should adapt it like so:

CREATE PROCEDURE [dbo] .[StoredProcName]
                      @askParam1 AS varchar (25)
                       
                        AS
                       
                      DECLARE @Param1 AS varchar (25)
                      SET @Param1 = @askParam1
                         
                      SELECT * FROM MyTable WHERE MyName = @Param1
                      

Select all Open in new window

(Note that an SP as simple as this one is unlikely to suffer from parameter sniffing; I use it only to demonstrate how to disable the sniffing).

It is astounding the effect this simple change has had on speed of execution for our most complex and frequently used SPs.  In many cases, dozens of users had been experiencing minute-long delays while a single user executed one of these SPs; immediately after the changes, we would watch as execution time drop to a few seconds, with no noticeable delay for other users.  

I should note, of course, that simply recompiling an SP, and with it a new query plan, can be enough to cause a speed improvement.  However, because many of the SPs I'm referring to are so complex, the parameters they accept are usually going to have atypical values in light of the query plan, even if it is a brand new plan. So, in treating the symptoms of parameter sniffing as a strategy, and having to recompile the procedures anyway, our problems were solved with an immediate performance improvement.

To consider if you may suffer the negative effects of parameter sniffing in some cases, or slow running procedures, then start by looking at your parameters.  In my short experience with this issue, I believe the following factors contribute to execution delays; the more extreme these factors, the lengthier the potential delay:


Before concluding this article, let me demonstrate one extreme example of complexity in an SP.  A procedure like this one, which is in fairly common usage at my company, would have wildly different execution behavior depending on the value of its first parameter:

CREATE PROCEDURE [dbo] .[StoredProcName]
                      @Param1 AS int
                       
                        AS
 
                      IF @Param1 = 1
                        BEGIN
                          SELECT * FROM MyTable1
                        END
                      ELSE IF @Param1 = 2
                        BEGIN
                          SELECT Field1, Field2 FROM MyTable2 WHERE Field3 = 'xyz'
                        END
                      ELSE
                        BEGIN
                          SELECT "Invalid Argument!" as ErrorMessage
                        END
                      

Select all Open in new window

The argument could be made that an SP like this should not exist, that it should be split into multiple SPs. There are a couple of strategies that suggest that breaking up your stored procedure into smaller functions or procedure.  However, there are at least two obvious instances in which an SP of this variety is valid:

1

when the IF/ELSE code is only part of a much larger procedure, or

2

when your procedure is later expanded to allow for situations not considered when it was originally written, and yet the SP cannot be split due to calls from legacy software.---

To wrap this article up, let me express again that this is a much more complex issue than I have presented here.  However, I can also say that this is one of those situations where "the proof is in the pudding."  If you're experiencing delays in execution of certain stored procedures, and it looks like the particulars match what I've described here, it will do no harm to disable parameter sniffing in the manner described above.  And if you experience a lasting improvement in speed of execution, that answers your question.

If you're interested in more information on parameter sniffing and optimization in general, please see the following articles.  The first article in particular, suggested by expert mark_wills is excellent, though a bit above my level at present:

If you have any further discussion to add, please do so.  I'm interested in hearing other perspectives on this issue, and I'm especially interested in improving my knowledge as a database administrator.