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?