Clean pages are those that have not changed, and the data within them still matches what is on disk. A dirty page is one that has been changed since last being written to disk and is the result of a write operation against that index or table data. When the buffer cache fills up, older and less used data will be purged in order to make room for newer data.ĭata is stored in 8k pages within the buffer cache and can be referred to as “clean” or “dirty” pages. The buffer cache (also known as the buffer pool) will use as much memory as is allocated to it in order to hold as many pages of data as possible. Whenever data is written to or read from a SQL Server database, it will be copied into memory by the buffer manager. This fact of life in computing is also an important part of SQL Server architecture. Once loaded, your favorite apps can perform very fast and only need to go back to disk when new data is needed. The way in which software deals with this problem is to write data from slow storage into fast memory. Even SSDs are slow when compared to high-performance memory. This is a fact of nature for anyone that works with computers. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached. This is an important part of SQL Server’s architecture and is responsible for the ability to query frequently accessed data extremely fast. When we talk about memory usage in SQL Server, we are often referring to the buffer cache.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |