0

I have a Stored Procedure with the hint WITH RECOMPILE, so the Stored Procedure do not store in the cache a Query Plan for the Stored Procedure. The average time the Stored Procedure resolve the Query is between 1 and 3 second, but from time to time the Stored Procedures takes > 30 seconds and sometimes it gets timeout. The strange thing here is that I run DBCC FLUSHPROCINDB and the Stored Procedure returns to its normal time (1 to 3 seconds).

Do you have any idea of what could be happening?

Community
  • 1
Santiago
  • 1

1 Answers1

3

The hint WITH RECOMPLILE is telling your database to rebuild the execution plan every time. This rebuild requires some overhead, and can explain why it takes an extended period of time in some circumstances. Determining exactly why and when the recompile takes longer will require more information from the system (ambient load, how much change there is in the tables being analyzed, table contention, etc).

A more detailed explanation can be found here. https://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why

Community
  • 1
Josh Bonello
  • 600
  • 4
  • 15
  • Thanks for the answer. I've some more questions. Let's say it's the recompile that it's taking too long, why is it possible that every time I keep executing it keeps taking that long? And why executing flushprocindb fixes the issue? – Santiago Jan 12 '17 at 22:21