Bust the Cache "With Recompile".

In SQL Server, an execution plan is a calculation of the most efficient way to perform a query (or set of queries).  So the database engine doesn't have to recalculate the optimal execution plan each time the query is requested, there is Execution Plan Caching.  The cache is just part of the database memory pool that stores the query plan and execution context.

It sounds great, but there is the potential to create a problem.  As the database schema evolves and the actual data changes, it's possible that some cached execution plans are no longer optimal and need to be recalculated.

A recent project involved an database that suddenly doubled it's data volume after a year of operation.  This was expected, as once a year a new fiscal year's worth of data is generated.  The stored procedures, being consumed by reports, became unresponsive and timed out around the same time the database was growing.

Because the data was significantly changed from when the execution plan was cached, and the database schema may have evolved over the year, the performance of the old execution plan needed refreshed.

To prove this cached execution plan was at fault, I ran tests with and without the optional WITH RECOMPILE.

EXECUTE usp_MyStoredProc 'myParamVal'; 

EXECUTE usp_MyStoredProc 'myParamVal'  WITH RECOMPILE;

Using WITH RECOMPILE this way tells the database engine to ignore the cached execution plan this time only - it does not recompile the stored procedure to recreate the execution plan.  As expected, the first execute statement took longer than I was willing to wait, so I stopped the query.  The second execute statement, using WITH RECOMPILE, executed very quickly, validating the cached execution plan was faulty.

The long-term solution for me was to rebuild my stored procedure using WITH RECOMPILE. This directs the stored procedure to never use a cached execution plan, and to recalculate the optimal plan each time it is called.  This may, or may not, be the right solution for you.  As the linked WITH RECOMPILE document states, you may use sp_recompile to recompile a stored procedure the next time it is run.  Using this option, all subsequent calls to the procedure will use the cached execution plan.