Whenever I have a client with an SQL 2000 database, and if they are experiencing performance issues (and even if they are not), I usually suggest they upgrade to SQL2005, or better yet SQL2008 (the second thing I suggest is more memory and faster disks, but that is another article altogether). After all, SQL2000 is getting close to 10 years old at this point, and 10 years is a very long time when you are talking technology. Needless to say, a lot of things have improved since SQL2000 was released.

Nonetheless, moving to SQL2008 (or 2005) is not without some issues. Recently I had a client undertake the suggested upgrade, and as expected, things immediately began to run much faster on the very same hardware. Well most things ran faster - in a few isolated cases, things that used to run perfectly fine, began to taking a very long time to process - a completely unexpected side-effect of the upgrade.

For this particular client, the performance issues were tracked to a handful of stored procedures that started taking 20-50 times as long to execute under SQL2005 (and 2008) as they did under 2000. After some research, and this article from Microsoft, I uncovered the wonderful world of "Parameter Sniffing":

"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans.

It is a complicated issue (read the full article linked above), but the solution is relatively simple. I have already implemented this in a few cases with dramatic results - stored procedures that were taking 15 or minutes to run would now complete in a matter of seconds, with just a few lines of code being changed in the stored procedure, and no need to change the client software - just the database.

The problem is SQL Server is using information that it gathers on the first run of your stored procedure, i.e. the parameters you passed in, to decide what will be the best execution plan for all time; while that may work in a lot of cases, it doesn't work for all cases and for those cases, you need a workaround.

The workaround for me was to not use the parameter variables that are passed within the logic of my proc, but instead to declare equivalent local variables, copy the parameter variables to the local variables - and then only use the local variables for the logic of your proc. It is a simple fix, and the result is that it defeats the bad execution plan that the SQL Server Optimizer would otherwise decide to use, and ther performance improvements can be huge in cases where this applies.

As an example, here is a part of a stored procedure, before I changed it  - pretty standard stuff: (please note this is just the top dozen lines of a 150+ line proc)

ALTER Procedure [dbo].[rptBankReconcile]
    @BankRecID     int = Null,
    @BankAccountID    int = Null 
AS
IF @BankAccountID Is Not Null
    SET @BankRecID = (SELECT Max(ID) FROM BankRec WHERE BankAccountID = @BankAccountID AND Posted = 1)
ELSE
    SET @BankAccountID = (SELECT BankAccountID FROM BankRec WHERE ID = @BankRecID)

---continued for 130+ lines ---

and now here is a look at the same section of code with the suggested changes. Note how I have used the same parameters in the stored procedure declaration (which prevents me from needing to change my client), but also declared local variables of the same type, and make a copy of the parameters passed in, into the 'local' variables, and then used those local variables to perform my processing:

ALTER Procedure [dbo].[rptBankReconcile]
    @BankRecID     int = Null,
    @BankAccountID    int = Null 
AS
  DECLARE @lBankRecID int
  DECLARE @lBankAccountID int

  SELECT @lBankRecID = @BankRecID, 
         @lBankAccountID = @BankAccountID 

IF @lBankAccountID Is Not Null
    SET @lBankRecID = (SELECT Max(ID) FROM BankRec WHERE BankAccountID = @lBankAccountID AND Posted = 1)
ELSE
    SET @lBankAccountID = (SELECT BankAccountID FROM BankRec WHERE ID = @lBankRecID)

---continued for 130+ lines ---

As a reminder, it is very important to not only declare local variables, and copy the values into them, but you also must update the code in the stored procedure to now use the local variables exclusively, instead of the parameters.

Use a global search and replace to find all the occurrences of the parameters, and swap them for the equivalent local variable. In my case, I declared all local variables with '@l' prefix, so that I can tell at a glance if the variable being used is a parameter, or a local copy of the parameter.

Make sure you make all the changes, because if you miss one (or more), the proc is still going to work, and it is still going to return the same results, but you may not benefit from the performance enhancements you are trying to accomplish, and you'll never know why.

So, the next time you client calls and complains that 'ever since you upgraded the database the system is running really slow', think about parameter sniffing, and use the information in this article for a quick fix.