12 Answers. ALTER will also force a recompile of the entire procedure. Statement level recompile applies to statements inside procedures, eg. a single SELECT, that are recompiled because the underlying tables changes, w/o any change to the procedure.

What causes stored procedure to recompile?

There are multiple reasons why a recompilation can occur. A recompile of an execution plan could be due to database level configuration changes, schema changes, index changes, etc. ... dm_os_performance_counters SQL Server DMV and filter on counter_name for the database. We can also use Extended Events and Profiler.

How do I force a stored procedure to recompile?

To recompile a stored procedure by using sp_recompile

Select New Query, then copy and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.