• Wait just a sec - the OP asked about using DBCC FREEPROCCACHE but accepted Gail's and Grant's answers that apply to the buffer pool, so I'm confused. Boris, were you asking about clearing the plan cache or clearing the buffer pool?

    The plan cache holds execution plans that can be reused so that a new execution plan doesn't have to be created each time the same T-SQL is executed. Plans stay in cache until SQL Server needs space there and flushes plans based on age and/or amount of use (not sure exactly what criteria it uses at the moment - need to do some Googling on that) or until someone executes a command or stored proc that clears either the whole cache or a particular plan. Clearing the entire cache can cause a a sudden, temporary decrease in query performance as SQL Server creates new execution plans for every query, but that performance hit only lasts until the cache is populated and the new execution plans can be reused. There's generally no reason to totally clear the plan cache - sometimes, it may be necessary to clear a single plan so that a better plan can be compiled the next time the query runs, but this should be done only after a thorough investigation of performance problems.

    The buffer pool holds data pages or index pages that have been read from disk in memory. When SQL Server needs a data or index page to execute a query, it looks for the page in the buffer pool first; if it can't find it there, it reads the page from disk and stores it in the buffer pool. Pages stay in the buffer pool until SQL Server needs space for additional pages. The logical read of a page from memory is much faster than the physical read of the page from disk. Keeping pages in memory as long as possible increases the chance that pages will be there when requested, providing a performance gain, especially for queries that access the same pages over and over. There's generally no reason to clear the buffer pool, either - as Gail and Grant said, SQL Server manages it well on its own.

    So, Boris, the overnight jobs on your server may cause some slight performance degradation if the execution plans force the plans for the web service queries out of the plan cache (which will be remedied when a new execution plan is compiled the next time the web service queries run) or if the overnight jobs force pages used by the web service queries out of the buffer pool (which will also be remedied when SQL Server reads the pages from disk into the buffer the next time the web service queries run). The temporary performance hit often is little more than a hiccup in well-designed databases execution efficient queries. It sounds like it didn't disrupt your processes very much at all - evidence the SQL Server generally knows its business very well!

    Jason Wolfkill