Using MSSQL 2005
Today I was called in to look at a stored procedure that started executing slowly when used from our program. Around the order of a couple seconds where it used it execute instantly. I ran SQL Server Profiler on it and it was using 1000+ in CPU and over 400,000 Reads. I copied the Exec line from SQL Server Profiler to SQL Management Studio to look at the Execution plan. The stored procedure executed instantly and returned the correct results. Execution plan looked correct, no glaring errors. I tried running it multiple times from our program to see if maybe it wasn't cached and I was just seeing a first run slowdown as the execution plan was determined, but it was consistent at 2-3 seconds per run.
For kicks I then ran DBCC FreeProcCache just to see if that would make my runs from SQL Management Studio run slower. Ran the stored procedure from Management Studio and it still ran instantly. I then ran it from the program again with profiler running, and it too ran instantly. The profiler showed the CPU had dropped to 0 and the Reads had dropped to 40. It seems to be staying consistently fast now.
Why would running DBCC FreeProcCache speed up a stored procedure so drastically?
Best Answer
The short answer: parameter sniffing.
The long answer: when you first run a stored procedure, the query optimizer looks at the parameters passed to it and metadata about the objects being queried. If that metadata changes significantly over time (e.g. the statistics histogram changes), or if the initial parameters are not representative of a typical call, the plan that the optimizer caches can become sub-optimal. By doing a freeproccache, you get rid of the "bad" plan and force the query optimizer to take another run at it.
If you can find out which stored procedure is the problem child, you can add "with recompile" to the definition of the procedure and it won't cache the plan for that procedure. If you can find the statement within the procedure that's causing the problem, you can add "option (recompile)" to it and only that statement will be recompiled when the procedure runs.