CODEWITHSUNDEEP
sql-server-2005
Amutha
Amutha

Reputation: 115

When does a Stored Procedure recompile?(Sql Server)

I guess,

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

Kindly give me the other possibilites.

Upvotes: 4

Views: 7532

Answers (3)

MikeTeeVee
MikeTeeVee

Reputation: 19392

The other 2 answers here (by @KM. and @gbn) list reasons for SQL to "invalidate" the cached plan.

What I mean by "invalidate" is, something caused the existing plan to require a recompilation.

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

This could be due to:

  1. Memory pressure on the cache to make room for newly generated plans.
    Maybe you have a ton of random ad-hoc / prepared scripts that run, and it leaves little room to hold on to plans created for your stored procedures
  2. The server is reset/rebooted
    That'll clear out your tempdb and cache
  3. Someone set up a routine job to periodically clear out the cache
    (because they don't know what the heck they're doing)

Here is a great link on #3 Above (how clearing the cache may be done manually):

My takeaway from the link above was to search your jobs and stored procedures for anything containing:

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

I've worked in various databases and I've had the luck of seeing my stored procedure's cached plans last no more than 24 hours (usually a lot less), so by the next day, everything needs to be recompiled.

In my experience, caching works, but usually only for a few hours out of the work day, so never expect your plans to last beyond that.

Upvotes: 0

KM.
KM.

Reputation: 103579

Optimizing SQL Server Stored Procedures to Avoid Recompiles

  • dropping and recreating the stored procedure
  • using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement
  • changing the schema of any referenced objects
  • running the sp_recompile system stored procedure against a table referenced by the stored procedure
  • restoring the database containing the stored procedure or any object referenced by the stored procedure
  • the stored procedures plan dropping from the cache
  • Stored procedure will recompile if there is a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table.
  • Stored procedures will recompile if the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code.

Upvotes: 3

gbn
gbn

Reputation: 432180

It's all here: Execution Plan Caching and Reuse under the section "Recompiling Execution Plans"

Upvotes: 3

Related Questions