SQL Server Performance Forum – Threads Archive

How transactions are written to the disk

Hello,
I am not clear on some things one is
if you are inserting 100 records and commiting that transaction
first it writes to the log file and then after commiting does it write to
the disk right away or does it put in the cache ..if so is that cache the memory or
the disk cache..
Can anybody explain me how it works..
Thanks,

---

Each buffer page contains header information about the page. This header holds a reference counter and an indicator of whether the page is dirty or not. A dirty page is one that has been modified in the buffer cache but whose changes have not yet been writen to disk.
Each time a page is referenced in the buffer cache by a SQL statement, its referece counter is incremented by one. Periodically, the buffer cache is scanned and the referenced counter is divided by four, with the remainder discarded. If the results of the division is zero, the page has been referenced fewer than three times since the last scan, and the dirty page indicator is set for that page. This indicator cuases the page to be added to the free list. If page has been modified, its modifications are first writen to disk; otherwise, if the page had only been read, then it will simply be freed without being written to disk. Basically, as more buffer pages are needed for new data pages to be read in, the least frequently referenced pages will be freed. Luis Martin
Moderator
SQL-Server-Performance.com

---

so the buffer cache is in memory structure right not on the disk cache am i correct

---

yes that is right, the disk cache is something that SQL is totally unaware of. If you are using write caching on a disk controller you have to make sure that the controller has battery backup and preserves the order of writes. If not then your SQL installation is not supported by MS, and may cause you problems… Cheers
Twan

---

]]>