Windows lazy write?  
Author Message
Ohno





PostPosted: Wed Dec 13 14:33:09 CST 2006 Top

SQL Server Developer >> Windows lazy write?

Hi all.

Here's a difficult question. I've reading up on the anatomy of a
transaction and have a question about with the transaction log is written to
disk. My question is, at the point when SQL Writes a completed command to
disk (i.e. flush the Log Cache to disk) does it simply pass the Data on to
the Windows OS so that Windows can deal with the write in it's own time? By
this I mean does SQL make sure that the information Really has been written
to disk and Windows hasn't decided that it can perform a lazy write?


Regards

Colin Dawson
www.cjdawson.com

SQL Server239  
 
 
Roy





PostPosted: Wed Dec 13 14:33:09 CST 2006 Top

SQL Server Developer >> Windows lazy write? The process can not complete until the hardware reports back that the
write has been done. In the case where the hardware is a
battery-backed caching RAID controller with write caching enabled the
actual write to the disk might not have actually completed. (Hopefully
there is no such thing as a write-caching controller that is NOT
battery backed!) Otherwise, unless the hardware is lying, it has been
written to disk.

This is why disk performance for the logs is SO important to SQL
Server performance.

Roy Harvey
Beacon Falls, CT

On Wed, 13 Dec 2006 20:16:32 GMT, "Colin Dawson"


>Hi all.
>
>Here's a difficult question. I've reading up on the anatomy of a
>transaction and have a question about with the transaction log is written to
>disk. My question is, at the point when SQL Writes a completed command to
>disk (i.e. flush the Log Cache to disk) does it simply pass the Data on to
>the Windows OS so that Windows can deal with the write in it's own time? By
>this I mean does SQL make sure that the information Really has been written
>to disk and Windows hasn't decided that it can perform a lazy write?
>
>
>Regards
>
>Colin Dawson
>www.cjdawson.com
>
 
 
Colin





PostPosted: Thu Dec 14 04:01:13 CST 2006 Top

SQL Server Developer >> Windows lazy write? Thanks Roy,

That's confirmed my assumption. I'd assumed that SQL Server overrode the
Windows OS default behaviour, and forced disk writes for all SQL operations.
Meaning if SQL says it's on disk, it's been written to hardware, and it's
the hardwares responsibility to make sure it's safe.

I do know that there are some controller cards about that do have a non
battery backed up lazy write mechanism! The option is something that can be
altered though, and I've never seen one of these set so that they are in
lazy write mode. I suppose they would have a use in a high thoughtput
situation where data loss isn't so much of an issue. As I said, I've never
seen this used in anger.

Regards

Colin Dawson
www.cjdawson.com




> The process can not complete until the hardware reports back that the
> write has been done. In the case where the hardware is a
> battery-backed caching RAID controller with write caching enabled the
> actual write to the disk might not have actually completed. (Hopefully
> there is no such thing as a write-caching controller that is NOT
> battery backed!) Otherwise, unless the hardware is lying, it has been
> written to disk.
>
> This is why disk performance for the logs is SO important to SQL
> Server performance.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 13 Dec 2006 20:16:32 GMT, "Colin Dawson"

>
>>Hi all.
>>
>>Here's a difficult question. I've reading up on the anatomy of a
>>transaction and have a question about with the transaction log is written
>>to
>>disk. My question is, at the point when SQL Writes a completed command
>>to
>>disk (i.e. flush the Log Cache to disk) does it simply pass the Data on to
>>the Windows OS so that Windows can deal with the write in it's own time?
>>By
>>this I mean does SQL make sure that the information Really has been
>>written
>>to disk and Windows hasn't decided that it can perform a lazy write?
>>
>>
>>Regards
>>
>>Colin Dawson
>>www.cjdawson.com
>>


 
 
Tibor





PostPosted: Thu Dec 14 05:42:01 CST 2006 Top

SQL Server Developer >> Windows lazy write? FWIW, here's a good whitepaper on this particular topic:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/




> Thanks Roy,
>
> That's confirmed my assumption. I'd assumed that SQL Server overrode the Windows OS default
> behaviour, and forced disk writes for all SQL operations. Meaning if SQL says it's on disk, it's
> been written to hardware, and it's the hardwares responsibility to make sure it's safe.
>
> I do know that there are some controller cards about that do have a non battery backed up lazy
> write mechanism! The option is something that can be altered though, and I've never seen one of
> these set so that they are in lazy write mode. I suppose they would have a use in a high
> thoughtput situation where data loss isn't so much of an issue. As I said, I've never seen this
> used in anger.
>
> Regards
>
> Colin Dawson
> www.cjdawson.com
>
>


>> The process can not complete until the hardware reports back that the
>> write has been done. In the case where the hardware is a
>> battery-backed caching RAID controller with write caching enabled the
>> actual write to the disk might not have actually completed. (Hopefully
>> there is no such thing as a write-caching controller that is NOT
>> battery backed!) Otherwise, unless the hardware is lying, it has been
>> written to disk.
>>
>> This is why disk performance for the logs is SO important to SQL
>> Server performance.
>>
>> Roy Harvey
>> Beacon Falls, CT
>>
>> On Wed, 13 Dec 2006 20:16:32 GMT, "Colin Dawson"

>>
>>>Hi all.
>>>
>>>Here's a difficult question. I've reading up on the anatomy of a
>>>transaction and have a question about with the transaction log is written to
>>>disk. My question is, at the point when SQL Writes a completed command to
>>>disk (i.e. flush the Log Cache to disk) does it simply pass the Data on to
>>>the Windows OS so that Windows can deal with the write in it's own time? By
>>>this I mean does SQL make sure that the information Really has been written
>>>to disk and Windows hasn't decided that it can perform a lazy write?
>>>
>>>
>>>Regards
>>>
>>>Colin Dawson
>>>www.cjdawson.com
>>>
>
>


 
 
Colin





PostPosted: Tue Dec 19 10:47:01 CST 2006 Top

SQL Server Developer >> Windows lazy write? Thanks, Tibor.

I'll take a close look at this one once, I'm back from my XMas hols. I've
mailed the link to my work address so that I don't loose it.

Regards

Colin Dawson
www.cjdawson.com



> FWIW, here's a good whitepaper on this particular topic:
>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>


>> Thanks Roy,
>>
>> That's confirmed my assumption. I'd assumed that SQL Server overrode the
>> Windows OS default behaviour, and forced disk writes for all SQL
>> operations. Meaning if SQL says it's on disk, it's been written to
>> hardware, and it's the hardwares responsibility to make sure it's safe.
>>
>> I do know that there are some controller cards about that do have a non
>> battery backed up lazy write mechanism! The option is something that can
>> be altered though, and I've never seen one of these set so that they are
>> in lazy write mode. I suppose they would have a use in a high
>> thoughtput situation where data loss isn't so much of an issue. As I
>> said, I've never seen this used in anger.
>>
>> Regards
>>
>> Colin Dawson
>> www.cjdawson.com
>>
>>


>>> The process can not complete until the hardware reports back that the
>>> write has been done. In the case where the hardware is a
>>> battery-backed caching RAID controller with write caching enabled the
>>> actual write to the disk might not have actually completed. (Hopefully
>>> there is no such thing as a write-caching controller that is NOT
>>> battery backed!) Otherwise, unless the hardware is lying, it has been
>>> written to disk.
>>>
>>> This is why disk performance for the logs is SO important to SQL
>>> Server performance.
>>>
>>> Roy Harvey
>>> Beacon Falls, CT
>>>
>>> On Wed, 13 Dec 2006 20:16:32 GMT, "Colin Dawson"

>>>
>>>>Hi all.
>>>>
>>>>Here's a difficult question. I've reading up on the anatomy of a
>>>>transaction and have a question about with the transaction log is
>>>>written to
>>>>disk. My question is, at the point when SQL Writes a completed command
>>>>to
>>>>disk (i.e. flush the Log Cache to disk) does it simply pass the Data on
>>>>to
>>>>the Windows OS so that Windows can deal with the write in it's own time?
>>>>By
>>>>this I mean does SQL make sure that the information Really has been
>>>>written
>>>>to disk and Windows hasn't decided that it can perform a lazy write?
>>>>
>>>>
>>>>Regards
>>>>
>>>>Colin Dawson
>>>>www.cjdawson.com
>>>>
>>
>>
>
>