September 3, 2011 at 6:12 pm
Books online states that one of the things that a checkpoint does is "Writes all dirty log and data pages to disk." (Checkpoints and the Active Portion of the Log)
I'm trying to figure out what a dirty log page is. I believe they are log pages in memory (not on disk yet)for transactions that have not yet been committed or rolled back. Transactions that have been committed or rolled back have to be on disk before the commit/rollback is "done", so those log pages can't be dirty. These dirty pages would eventually be written anyway before their transaction becomes permanent, so the checkpoint is just forcing it to happen a little early?
Thanks,
Chad
September 3, 2011 at 6:55 pm
These articles by Gail Shaw may assist.
http://www.sqlservercentral.com/articles/64582/
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
And this about what CHECKPOINT can and can not accomplish
September 3, 2011 at 7:54 pm
Thanks Ron, there was some good information there.
I found two parts that related to my question:
SQL Server automatically runs checkpoints on a regular basis to keep the number of log records that it would need to process during a database recovery.
. This isn't specifically talking about dirty log pages, but it does emphasize that the log records need to be written to disk, since we wouldn't be able to recover if they were not there.
(Checkpoint) Writes all dirty pages for the current database to disk. Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk. Checkpoints save time during a later recovery by creating a point at which all dirty pages are guaranteed to have been written to disk.
In the BOL link it states that data pages are written by the checkpoint, and doesn't mention dirty log pages. I do realize that doesn't necessarily mean they are not written by a checkpoint.
Here's perhaps another way to phrase my question...
The tran log's purpose is to track all changes so that the transaction can be rolled back or recovered during recovery. In order for it to do that, it has to be on disk - anything left just in memory could be lost. So every update, however big or small has to flush its log pages to disk before it can be complete. So is there ever such a thing as a dirty log page at all? Wouldn't all log pages be immediately flushed to disk as soon as possible? If you updated a single row that only had one bit column, that update would have to have its log page flushed to disk before it would return "1 row updated" back to the client, right? <Sigh> and that reminds me that I read somewhere that the log doesn't use pages anyway so maybe I better change the terminology I'm using to "log record" instead of "log page"...
Thanks for the links Ron, do you think I'm on the right track?
Thanks,
Chad
September 3, 2011 at 8:21 pm
bitbucket-25253 (9/3/2011)
These articles by Gail Shaw may assist.http://www.sqlservercentral.com/articles/64582/
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
And this about what CHECKPOINT can and can not accomplish
I got "Page Not Found" on the first and third link.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 3, 2011 at 8:34 pm
Welsh Corgi (9/3/2011)
bitbucket-25253 (9/3/2011)
These articles by Gail Shaw may assist.http://www.sqlservercentral.com/articles/64582/
http://www.sqlservercentral.com/articles/Transaction+Log/72488/
And this about what CHECKPOINT can and can not accomplish
I got "Page Not Found" on the first and third link.
From Chad Crawford's reply, I take it he was able to use the links, I tested the links before posting and they worked. Now attempting it again I get that #$%& message "Page NOT Found" Worse yet if I copy and paste the link into a new tab (using Internet Explorer) the page displays .... Maybe something with SSC web site ? ? ?
September 3, 2011 at 8:40 pm
I had to copy and paste the URL. I think the HREF portion of the links has an escaped
at the end.
Thanks,
Chad
September 6, 2011 at 9:20 am
I found the reference for log pages - it was from a QOD[/url] 3 years ago and using that I was able to find this R2 MSDN article: http://msdn.microsoft.com/en-us/library/ms190969(v=SQL.105).aspx wherein it states: Log files do not contain pages; they contain a series of log records.
In the QOD the definition of "Page" was debated (an 8k structure vs. a "unit" written to disk), but regardless it seems to me that a log record would never be dirty and that the checkpoint is just writing the dirty data pages out. Or am I wrong? Are log pages sometimes cached in memory while the transaction is still running?
September 19, 2011 at 4:02 pm
I found a reference in the SQL Server 2008 Internals book (p.183) that states:
Checkpoint operations also write log records from transactions in progress to disk becuase the cached log records are also considered to be dirty.
Gail Shaw also posted this in another thread:
Checkpoint will cause the log buffer to be flushed to disk (the current set of un-hardened log records).
So it seems that log records can indeed be dirty, contrary to what I previously thought.
Here is a related question, what does a dirty log record consist of?
I can think of two possibilities:
1. If there could be multiple LSNs per DML statement then a large update could have LSNs that are sitting in memory while the update is running. Once the update finishes (assuming it is the only statement in an implicit transaction), all the LSNs are flushed to disk.
2. If there is only one LSN per DML statement, I think the only time you have a dirty log record would be during a multi-statement transaction that is still open (i.e. the statements at the beginning have finished, but the log isn't hardened yet nor has a commit happened)?
Of course, they are not mutually exclusive, they could both be true...
I know LSNs are created in ascending order, do LSNs also have to be written to disk in LSN order? If so, does committing a transaction cause all the dirty log records that have not been committed to be written to disk (so that the LSN order is preserved), or are the LSNs not necessarily written to disk in LSN order?
I know that I shouldn't need to know logs to this level of detail, but for some reason I've been incredibly curious of late.
Thanks,
Chad
September 19, 2011 at 4:37 pm
Chad what adds to my confusion is items like:
Checkpoint will cause the log buffer to be flushed to disk (the current set of un-hardened log records).
And statements such as "the data is written to the disc' - It always raises the question(s) in my mind, fine where both the log file and database are files on on the disk ... which is it written to?. Another example
I found a reference in the SQL Server 2008 Internals book (p.183) that states:
Checkpoint operations also write log records from transactions in progress to disk becuase the cached log records are also considered to be dirty.
And yes I am aware that the logfile is organized into sub sections (VLF's), but why oh why can't the authors say either to the "log file" or "database" instead of the general term "disc". Well my whinning here is of no help to anyone. But I had to vent....
September 19, 2011 at 4:47 pm
To answer your question from The Thread, and a couple of points from your last post, run something like this:
USE Sandpit -- A database in SIMPLE recovery!
CHECKPOINT
SELECT * FROM sys.fn_dblog(NULL, NULL) AS fd
SELECT v.number
INTO dbo.Table1
FROM master.dbo.spt_values AS v
WHERE v.type = N'P'
SELECT * FROM sys.fn_dblog(NULL, NULL) AS fd
You should see that every log operation gets its own LSN. Depending on the concurrent workload, they may or may not be sequential. It's easy to imagine that there could be large gaps if, for example, a thread gets switched off the scheduler, or blocking occurs. In any case, FlushToLSN always flushes all the log records up to a particular LSN because they might be needed for recovery (either to REDO or UNDO a change).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 19, 2011 at 5:00 pm
bitbucket-25253 (9/19/2011)
Chad what adds to my confusion is items like:Checkpoint will cause the log buffer to be flushed to disk (the current set of un-hardened log records).
Checkpoint calculates the minimum recovery LSN (as documented at the link you've seen before on this thread: http://msdn.microsoft.com/en-us/library/ms189573.aspx) and all log records numbered up to and including that LSN are written to the physical log file before checkpoint starts writing dirty data pages to the physical data file(s). The log records that get flushed may be in more than one in-memory log buffer - log buffers vary from 512 bytes to 60KB in 512 byte increments. More details at http://sqlskills.com/BLOGS/PAUL/post/Benchmarking-1-TB-table-population-(part-2-optimizing-log-block-IO-size-and-how-log-IO-works).aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 19, 2011 at 5:30 pm
SQL Kiwi
are written to the physical log file before checkpoint starts writing dirty data pages to the physical data file(s).
Thanks
physical log file / physical data - clear concise ... many thanks , wish other writers were as clear and concise as you have been.
September 20, 2011 at 9:04 am
That was awesome Paul. I've been running fn_dblog under different scenarios and what I see is very cool. I did some googling as well and found some interesting articles (some a little scary - restores using fn_dblog) that helped a lot as well. One thing that surprised me is how infrequent checkpoints are, even on a production system. Of course the frequency will vary system to system, but I assumed we would have several per minute on ours and that was not the case. Shows what I know, eh?
Can you confirm, deny, or give an opinion on one piece that I haven't yet found an answer to? While researching this, I've found several places that indicate that the writes to the physical log on disk must be done in order (here and here for example), which I assume means LSN order although I can't find any reference that explicitly states it that way. If that is the case (and here comes the question), would committing a transaction (implicitly or explicitly) write out all the dirty log records to the physical log file just as a checkpoint would? For a transaction to be committed, its log records would need to be pushed to the log file on disk, and pushing those log records would require all prior log records (some of which may not yet be committed or rolled back) to be pushed to the physical log file as well so that the order was maintained, correcdt? In my mind that is the only way it could work, and that would mean that although dirty log records would exist, on a moderately busy system they wouldn't ever exist for very long.
Thanks!
Chad
September 20, 2011 at 11:10 am
Chad Crawford (9/20/2011)
That was awesome Paul. I've been running fn_dblog under different scenarios and what I see is very cool. I did some googling as well and found some interesting articles (some a little scary - restores using fn_dblog) that helped a lot as well. One thing that surprised me is how infrequent checkpoints are, even on a production system. Of course the frequency will vary system to system, but I assumed we would have several per minute on ours and that was not the case. Shows what I know, eh?
There are some big improvements to checkpoint in Denali - IIRC the idea is to perform a steady low level of checkpoint activity rather than big hits now and again.
Can you confirm, deny, or give an opinion on one piece that I haven't yet found an answer to? While researching this, I've found several places that indicate that the writes to the physical log on disk must be done in order (here and here for example), which I assume means LSN order although I can't find any reference that explicitly states it that way.
As far as I know, this is true, and the log records are packed into disk-sector-sized sequential atomic writes.
If that is the case (and here comes the question), would committing a transaction (implicitly or explicitly) write out all the dirty log records to the physical log file just as a checkpoint would? For a transaction to be committed, its log records would need to be pushed to the log file on disk, and pushing those log records would require all prior log records (some of which may not yet be committed or rolled back) to be pushed to the physical log file as well so that the order was maintained, correcdt? In my mind that is the only way it could work, and that would mean that although dirty log records would exist, on a moderately busy system they wouldn't ever exist for very long.
Yes. The general contract of FlushToLSN is that all log records in memory (dirty, to use your phrase) get flushed up to the required LSN before any dirty data pages are written. Lower LSN-numbered records may be associated with transactions that are still in-flight, but that's ok from a recovery point of view, they still get flushed as a block (well ok as a series of sector-sized blocks)...
There is an interesting edge case where a rollback might have to fetch the modified data page back in and UNDO the log records. That's explained in Bob Dorr's extremely hairy article: http://blogs.msdn.com/b/psssql/archive/2010/03/24/how-it-works-bob-dorr-s-sql-server-i-o-presentation.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 20, 2011 at 2:37 pm
Thanks Ron and Paul, I appreciate your patience in seeing me through! (BTW - Bob's presentation looks great! I'm part way through, thanks for the link.)
Thanks,
Chad
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply