3

I understand that when (for example) an update is issued, the data pages are first read into the buffer pool, locks are acquired and changes are written out to the log file in the buffer, then the log file is written to the disk and later at a checkpoint dirty pages in the buffer pool are flushed out and gets hardened on the drive. What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint? Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush? Thanks.

  • try to start with this : techrepublic.com/article/… – itzik Paz Nov 15 '14 at 9:11
  • Thanks mate but it doesn't say which file it reads from. I am just curious if "select" reads from the log file or from the pages in the memory since they are the only up-to-date ones after a DML and before checkpoint. – Stackoverflowuser Nov 15 '14 at 9:16
3

For specific part of Question you asked to Sean.

'thus, when the page is "read" by another transaction, the updated values are there.': How does SQL Server know that data on the disk is out of date and therefore should read (upon a select query) from the memory?

Suppose there is other request which comes and asks to read same page. SQL Server Buffer Manager will check the data cache in the buffer pool to see if it already has the page cached in memory. If the page is already cached, then the results are passed back to the Access Methods(Access Methods contains set of code which handles I/O requests for rows, indexes, pages, allocations and row versions) to continue with operation as requested. If the page isn’t already in cache, then the Buffer Manager will get the page from the database on disk, put it in the data cache, and pass the results to the Access Methods.

Is there an information on the page header that the data in the page on the disk is stale? Isn't there a performance penalty for this as it reads from the disk first and goes to memory next.

Yes there is. The header of each page in cache stores details about the last two times it was accessed, and a periodic scan through the cache examines these values. A counter is maintained that is decremented if the page hasn’t been accessed for a while; and when SQL Server needs to free up some cache, the pages with the lowest counter are flushed first. The process of “aging out” pages from cache and maintaining an available amount of free cache pages for subsequent use can be done by any worker thread after scheduling its own I/O or by the lazywriter process.

Having said that I would like you to read How It Works: Bob Dorr's SQL Server I/O Presentation

Take the following as an example update tblxxx set col1 = GetDate() where the table is 1 billion rows.

Simplified the process to discuss.

  1. Begin transaction--Transaction starts here

  2. Fetch Page--Page is fetched from Disk to memory

  3. Acquire Lock

  4. Acquire Latch

  5. Generate log record--Change to the page generates Log records

  6. Update page LSN to match that of the log record--When change to a page generates Log records They have LSN numbers identifying them and the same LSN number is stamped on header of the page change to which generated these log records.

  7. Change Data (dirty the page)--Since page changes as it was on disk. It is now dirty

  8. Release Latch

  9. Commit Transaction Starting--Commit issued for transaction

  10. FlushToLSN of Commit--LSN corresponding to commit is flushed to log disk and recorded. This is done before dirty data page is flushed to disk

  11. Release locks

  12. Commit Transaction Complete

The entire table won’t fit into memory so lazy writer will write dirty pages to disk. In doing so lazy writer will first call the routine FlushToLSN to flush all log records up to and including the LSN stored in the page header of the dirty page. Then lazy writer, and only then, will it issue the write of the data page. If a power outage occurs at this point the log records can be used to reconstruct the page (rollback the changes).

Notice that the latch only protects the physical access to the in-memory data page only for the small about of time the physical change is being made. The lock protects the associated data until the commit or rollback takes place.

This allows lazy writer to remove a dirty page while the locking structures continue to maintain the transactional integrity.

The question which can also arise is if rollback is issued instead of a commit would SQL Server fetch back in pages to undo the update? The answer is yes. The page could have been removed from buffer pool/page cache by lazy writer after the FlushToLSN took place. When the rollback occurs the pages changed by the transaction may need to be fetched into buffer pool to undo the change.

PS: Feel free to ask if any further clarification is required.

  • Hi Shanky. Thanks a lot, appreciated. So, do I understand alright; if a query selects data from a table after another transaction updates the same table's same data (commits its transaction) but before the data in the memory is written to disk (by lazy writer or a worker thread), the select statement will first check the buffer pool and return the dirty pages if they still exist, if not will go to disk? Thanks. – Stackoverflowuser Nov 16 '14 at 8:25
  • 1
    You are correct. If page is not found in memory it would be first brought from disk to memory. If it is there then no need – Shanky Nov 16 '14 at 11:10
4

What I don't understand is, doesn't this mean the data in the data pages on the disk is stale until a checkpoint?

On Disk - yes.

Where does SQL Server read the data from when a select query is issued between "commit transaction" and the checkpoint flush? Thanks.

Memory. Let me explain a little more...

When a the update statement runs (say update mytable set columnA = 1 where columnA = 2) the change is logged and also made on the data page in memory. You've already said that the logging happens and is hardened (flushed to stable media) but the change is also made on the data page in memory. Thus, when the page is "read" by another transaction, the updated values are there.

There is something else that comes into play which are called latches. Locks keep logical consistency but latches keep physical consistency. The data page is latched, locks are taken, updates happen, logs are written and eventually flushed. The changes are always first made, in memory.

You might be thinking... "hey wait, if the changes are made to the data pages but logging isn't complete, the database is inconsistent!" That's close! The changes made to the data pages aren't reflected on disk yet - so if a crash were to happen, the data would be as it was pre-update which is how it should be. When the checkpoint occurs, WAL (write ahead logging) must still take place, so all LSNs up to the most recent reflected on data pages part of the checkpoint must first be flushed to disk. Thus the consistency is maintained and all is well in the world of SQL Server :)

  • 'thus, when the page is "read" by another transaction, the updated values are there.': How does SQL Server know that data on the disk is out of date and therefore should read (upon a select query) from the memory? Is there an information on the page header that the data in the page on the disk is stale? Isn't there a performance penalty for this as it reads from the disk first and goes to memory next? – Stackoverflowuser Nov 15 '14 at 11:01
  • 3
    @Stackoverflowuser The query processor is always operating on in-memory pages. It requests pages solely from the buffer pool. If a page isn't present, the storage engine reads it from disk and puts it in the buffer pool. Get yourself an internals book. – Mark Storey-Smith Nov 15 '14 at 12:14
0
  1. Transaction Log - when you execute a DML operation (Data Modification Language = INSERTE/UPDATE/DELETE) it is been writing to the transaction log. "select .... from " are not written to the transaction log. If only DML operations are saved, so the question is "why ?" Answer: To give us the ability to Undo/Rollback transactional operation, and in case of server/app failure, if we had a transaction log backup, we can restore it by roll-forward the transaction log .

  2. Memory (buffer cache/pool ) - in sql server the CPU working with the memory only. When SQL reads Buffer/Block (Block = in sql server the smallest data unit, 8KB) From the Disk he search it in the Buffer pool(memory) and if it doesn't exist there then he reads from the disk.

Tansaction log - http://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.