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?