When is recompilation of a stored procedure performed? (Sql Server)

Question

I think,

When I use a temporary table in a Stored Procedure, the stored procedure will be automatically recompiled.

Please give me other options.

Answer 1

Optimize SQL Server Stored Procedures to Avoid Recompiling

• deleting and restoring a stored procedure
• using the WITH RECOMPILE clause in a CREATE PROCEDURE or EXECUTE statement
• changing the layout of any reference objects
• start the sp_recompile system stored procedure for the table referenced by the stored procedure
• restoring a database containing a stored procedure or any object referenced by the stored procedure
• stored cache plan descending from cache
• The stored procedure will be recompiled if the number of rows in the table referenced by the stored procedure has changed. SQL Server recompiles the stored procedure to ensure that the execution plan has updated statistics for the table.
• Stored procedures will be recompiled if the developer moves data definition operations using the data language using data manipulation operations. This usually happens when temporary objects are created and referenced in all code.

Answer 2

Everything is here: Caching and reusing the execution plan in the section "Recompiling execution plans"

Answer 3

The other two answers here (@KM. And @gbn) list the reasons why SQL "invalidates" the cached plan.

What I mean by "cancellation" is that the existing plan required a recompilation.

However, I believe the reason for # 1 recompilation is that the cached plan no longer exists.

This may be due to:

1. Memory pressure on the cache to make room for newly created plans.
   Maybe you have a ton of random / prepared scripts that work, and that leaves little room for plans created for your stored procedures
2. Server rebooted / rebooted
   This will clear your tempdb and cache.
3. Someone set up routine work to periodically clear the cache
   (because they don’t know what the hell they are doing)

Here is a great link to # 3 above (how clearing the cache can be done manually):

• https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/

My link to the link above was to look for your jobs and stored procedures for anything containing:

1. "DBCC FREE"
2. "DBCC FLUSH"
3. "CLEAR PROC"

I worked in various databases, and I was lucky to see that my cached stored procedure plans last no more than 24 hours (usually much less), so the next day everything should be recompiled.

In my experience, caching works, but usually only within a few hours of a business day, so never expect your plans to go beyond this.