0

After I rebuild my indexes and update my statistics, SQL Server will have to rebuild the execution plan cache to get up to speed with the current setup. So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ?

And if it chooses to wait until a query is executed to rebuild the execution plan cache, will there be any slight performance degradation since it has to build the cache from start again before executing the query ?

Let me know if I am missing anything here. Thanks!

2
  • There are many caches in SQL Server, to which are you referring? Buffer cache (buffer pool)? Jul 26, 2016 at 4:53
  • I meant execution plan cache. Updated the question. Thanks for pointing out.
    – karun_r
    Jul 26, 2016 at 5:00

1 Answer 1

1

So, will the SQL server do this immediately after my stats are updated or will it wait until a corresponding query is executed to rebuild the cache ?

NO, SQL Server would not do it immediately. Rebuilding indexes does lot of page movements and so does stats update. There is quite a chance that pages already present in memory, before index operation, would have been thrown back to disk. Now when you run the query again SQL Server optimizer will prepare new plan looking at new statistics and would request I/O to bring pages into memory which were thrown back to disk.

Will there be any slight performance degradation since it has to build the cache from start again before executing the query ?

This can cause a bit of more time being spent by optimizer in preparing new better plan and I/O bringing pages into memory but this would just be the first time. But there would not be much performance degradation. In many cases the new plan would execute query much faster and this would outweigh the time taken by optimizer to build new plan and I/O to get pages into memory.

Since you are interested about plan cache I suggest you read Plan Cache and Reuse. As per the document among other following two can also cause plan to recompile

  • 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.

BUT note that plans do not always get invalidated when you update stats. There are quite few articles describing this

3
  • But there is a chance, right ? Some users were observing little bit of performance issues after the weekly index rebuilding and stats updating. I thought this might be the problem since nothing else has changed.
    – karun_r
    Jul 26, 2016 at 5:02
  • Yes there is, are you sure you are correctly rebuilding indexes and updating stats. Sometimes a redundant task like unnecessary rebuild or update stats causes plans to recompile and thus bringing in slight performance issue. Are you using maintenance plan or any script ?
    – Shanky
    Jul 26, 2016 at 5:05
  • We are doing these two activities on a weekly basis using a stored proc.
    – karun_r
    Jul 26, 2016 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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