What factors can cause Stored Procedure Recompilation on SQL Server?

Question

What factors should I be aware of that can cause excessive stored procedure recompilation?

Examples of the code that will cause a stored procedure to recompile would be useful. The purpose would be to avoid a recompile if possible which should improve performance.

Dynamic SQL and variable paths resulting in different outputs (either by data type and/or number of columns) seem like they could present a problem. Are the assumptions correct? Are there other examples.

Edit: I did find another example. Creating a temporary table in a flow control statement will cause a recompile.

Answer 1

There are a few ways to ensure recompilation of a stored procedure:

• using WITH RECOMPILE,
• making the stored procedure dynamic (think exec())
• marking the proc for recompile with sp_recompile.
• changing the schema that a cached query plan relies upon
• calling DBCC FREEPROCCACHE
• At the query level an individual statement within a proc can be recompiled with the RECOMPILE query hint (SQL 2008).

Factors in Recompilation

Besides the hard-factors listed above, what causes stored procedure recompilation? Well, lots of things. Some of these are interwoven with the list above, but I want to re-present them b/c it might not be obvious.

• Inserting or deleting lots of data (data density in indexes & tables often controls query plans)
• Rebuilding indexes (a change to underlying objects)
• Creating/dropping temp tables (again, underlying DML changes).
• query plan ages out (think not used recently and sql want's to clean up memory use)

This is by no means an exhaustive list. The query optimizer evolves and suprises no matter how long you've been using SQL Server. But here are some resources that may be of use:

• Troubleshooting stored procedure recompilation (an oldie, but a goodie)
• Recompling Stored Procedures
• Optimizing SQL Server Stored Procedures to Avoid Recompiles
• Execution Plan Caching and Reuse (a must read)

BUT WAIT -THERE'S MORE !

With that said, the presumption in your question is that recompiles are always bad for performance. In fact, often recompliation is good.

So when would you want it to recompile? Let's look at one example of a proc that searches by last name. Stored procedures do 'parameter sniffing' which is a blessing (if it works for you) and a curse (if it works against you). First pass someone searches on Zebr% for zerbrowski. The last name index realizes this is very specific and will return, lets say, 3 rows from a million -- so one execution plan is built. With the proc compiled for a low row result, the next search is for S%. Well, S is your most common name and matches 93,543 rows out of 1 million.

Answer 2

Certain SET options can cause stored procedure recompilation or even multiple recompilations in one execution!

Some of these options may be not even inside the SP

--this will cause recompilation
SET concat_null_yields_null ON;
EXEC spMyProc;

Some of the options that cause recompilation when inside the SP:

ARITHABORT

ANSI_NULLS

QUOTED_IDENTIFIER

Luckily, this one doesn't cause the recompilation: SET NOCOUNT ON;