March 28, 2011 at 3:38 am
Checkpoint writes the changed pages to disk. It does not flush the data cache...
Again now what does that mean and what exactly happens? Damn this is getting more and more confusing.
Sorry for being such a pain.... :crying:
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 28, 2011 at 3:42 am
It means exactly what I said. Checkpoint writes the changed pages to disk, it does not remove the data page from memory.
Some transaction has run, it has required SQL to load a set of data pages into memory. It has modified some of those pages. The checkpoint then runs, takes the changes and writes them back to disk so that the pages on disk look exactly like the pages in memory. Checkpoint does not remove (discard) those pages from memory. It leaves them there so that the transaction (or the rollback, or some other transaction) can use them.
Think about MS Word. What you're proposing (checkpoint removes the page from memory) would be like MS Word closing the document every time you save it. I'm sure you'll agree that is not what happens and it would be extremely inefficient (and stupid) if it did.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2011 at 3:50 am
Write data pages to disk != remove pages from memory.
excellent gail.
opening ssc now only (on monday morning)
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 28, 2011 at 4:19 am
Sachin Nandanwar (3/27/2011)
But what happens if a checkpoint occurs in middle of a very long running transaction and then the transaction rolls back.
The dirty pages now have been written to disk which is undesirable in the above scenario but log entries aren't made to the log files as the transaction has rolled back.
How is that done ? By the next Checkpoint ? If yes then one will have to burden themselves with wrong set of data.
Dirty pages only written to the disk when the LSN on the page <= LSN for last record written to the log file.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 28, 2011 at 4:52 am
Hey Gail,
Thanks for such a nice explaination.
If my understanding is correct when a rollbacks happens and also a checkpoint in middle of an inflight transaction there will be basically 2 versions of the same pages.One on disk(updated records which were written to the disk by checkpoint during the transaction) and other one on the memory(which were rolled back due to rollback of the transaction).
So now when I query the table I will get the rolledbacked values from the memory which will be consistent with the log files.
I hope this time I am right.
One more thing I really appreciate your patience level.;-)
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 28, 2011 at 4:53 am
Sachin Nandanwar (3/28/2011)
But just one last confusion if you can clear me on that.When the data is written to the disk in middle of a transaction and a checkpoint occurs then how is the old value then again restored on the data pages which are already flushed to the disk ?Writting to the log and again rewriting the deletes on the log files in case of rollbacks is fine but just not sure which process will update the pages on the disk with the old values in case of rollbacks.
Back to the cookie analogy.. Let's see if I can make this work in light of what Gail and Wayne both said.
Eating cookies before they are baked is BAD as you might get food poisoning. (Moving the dirty pages to stable storage before the log file is hardened will set the database as suspect).
So, we have a cookie sheet and we have a plate. The cookie sheet is our dirty data page. The raw dough is our data changes. When we add raw dough to the cookie sheet, it becomes dirty. Filling the cookie sheet is a transaction. Now, we could move the raw cookies from the cookie sheet to the plate or straight to the plate from the bowl (the stable storage / physical data file), but that would just be messy and violate all sorts of food health regulations. Instead, we bake the cookies (harden the log file) before moving them to the plate.
Food health regulations (Microsoft software rules) won't let us move the cookies to the plate until and unless they are baked. Even if some of the cookies on the cookie sheet are baked and others aren't (mid-transaction), there's someone guarding the plate who won't let all those cookies move to the plate unless the entire sheet's worth have been baked. So if that person catches us moving the cookies, he will stop us and move the cookies back to the cookie sheet to finish the baking process (The rollback).
Regardless of when the cookie move happens (mid-transaction or post transaction), all cookies must be baked before the last cookie is placed on the plate. Once cookies are baked, they can't be turned back to raw dough. They are hardened. And even if they are flushed from our dirty page to the plate, they can't be turned back to raw dough. And the greasy spots on the cookie sheet tell us where these cookies used to be. All the guard has to do is place the cookies back exactly where they were on the cookie sheet to rollback a transaction, removing only the cookies newly placed on the plate. (This last sentence is an oversimplification of what Wayne said about the reverse transaction statements. I just can't figure out how to fit that whole bit into the analogy.)
And again, I'm not sure if my analogy just confused the issue more. But if you understood it, it means I'm finally figuring this out, thanks to Gail and Wayne.
EDIT: After writing this, I'm thinking the cookie analogy might not be working after all. It over simplifies things and can lead to wrong conclusions. I'll try to find another analogy.
March 28, 2011 at 5:11 am
Sachin Nandanwar (3/28/2011)
If my understanding is correct when a rollbacks happens and also a checkpoint in middle of an inflight transaction there will be basically 2 versions of the same pages.One on disk(updated records which were written to the disk by checkpoint during the transaction) and other one on the memory(which were rolled back due to rollback of the transaction).
That's pretty much true at any point in time. The data file usually has older versions of the pages than are what is in memory. This is why tools that try and copy the MDF without SQL's knowledge usually get useless files
So now when I query the table I will get the rolledbacked values from the memory which will be consistent with the log files.
What's in memory is always consistent and correct, and it's always what queries read. No query reads directly from disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2011 at 8:12 am
So to conclude(Hopefully;-)) the discussion we had till now we covered two importants points.
1)Truncation and the Shrinking of log files and the cookie analogy.
The difference is
Truncation is the process of marking the active VLF's as inactive while shrinking of the log files will result in actual physical shrinking of the log files.
2)Checkpoint's occurring in middle of inflight transactions.
The log records will be updated to the log files before the checkpoint occurs in middle of a transaction.The data pages are modified in the buffer memory and the checkpoint writes them to the disk.
If the inflight transaction causes a rollback the delete for the rollback will be written to the log file and the data pages will be again modified in the buffer memory to the rolledback values at the point of rollback.
The unbreakable rule always will be "log before data".
It wont matter if there is a mismatch between the pages on the disk and the memory.The queries will always use the memory data pages to return the resultset.
So next time when the checkpoint occurs the rolledbacked values on the memory will be updated on to the disk and the contents of the disk,memory and the entries for the records in log files will be in sync.
Please add more details if I may have missed any.
EDIT:Initially I stated truncation as process of marking inactive VLF's as active in the post instead it is the other way round.It is a process of marking active VLF's to inactive.
Thanks Gail for pointing that out.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 28, 2011 at 8:14 am
Sachin Nandanwar (3/28/2011)
Truncation is the process of marking the inactive VLF's as active
marking active VLFs as inactive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2011 at 8:16 am
GilaMonster (3/28/2011)
Sachin Nandanwar (3/28/2011)
Truncation is the process of marking the inactive VLF's as activemarking active VLFs as inactive.
Yes sorry.Will update it.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 10 posts - 61 through 69 (of 69 total)
You must be logged in to reply to this topic. Login to reply