Do we need to Recompile all stored procedures when indexes rebuild happen?

Recently I have done re开发者_如何学Pythonbuilding indexes on my production server. Do I need to re compile all my existing stored procedures to get this effected. Please suggest.

Yes, you should recompile the stored procedures (you can use sp_recompile). Changing the table structure forces a new execution plan, but just adding an index does not. When you restart Sql Server, that will have the same effect.

From MSDN:

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization occurs automatically the first time a stored procedure is run after SQL Server is restarted. It also occurs if an underlying table that is used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not occur until the next time that the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time that it executes

Your question was about "rebuilding indexes" but the quote in the answer you accepted is about creating new indexes.

Rebuilding indexes will also end up re-creating the statistics on that index with fullscan.

As a result of the updated statistics all affected queries will automatically get an optimality based recompile. Except if the query happens to contain the KEEPFIXED PLAN hint.

Moreover I don't believe the quote in Kevin's answer to be correct (at least since SQL Server 2008+) anyway.

Both of the following white papers state that "Adding an index to a table or an indexed view" will cause a recompile.

• Plan Caching in SQL Server 2008
• Plan Caching and Recompilation in SQL Server 2012

This is also what I see when testing.

CREATE TABLE T(X INT, Y INT);

GO

CREATE PROC P AS SELECT Y FROM T;

GO

EXEC P;

GO

CREATE NONCLUSTERED INDEX IX ON T(Y);

GO

EXEC P;


GO


DROP TABLE T;DROP PROC P

The execution plan shows the new index is used automatically

Profiler shows an automatic recompile with reason "Schema Changed"