question

Usually when you have a procedure that is sometimes running good and other times it's not, it's usually one of two causes. Either the statistics are going out of date and you're getting bad plans based on bad statistics, which is then fixed when the statistics update. Or, the alternative is that you're experiencing the bad side of parameter sniffing. This is when the values passed to a procedure are used against the statistics to identify a plan that doesn't work well for all possible values passed to the procedure. First, try updating the stats manually. If that fixes the issue, you're done. If not, it's parameter sniffing. You'll need to break down the procedure and try one of the many methods of fixing it, such as using local variables, or using the OPTIMIZE FOR query hint.

Great answer by Grant (+1)! I would like to add that if you are experiencing problems with your stats getting out of date, then you are possibly not doing (enough) pro-active index and statistics maintenance. If a manual stats update fixed the problem, then this would further prove that point. Please take a look at [Ola Hallengren's excellent database maintenance scripts][1], they make the whole process a little easier for DBAs to do the standard maintenance tasks in a better way than the cruddy maintenance plans from SSMS. [1]: http://ola.hallengren.com/

Make sure you don't alter any parameters inside the procedure based on some conditional logic: CREATE PROC dbo.MyProc @myParam int AS IF something=aValue SET @myParam=toSomethingElse SELECT something FROM someTable WHERE someColumn=@myParam --or this is also a bad idea IF something=aValue SELECT something FROM someTable WHERE someColumn=@myParam ELSE SELECT something FROM someTable WHERE someColumn = SomeOtherCondition Go The procedure is optimized when the procedure is compiled and if the parameter is changed, the execution looks almost the same but the new value of the paramater may cause an unefficient queryplan or cause a recomplie of the procedure.