3

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
2
  • That definitely sounds like what could be going on. The stored procedure in question has 6 or so parameters that can be passed in that cause the stored procedure to execute different sql. The SP is setup to use filters. If A is supplied do this, if B is supplied then look at C for more info, etc. Are there any real "rules" for which sps should be run "with recompile" set? I want to be careful not to cause more delay from the recompile than can be caused by a stale plan.
    – Equixor
    Jul 27, 2011 at 21:40
  • If you've got a procedure that behaves like the one you have (i.e. performance falls off a cliff), then I'd say that qualifies. However, you have an advantage in that this is a stored procedure (as opposed to something coming from an ORM as ad hoc). If the number of branches is low, you could write a sproc for each branch and have your original sproc call the sub-procedures based on the parameters. Each of the sub-procedures should have stable performance, which could make your situation better.
    – Ben Thul
    Jul 28, 2011 at 2:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .