SQL Server Performance

How To Set Up Automatic Buffer Cache Clean?

Discussion in 'Performance Tuning for DBAs' started by zvidas, May 10, 2005.

  1. zvidas New Member

    Does anyone know how to clean the buffer cache earlier than it reaches the max size? In theory the cache holds inactive pages while buffer cache reaches its max size (or min free buffer pages limit ) then lazywriter thread cleans all inactive pages. In order to improve sql server performance I need to clear these inactive(not all) pages periodically

    Thanks for your help
  2. deepakontheweb New Member

    I don't think its a good practise..

    Checkpoint : Execute to minimize the number of dirty pages

    DBCC DROPCLEANBUFFERS : Removes all clean buffers from the buffer pool. Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

    DBCC FREEPROCCACHE : Removes all elements from the procedure cache. Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.

    Read more from books online under 'Lazy writer' Freeing and Writing Buffer Pages

    Each instance also has a separate lazywriter thread that scans through the buffer cache. The lazywriter process sleeps for an interval of time. When it is restarted, it checks the size of the free buffer list. If the free buffer list is below a certain point (dependent on the size of the cache) the lazywriter process scans the buffer cache to reclaim unused pages and write dirty pages that have a reference count of 0. On the Windows NT and Windows 2000 operating systems, most of the work populating the free buffer list and writing dirty pages is done by the individual threads and the lazywriter thread typically finds little to do.

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  3. zvidas New Member

    Thanks deepakontheweb. I've already read this. These two statements which you suggested cleans all data from buffer even these pages which were not aged.
    The problem is that when our cache becomes full performance degrades because the new data is read from physical disk not from cache. It looks like that there are no enough free pages to import data from db file into cache. The size of buffer cache is about 2GB. Sometimes the cache clears automatically, but not often as we wanted to. It looks like our lazywriter is quite lazy.
    Any ideas?
  4. deepakontheweb New Member

    On which SQL edition/version/serive pack and Operating system your database server is running on? how much memory is installed on server [also tell us SQL min/max memory settings and Page file size]

    I hope there could be something which is disturbing or causing problem.

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  5. zvidas New Member

    Well we have Windows 2000 advanced server; SQL 2000 Enterprise edition, SP3. Server has 2 processors, 4015 MB RAM memory. SQL memory is alocated dynamicaly: min 0, max 3115 MB. The spotlight shows that sql server has 1.6 GB. When monitoring this server with spotlight sometimes it shows that there are no free pages at all.
  6. deepakontheweb New Member

  7. zvidas New Member

    Cache Hit Ratio99.993515014648438
    Cache Flushes0.0
    Free Page Scan (Avg)0.0
    Free Page Scan (Max)0.0
    Min Free Buffers331.0
    Cache Size 4362.0
    Free Buffers359.0
  8. deepakontheweb New Member

    To check if Min Free Buffer counter goes to Zero [or No free buffers at all -- As you told in previous post] create a table

    create table lrustats
    [Statistic] nvarchar(32) not null,
    [Value] float not null

    and populate it using below command through a SQL Job that runs after 5 mins or whatever druation.

    insert into lrustats exec('dbcc sqlperf(lrustats) with tableresults, no_infomsgs')

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  9. zvidas New Member

    Ok, thanks, I'll monitor it and tomorrow let you to know

  10. zvidas New Member

    One more question... Why Min Free Buffers (331.0) and Cache Size (4362.0) on all of our servers is the same, hence they have different size of allocated memory: 512 MB, 4GB, 3GB...
  11. deepakontheweb New Member

    Ooops.. [Sorry to say] Check for "Free Buffers" not "Min Free Buffers"

    Your all questions are answerd here...

    How Does SQL Server Manage Memory Dynamically?
    SQL Server grows and shrinks its memory usage dynamically by committing and de-committing buffers from the buffers reserved at startup. The LazyWriter process is responsible for growing and shrinking the BPool. A committed bitmap array is maintained to track the commit or de-commit of buffers.

    Growing the BPool
    When the LazyWriter wakes up, it checks if SQL Server needs more memory. If so, then the LazyWriter checks the committed bitmap to find a buffer with the bit set to off (0 or not committed) and locates the buffer. The buffer is then committed, and finally the bit in the committed bitmap is flipped to on (1 or committed).

    Shrinking the BPool
    When the LazyWriter wakes up, it checks if the OS has sufficient memory available to service the memory requests from other applications. If not, then the LazyWriter looks for buffers that can be de-committed. If it finds such a buffer, it is de-committed and the 8 KB page is returned to SQL Server's reserved address space, which can be used in the OS.

    SQL Server will not commit the value configured in the min server memory (MB) setting, but it will maintain at least that value once it is reached. As more memory is needed, more buffers are committed, and their bits are flipped to on (1) until the max server memory (MB) setting is reached. SQL Server cannot commit memory exceeding the max server memory (MB) setting.

    When SQL Server is configured to use memory dynamically, it polls the system periodically to determine the amount of free RAM available on the system. SQL Server grows or shrinks the BPool to keep 4 MB (on Intel systems) to 10 MB (on Alpha Systems) RAM available on the system. If there is less memory free, SQL Server releases memory to the OS. SQL Server adds memory to the BPool only when its workload requires more memory; a server at rest does not grow its BPool.

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  12. deepakontheweb New Member

    Also you can run DBCC MEMORYSTATUS and check for "Free" counter

    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 14546
    Free 552
    Procedures 12721
    Inram 0
    Dirty 19344
    Kept 0
    I/O 0
    Latched 64
    Other 542597

    You can run DBCC CACHESTATS


    and use performance monitor under SQL Server:Buffer Manager/ Free Pages

    Deepak Kumar

    --An eye for an eye and everyone shall be blind
  13. zvidas New Member

    Thank you deepakontheweb again. I'm starting to beleive that all works normally. I guess I was misguided by monitoring tool and my dark knowledge about what is going on here. Simply the SQL server takes dynamically only 1.6GB from allocated 3 GB and it stands on this point. The buffer cache takes 1.5 GB of these 1.6GB and this tool says that buffer cache is full of active pages about 99percent. So I thinked that it isn't normal. But after reading contents of your published links I guess that it's normal proccess. <br />Free buffers number varies from 1200 to 12 all the time. This is about 1 percent of the buffer cache so therefore in the graph it looked like buffer cache is completelly full. But after good thinking I would like to have more free space in the buffer cache <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Cheers<br />
  14. TRACEYSQL New Member

    This is a really good thread i been reading it all.
    So basically keep an eye on the free pages.....and if this goes to zero then this would mean more memory required

  15. TRACEYSQL New Member

    Hi Zvidas how did you get the buffer cache value.......of 1.6 g

Share This Page