SQL Server Performance

if i rebuild indexes then should i flush the procedurte cache?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Sep 25, 2011.

  1. Trev256b Member

    my understanding is that if you rebuild indexes then (as you are amending the indexes) sql server will detect the change and mark the relevant entries in the cache to be dropped - therefore you don't need to flush the cache.
    1) is this correct?
    2) however, is there another reason to flush the cache? can old unused entries be left permanently in procedurte cache accidentally? will there be times when an empty cache is more efficient?
  2. FrankKalis Moderator

    My understanding is a little bit different, but I might be wrong. :)
    Rebuilding indexes and updating statistics will be detected, but MAY cause a recompile for a given plan. If the distribution of the statistics has not changed, and SQL Server still considers the statistics "optimal", I'm inclided to say, then why should SQL Server drop the plan from the cache, or even flush the whole cache? Seems like a waste of resources to me.
    As for you second question: Off top of my head I can think of any reason to flush the cache at all. Since this happens on the server level, you really would not want to do this on a production server. :)
    A system like SQL Server has sophisticated ways of maintaining the cache itself, so that there usually is no reason for you to intervene. However, you can remove single entries for the cache. Have a look here: http://serverfault.com/questions/91...ific-bad-plan-from-the-sql-server-query-cache.
    Also, I can't think of when an empty cache will be more efficient, except maybe for troubleshooting and/or performance-testing reasons, but that maybe a different story.
  3. preethi Member

    Only once I saw an empty Proc cache efficient:
    We had a complex procedure where based on parameter values different plans are best.When a plan is created for one set of parameters, it need to be dropped for the other set of parameters. Initially we went with "WITH RECOMPILE " option but later we vroke it into two.
    Now we have more advanced options to optimize and attach a plan to use during execution. they overshadow the usefullness of empty proc cache.
  4. satya Moderator

    Do not flush the PROC CACHE unless you know the in and out of your query execution such as using hints etc. The empty cache will be good start to do a baseline and benchmarking on your environment to obtain how the system is performing at certain levels. In any case do not attempt this on production server which may degrade the performance further...

Share This Page