SQL Write-Ahead Logging

Question

Post reply

SQL Write-Ahead Logging

SQLSACT

SSC-Insane

Points: 21838
More actions
April 9, 2021 at 1:41 pm

#3867251
Hi All
I hope to get some clarity on a confusing topic for me.
With WAL - Any modifications are written to the transaction log before they're written to the data files (mdf). Is this correct?
I'm looking for a very simple explanation to clear this up for me.
Most articles mention something along these lines...
```
which guarantees that no data modifications are written to disk before the associated log record is written to disk.
```
The "no data modifications" means data files?
I often see "flushed to disk" mentioned. What exactly does this mean?? Flushed to ldf on disk or mdf on disk?
Where does the log cache part of the buffer pool fit in here?
How does delayed durability fit in here?

Thanks
- This topic was modified 3 years, 7 months ago by SQLSACT.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101101 More actions · Answer 1

First, need to number these qs:

SQLSACT wrote:

(1) With WAL - Any modifications are written to the transaction log before they're written to the data files (mdf). Is this correct?
Most articles mention something along these lines...
which guarantees that no data modifications are written to disk before the associated log record is written to disk.
(2) The "no data modifications" means data files?
(3) I often see "flushed to disk" mentioned. What exactly does this mean??
(4) Flushed to ldf on disk or mdf on disk?
(5) Where does the log cache part of the buffer pool fit in here?
(6) How does delayed durability fit in here?

Now everyone has a common point of reference to respond. People may want to weigh in on some topics but not others.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101101 More actions · Answer 2

(1) Yes.

(2) It means to any recoverable resource, that is, any resource affected by COMMIT / ROLLBACK.

(3) Data in memory (in a buffer) that is forcibly written to disk is said to be "flushed". This does not mean any type of problem/error has occurred. SQL Server has background processes that flush data to disk.

(4) Flushed applies to any data written.

(5) A log cache is the log equivalent of a data buffer/cache. Since log writes are so critical, they are given separate buffers/caches to speed them up.

(6) Delayed durability gives SQL "permission" to write data before the corresponding log write(s) has(have) been confirmed. It can speed up processing, but at the possible risk of permanently losing data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

Thanks

ScottPletcher wrote:

(3) Data in memory (in a buffer) that is forcibly written to disk is said to be "flushed". This does not mean any type of problem/error has occurred. SQL Server has background processes that flush data to disk.

Like Checkpoints?

ScottPletcher wrote:

(6) Delayed durability gives SQL "permission" to write data before the corresponding log write(s) has(have) been confirmed. It can speed up processing, but at the possible risk of permanently losing data.

So, delayed durability has nothing to do with the log cache?

So, SQL will write to mdf's before writing to ldf's?

I realize that something like this is not exactly day-to-day DBA operational work, I would just like to get a complete understanding.

I'm trying to visualize this whole process and it's really difficult.

Thanks

ScottPletcher SSC Guru Points: 101101 More actions · Answer 4

SQLSACT wrote:

Thanks
ScottPletcher wrote:
(3) Data in memory (in a buffer) that is forcibly written to disk is said to be "flushed". This does not mean any type of problem/error has occurred. SQL Server has background processes that flush data to disk.
Like Checkpoints?
ScottPletcher wrote:
(6) Delayed durability gives SQL "permission" to write data before the corresponding log write(s) has(have) been confirmed. It can speed up processing, but at the possible risk of permanently losing data.
So, delayed durability has nothing to do with the log cache?
So, SQL will write to mdf's before writing to ldf's?
I realize that something like this is not exactly day-to-day DBA operational work, I would just like to get a complete understanding.
I'm trying to visualize this whole process and it's really difficult.
Thanks

(3) Yes, exactly. The point of a CHECKPOINT is to flush all data for the db to disk, creating a guaranteed recovery point.

(6) Yes. Yes, potentially. More likely it has not written either but it still allows the transaction to finish. You get more speed and throughput but at a slight risk of genuinely losing data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLSACT SSC-Insane Points: 21838 More actions · Answer 5

ScottPletcher wrote:

SQLSACT wrote:

(6) Yes. Yes, potentially. More likely it has not written either but it still allows the transaction to finish. You get more speed and throughput but at a slight risk of genuinely losing data.

So it's kind of a optimistic way of thinking about it. It assumes that the corresponding writes will happen sometime.

Where do all of these writes-to-happen-in-the-future pile up?

Does this not cause a backlog somewhere?

If something goes wrong and these writes don't happen, that's the data you'll lose?

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

I'm trying to get a flow of exactly what happens.

Am I on the right track here.....

Page brought into buffer cache.
Page modified and marked as dirty.
Record of change made in log cache.
Record of change written to transaction log on disk.
Changes written to mdf's.
Page marked as clean in buffer cache.

Thanks

ScottPletcher SSC Guru Points: 101101 More actions · Answer 7

Yeah, that's basically it. Although "changes written to mdf" is "changes to mdf made in buffers". The mdf pages do NOT have to be written to disk for the trans to continue. The log can be used to rollback / forward recover that data. That's the whole point of WAL.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".