SqlServer Begin's

I was recently faced with a situation where the users were complaining that of the one of the development database servers where slowing down. And so I logged to the server remotely and opened task manager and noticed the CPU was performing as usual but the PF memory was summing up to the total server memory. My first thought was, is the server under memory pressure? Note - This database instance in concern lives in a box where there are two sql server instances. To investigate further I opened the performance monitor and configured some of the usual counters I monitor with. Buffer cache hit ratio , page life expectancy ,Avg and current disk queue length , Processor time, sql server connections , user database and temp transactions , complications and recompilations. When I went through the Buffer cache and page life expectancy counters I knew it was nothing to do with memory pressure. But what could it be that server was encountering this slowness and request time outs ? It was the...

Buffer cache is a part of the buffer pool which is also referred to as the data cache. The buffer cache is responsible for holding the data pages in memory which are frequently accessed by Sql Server. Alternatively data pages are read from the disk which causes high IO operations. How do you clean the buffer cache without restarting the server ? Following are two DBCC command that could be used to clear the buffer cache. DBCC DropCleanBuffers This DBCC command clears the entire buffer cache for a give sql server instance DBCC FlushProcinDB (DBID) The DBCC command clears the buffer cache for the given database Example 1 This shows the buffer cache can be cleaned using the DBCC FLUSHPROCINDB SELECT * FROM sys.syscacheobjects where dbid = db_id ( 'ADVENTUREWORKS' ) GO DBCC FLUSHPROCINDB ( 12 ) GO SELECT * FROM sys.syscacheobjects where dbid = db_id ( 'ADVENTUREWORKS' )