Memory Management in SQL Server 2005
The configuration of Memory in SQL Server 2005 is similar in many ways to that of its predecessor SQL Server 2000. This article will cover the configuration options and topics that an Administrator can use. If RAM is configured correctly then the performance of your SQL Server 2005 database can greatly improve. This article will discuss the management of the buffer cache and SQL Server memory allocation. This should allow you to then make informed choices regarding your own configuration.
Buffer CacheWhen we talk about caching data we are referring to the process of storing data from disk in memory. When the data is read from disk and stored in memory that data �gets cached�. Data access from memory is much faster than data access direct from disk and as such you want SQL Server to have enough memory to achieve a high cache hit ratio.The SQL Server buffer cache is the largest in SQL Server memory and it stores and data and index pages. Each instance of SQL Server maintains a linked list (called the free list) which contains the address of pages in the buffer cache that are available (free) to store data. When SQL Server starts pages are allocated for buffer cache and they are free for use. As threads begin to access data SQL Server reads pages from physical disk which is then stored in the first free page in the list. If that thread or another must read or modify the same page it can do so from the buffer cache. This reduces I/O overhead on the physical disk and thus enhances the performance of the operation. Header information is stored on each page in the buffer. It contains a reference counter and indicates whether the page is �dirty�. A �dirty page� is described as a page that has been modified in the buffer cache but has not yet been written back to disk. The reference counter is used to collate information on data access, an algorithm is used that decides what data to keep in the buffer cache, and the least frequently used is removed. Data in the cache that has been selected from removal if that page has been modified it is written back to disk, other wise data that has not been modified is discarded, and the page in the buffer cache is freed.
Lazy Writer ProcessSQL Server uses two built-in mechanisms to scan the buffer cache and write out dirty pages, the lazy writer process and the checkpoint process. The lazy writer checks that the free buffer list does not fall below a specific size. If the free list falls below that size the lazy writer scans the cache and claim back unused pages, the limit depends on the size of the buffer cache. In windows 2000 and 2003 most of this work is done by the individual threads so the Lazy Writer does not have that much work to do, unless the system is very I/O intensive the lazy writer is needed to help maintain the free list.Checkpoint ProcessThe checkpoint process also writes out modified pages to disk but does not free those pages in the buffer. The SQL Server check point operation synchronises the physical data with the state of the buffer cache by writing out all modified pages of the buffer cache to disk. The check point forces any pending transaction log records in the log buffer to be written to the log file thus ensuring a permanent copy of the data on disk when the checkpoint completes. The checkpoint process is used to help speed up the recovery process as the number of transactions that must be rolled forward is minimised. The reduction in time taken to recover does come at some cost to performance. The check point process incurs some overhead because it may perform a large number of writes to disk. These writes could potentially slow user response times. Systems that experience heavy data modification are candidates for this overhead.The check point operation involves a number of steps:
Checkpoints occur when:
|
