<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Speedier Execution of Stored Procedures in SQL Server

Published on
22,657 Points
8,857 Views
23 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
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

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

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:

complexity of stored procedure
the number of parameters in the SP
the number of times those parameters are referenced in the SP
the SP was created and tested with atypical parameters
the actual data referenced by the SP has changed over time and the query plan is no longer optimal

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

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.
23
Comment
Author:Bobaran98
9 Comments
LVL 61

Expert Comment

by:Kevin Cross
Bobaran98,

Gave your article its first plug to applaud you for your efforts.  :) Nice work so far!

Regards,
mwvisa1
0
LVL 21

Expert Comment

by:alainbryden
I think this article may be eclipsed by this recent one: http://www.experts-exchange.com/articles/Database/MySQL/3-Ways-to-Speed-Up-MySQL.html
0
LVL 21

Expert Comment

by:alainbryden
N/M, this deals with a specific case.
Perhaps consider renaming the article "Recovering from SQL performance issues due to Parameter Sniffing"
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

LVL 38

Expert Comment

by:younghv
Bobaran98,
I really enjoyed reading through your Article. Regardless of the discipline, I think all of us have been dropped into situations where we had to either sink or swim - and this might be exactly what some other young DBA needs to get something on track.
Enthusiastic yes vote above.
0
LVL 5

Expert Comment

by:Abiel de Groot
A very interesting article and a wake-up call. There was me thinking MS SQL was flawless ;-)
0
LVL 55

Expert Comment

by:Mark Wills
Just a small heads up in case you are not aware, MS has recognised the problem and making a few moves to better handle it  : http://support.microsoft.com/kb/980653/
0

Expert Comment

by:j_heck
Great article Bobaran98.  It caught my attention because of a problem I am currently experiencing why a stored procedure I have runs in 9 seconds with real dates and when parameters are added it takes over 5 minutes to run the same stored procedure.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28120553.html

Thanks,
John
0
LVL 15

Expert Comment

by:dbbishop
great article. Clear examples and explanations. Keep up the work.

Thanks,
Doug
0
LVL 8

Expert Comment

by:Yashwant Vishwakarma
Nice share !!! It will help a lot to us also... voted Yes..
0

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month