I was reminded today of something I solved a long time ago, something that TREMENDOUSLY impacts the performance of stored procedures in SQL Server.  This issue relates to 2000 & 2005; I have not tested this in 2008.

Basically, if your stored procedure is using date parameters AT ALL, the performance of the stored procedure will be HORRIBLE.  The whole purpose for using stored procedures is that they are much faster than using what is called a dynamic query, or a plain-text query.  The reason stored procedures are so much faster is that the database caches what is called an execution plan for the stored procedures, enabling the database to get at your data much faster.

For some reason, the use of date parameters prevents the caching of the execution plan, and even makes your stored procedure LESS efficient than a plain-text or dynamic query, because of the overhead of SQL trying to cache the execution plan.

So, the following stored procedure would NEVER get cached:

CREATE PROCEDURE dbo.storedProc1
   @dateParam1 datetime
   , @dateParam2 datetime
   , @otherParam nvarchar(50)
AS
   SELECT
      *
   FROM
      dbo.myTable (NOLOCK)
   WHERE
      dbo.myTable.dateField BETWEEN @dateParam1 AND @dateParam2
      AND dbo.myTable.otherField = @otherParam

HOWEVER, you can fake out SQL Server into caching the execution plan with the use of declared variables within your query.  Using the above example, the stored proc will now need to look like the following, which WILL allow for caching of the execution plan:

CREATE PROCEDURE dbo.storedProc1
   @dateParam1 datetime
   , @dateParam2 datetime
   , @otherParam nvarchar(50)
AS
   DECLARE @dateParam1_new datetime, @dateParam2_new datetime
   SET @dateParam1_new = @dateParam1
   SET @dateParam2_new = @dateParam2

   SELECT
      *
   FROM
      dbo.myTable (NOLOCK)
   WHERE
      dbo.myTable.dateField BETWEEN @dateParam1_new AND @dateParam2_new
      AND dbo.myTable.otherField = @otherParam

As you can see, I have only declared new variables and assigned them to the parameters of the proc.  This is all it takes to trick SQL Server into caching the execution plan.  Also, please notice that I only needed to do this on the DATE PARAMETERS, as they are the culprits.

I have made this a standard in all of my stored procedures that use date parameters, and the results are phenomenal.  For example, I had a complex report that was returning tens of thousands of rows, and it was taking more than 4 minutes to process the stored procedure, and by simply changing the above, the results are now under 45 seconds.

Keep in mind that the increased performance of the stored procedure is for the 2nd time through and thereon out, as the caching of the execution plan does not happen until the 1st time the stored procedure is ran.  Unfortunately, the execution plan is NOT cached when you CREATE or ALTER the procedure, but only after it is ran to return data.  If I am changing a heavy hitter, I typically run the procedure with parameters that will execute quickly, to force the caching so users are not impacted.

I hope this helps!  I am certain that many people face this problem, and don't know why.  I was helping a guy today who had indexed the hell out of a table, date columns included, and did not understand why the query would still run forever.  It won't matter what you try and do to the table or whatever.  STORED PROCEDURES WILL NOT CACHE THE EXECUTION PLAN WITH DATE PARAMETERS, UNLESS YOU FAKE IT OUT.

ENJOY!