March 23, 2011 at 8:07 am
Again, what Gail said.
But in my original analogy, the parts of the plate covered with cookies were the active VLFs and the empty parts of the plate were the inactive (or truncated) VLFs. The act of consuming the cookies (I.E., sticking the data into the data file) makes the affected part of the plate available for checkpointing. The checkpoint says "yes, this part of the plate is indeed empty and should be available for more cookies."
I hope I didn't just confuse you more.
March 23, 2011 at 8:35 am
Brandie Tarvin (3/23/2011)
The act of consuming the cookies (I.E., sticking the data into the data file) makes the affected part of the plate available for checkpointing.
Data doesn't move from the log to the data file (except during log restores). Data changes are written to memory (data cache) and the record of those changes is written to the log buffer. When the transaction commits (or maybe before) the log buffer is flushed to disk. At some time later the modified data page is written to the data file. This is done either by the checkpoint or the lazy writer.
It's the checkpoint process itself that marks the log as reusable, providing nothing else needs the log records.
Edit: clarified and expanded.
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 23, 2011 at 9:36 am
GilaMonster (3/23/2011)
muthukkumaran (3/23/2011)
Truncating the log file will reduce the logical log (VLF) file size. By default, SQL server truncates the transaction log file OR deletes the logical file (VLF) automatically and reuses the log file again and again (Just like a circular).Truncating does not reduce the VLF size. It just changes the status of one or more VLFs from active to inactive. Truncation also does not delete any VLFs. Deleting a VLF would result in a change in the size of the log file and its only shrink which does that.
Sorry for the misinformed.Its corrected now truncating vs shrinking [/url].
Thanks Gail usual...
Edit: for add link
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 25, 2011 at 6:00 am
GilaMonster (3/23/2011)
Brandie Tarvin (3/23/2011)
The act of consuming the cookies (I.E., sticking the data into the data file) makes the affected part of the plate available for checkpointing.Data doesn't move from the log to the data file (except during log restores). Data changes are written to memory (data cache) and the record of those changes is written to the log buffer. When the transaction commits (or maybe before) the log buffer is flushed to disk. At some time later the modified data page is written to the data file. This is done either by the checkpoint or the lazy writer.
It's the checkpoint process itself that marks the log as reusable, providing nothing else needs the log records.
Edit: clarified and expanded.
Okay, been reading up. My cookie analogy is still good if I change the bit about "sticking the data in the data file" to "moving the data to stable media."
Per Microsoft:
The SQL Server ... checkpoint process periodically sweeps the buffer pool for buffers that contain pages from a specified database and flushes all dirty buffers to stable media.
Stable media can mean disk, but doesn't only mean disk. It could be a SAN cache (assuming the disk controllers have a cache mechanism set up that way, or "something else" that Microsoft doesn't exactly quantify-which is confusing. If they're going to make a statement like that, they should clarify it.
So, eating the cookies to clear the plate, means moving the data from dirty pages to stable media (aka the disk in many cases) and making sure there's room for more cookies. Yes?
March 25, 2011 at 6:11 am
Brandie Tarvin (3/25/2011)
Okay, been reading up. My cookie analogy is still good if I change the bit about "sticking the data in the data file" to "moving the data to stable media."Per Microsoft:
The SQL Server ... checkpoint process periodically sweeps the buffer pool for buffers that contain pages from a specified database and flushes all dirty buffers to stable media.
Stable media can mean disk, but doesn't only mean disk. It could be a SAN cache (assuming the disk controllers have a cache mechanism set up that way, or "something else" that Microsoft doesn't exactly quantify-which is confusing. If they're going to make a statement like that, they should clarify it.
Clarification - Something that tells the windows operating system that it is stable media.
If Windows writes to a SAN and the SAN returns write complete, Windows will assume that it meant just that. Write to disk complete. If the SAN actually just wrote it to cache and then lied as to the completion of the write (which they do), windows cannot know any better.
If you manage to implement an IO driver that takes any write operation and just discards the data returning write success, Windows is going to believe that the write was completed successfully even though it wasn't.
So, eating the cookies to clear the plate, means moving the data from dirty pages to stable media (aka the disk in many cases) and making sure there's room for more cookies. Yes?
The cookie analogy works without involving the data file.
The plate is the log, the cookies are active log records, the empty spaces on the plate are inactive log records. The checkpoint process looks at each cookie on the plate and decides whether it still needs to be there or not. If not, it eats the cookie, leaving space for future log records.
The changed records can have long since being written to stable media by the time the associated log records are truncated. Depends what else is happening, what else those log records are needed for (log backups, mirroring, replication, CDC, etc0
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 25, 2011 at 6:17 am
GilaMonster (3/25/2011)
The changed records can have long since being written to stable media by the time the associated log records are truncated. Depends what else is happening, what else those log records are needed for (log backups, mirroring, replication, CDC, etc0
Okay. Now you just said something I have not found in any documentation I've read so far (bearing in mind I'm not done reading those I/O links you gave me).
So, let me see if I understand this correctly. SQL Server writes data to both the log file and the data file simultaneously? Then commits the data in the data file (after the transaction is complete) and uses the log file for other processes, if there are other processes needing the data?
Or did I misunderstand again?
March 25, 2011 at 6:37 am
Brandie Tarvin (3/25/2011)
GilaMonster (3/25/2011)
The changed records can have long since being written to stable media by the time the associated log records are truncated. Depends what else is happening, what else those log records are needed for (log backups, mirroring, replication, CDC, etc0Okay. Now you just said something I have not found in any documentation I've read so far (bearing in mind I'm not done reading those I/O links you gave me).
Specifically what?
So, let me see if I understand this correctly. SQL Server writes data to both the log file and the data file simultaneously? Then commits the data in the data file (after the transaction is complete) and uses the log file for other processes, if there are other processes needing the data?
No.
SQL writes data modifications to data pages in the data cache. It writes the log records to disk at the point the transaction is committed (or sometimes before). At some point (maybe immediately, maybe not) the changed data pages are written to disk.
They can have been written before the transaction commits, that's perfectly valid and acceptable. The only unbreakable rule is log before data. Log records must be hardened in the log file before the data changes that the log records describe are written from the data cache to disk.
The primary purpose of the log is durability (once transaction completes the data changes must remain non matter what) and consistency. There are other processes that take advantage of the log to do other stuff.
Edit: A little clarification
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 25, 2011 at 6:49 am
GilaMonster (3/25/2011)
Brandie Tarvin (3/25/2011)
GilaMonster (3/25/2011)
The changed records can have long since being written to stable media by the time the associated log records are truncated. Depends what else is happening, what else those log records are needed for (log backups, mirroring, replication, CDC, etc0Okay. Now you just said something I have not found in any documentation I've read so far (bearing in mind I'm not done reading those I/O links you gave me).
Specifically what?
Specifically the comment about the log records I quoted above.
GilaMonster (3/25/2011)
Brandie Tarvin (3/25/2011)So, let me see if I understand this correctly. SQL Server writes data to both the log file and the data file simultaneously? Then commits the data in the data file (after the transaction is complete) and uses the log file for other processes, if there are other processes needing the data?
No.
SQL writes data modifications to pages in cache.
See, this is where I get lost again. Nobody ever states it outright, and that's what's driving me nuts. Which pages? The pages in the log file or the pages in the data file? Or pages someplace else?
March 25, 2011 at 7:23 am
Brandie Tarvin (3/25/2011)
See, this is where I get lost again. Nobody ever states it outright, and that's what's driving me nuts. Which pages? The pages in the log file or the pages in the data file? Or pages someplace else?
Log files don't have pages.
http://msdn.microsoft.com/en-us/library/ms190969.aspx
As for what else the log records are used for: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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 25, 2011 at 7:39 am
To get more input, I #SQLHelp tweeted the question about the transaction log to data file that I'd posted. I got an immediate response that SQL does indeed work that way, with a link to a post on dbforums that has ... questionable information.
Lots of people chiming in now (after a long wait), with different links. Looks like I have more reading to do.
Sorry for taking over this thread from the OP.
March 25, 2011 at 7:48 am
Brandie Tarvin (3/25/2011)
To get more input, I #SQLHelp tweeted the question about the transaction log to data file that I'd posted. I got an immediate response that SQL does indeed work that way, with a link to a post on dbforums that has ... questionable information.
From who?
To be honest, I've seen too much questionable info on #sqlhelp to feel comfortable using it. Low signal:noise ratio.
The IO docs I referred you to should make it clear that SQL does not move data from log to data file during a checkpoint, and given that they are MS whitepapers and the authoritative references on the subject...
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 25, 2011 at 7:57 am
GilaMonster (3/25/2011)
The IO docs I referred you to should make it clear that SQL does not move data from log to data file during a checkpoint, and given that they are MS whitepapers and the authoritative references on the subject...
The problem is that the docs aren't very clear. They are written for people who already know what most of the terminology means (I know only some of it), not for those who don't. For instance, there's no mention of the fact that Transaction Logs have no pages. That's why I keep asking so many questions.
I know my questions are probably frustrating you, but I'm a visual, hands-on learner. Documentation-only learning doesn't work for me unless I pull it apart and put it back together with references I recognize. If I don't keep pounding at this, and asking the question 6 ways to Sunday, then I'll miss something, make a bad assumption, then later on make poor recommendations based on my assumptions.
I do appreciate the time you're taking out of your schedule to help me muddle through this.
March 25, 2011 at 8:03 am
Brandie Tarvin (3/25/2011)
For instance, there's no mention of the fact that Transaction Logs have no pages.
No, but there's nothing that says they do. Read up on architecture of the log and the data file (msdn/Books Online) and you will see that data files have pages and extents, log files have VLFs (virtual log files)
It's one of those cases where you cannot list all this things that don't exist.
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 27, 2011 at 10:16 am
Brandie Tarvin (3/22/2011)
You are misreading that entry. It says it truncates, not shrinks. Truncate allows SQL to reuse the portion of the log that was just freed. It's like eating the cookies off a plate. You allow that section of the plate to be used for more cookies, but the action itself does not alter the size of the plate.EDIT: To further extend the analogy (re: the first sentence about filling up disk space), if you don't eat any cookies off the plate, but you get more cookies, you fill up your plate by constantly putting more cookies on it. Until the plate is so full that cookies no longer fit, in which case, you have to add more plates to hold the additional cookies or the cookies just can't go anywhere (SQL Server stops).
Brandie, this is an AWESOME analogy. If you don't mind, I think I'll start using this to explain it to people. (And it brings to mind Lucy in the cake factory...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 27, 2011 at 10:39 am
Brandie Tarvin (3/25/2011)
GilaMonster (3/25/2011)
The IO docs I referred you to should make it clear that SQL does not move data from log to data file during a checkpoint, and given that they are MS whitepapers and the authoritative references on the subject...The problem is that the docs aren't very clear. They are written for people who already know what most of the terminology means (I know only some of it), not for those who don't. For instance, there's no mention of the fact that Transaction Logs have no pages. That's why I keep asking so many questions.
I know my questions are probably frustrating you, but I'm a visual, hands-on learner. Documentation-only learning doesn't work for me unless I pull it apart and put it back together with references I recognize. If I don't keep pounding at this, and asking the question 6 ways to Sunday, then I'll miss something, make a bad assumption, then later on make poor recommendations based on my assumptions.
I do appreciate the time you're taking out of your schedule to help me muddle through this.
Brandie, you might want to download from technet the MCM videos on log files internals. Paul covers this topic thoroughly in it, and the slides should give you the visual that you need.
Log Files Internals and Maintenance Lecture video
Log Files Internals and Maintenance Demo video
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 31 through 45 (of 69 total)
You must be logged in to reply to this topic. Login to reply