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.
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.
Begin transaction--Transaction starts here
Fetch Page--Page is fetched from Disk to memory
Generate log record--Change to the page generates Log records
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.
Change Data (dirty the page)--Since page changes as it was on disk. It is now dirty
Commit Transaction Starting--Commit issued for transaction
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
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.
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 :)
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 .
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/