Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Currently looking into reindexing our sql database, and cannot find any information on whether or not the procedure cache would be effected. Any help or information would be great. We are using SQL Server 2005 as our DBMS.

share|improve this question
1  
When you say "query cache", do you mean "Procedure Cache" ? To best of my knowledge, reindexing a database would not affect the procedure cache. Procedure cache gets flushed out when you do dbcc freeproccache. –  Kin Mar 26 '13 at 20:19
    
Yes, the procedure Cache was what I meant. Thanks –  BananaJustice Mar 26 '13 at 20:30

1 Answer 1

up vote 6 down vote accepted

Execution Plan Caching and Reuse lists some of the factors that trigger recompilation:

Recompiling Execution Plans

Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL Server detects the changes that invalidate an execution plan and marks the plan as not valid. A new plan must then be recompiled for the next connection that executes the query. The conditions that invalidate a plan include the following:

  • Changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  • Changes made to a single procedure, which would drop all plans for that procedure from the cache (ALTER PROCEDURE).
  • Changes to any indexes used by the execution plan.
  • Updates on statistics used by the execution plan, generated either explicitly from a statement, such as UPDATE STATISTICS, or generated automatically.
  • Dropping an index used by the execution plan.
  • An explicit call to sp_recompile.
  • Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  • For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.
  • Executing a stored procedure using the WITH RECOMPILE option.

Since Index Rebuild operations also update the statistics as a side effect, it follows that an index rebuild will trigger a plan invalidation (recompile).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.