Last Good Quote: Son's are the seasoning on our lives. - Someone on Facebook

Wednesday, August 27

MSSQL - When does data move from Memory to Disk

So I had to ponder this question (I had a vague idea but wanted to get a firm understanding)

Question: We know SQL stores data objects in RAM as it needs, at what point does the changes that are made in RAM get written to the disk?

In layman's terms: In the background the server monitors it's memory. When a particular object has not been used "in a while" that object is moved to disc. It is at this point that the disc and memory are syncronised. This is also done on a proper shutdown of the SQL Server.

Side Note: If memory is full of objects that are frequently used, SQL Server will read directly from disk. This can negatively impact performance in a substantial way.

From Wikipedia (link):

SQL Server buffers pages in RAM to minimize disc I/O. Any 8 KB page can be buffered in-memory, and the set of all pages currently buffered is called the buffer cache. The amount of memory available to SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager. Either reading from or writing to any page copies it to the buffer cache. Subsequent reads or writes are redirected to the in-memory copy, rather than the on-disc version. The page is updated on the disc by the Buffer Manager only if the in-memory cache has not been referenced for some time. While writing pages back to disc, asynchronous I/O is used whereby the I/O operation is done in a background thread so that other operations do not have to wait for the I/O operation to complete. Each page is written along with its checksumwhen it is written. When reading the page back, its checksum is computed again and matched with the stored version to ensure the page has not been damaged or tampered with in the meantime.[58]


Post a Comment