Microsoft – SQL Server – Datafiles – Log File Write Patterns

One of the first emails I received this morning was one detailing one of my wrong assumptions about NetApp LUN (mis) alignment determination:

NetApp Lun Aligning

And, so I read up some more and tried argumenting that blog with any new data on the Net.

And, I still came away with something that I did not fully disclose earlier.  And, that is that seemingly LUNS dedicated to MS SQL Server log files are registering as mis-aligned when ‘profiled’ within NetApp.

The specific NetApp commands for validating alignment:

  • priv set diag; lun alignment show <lun-name>
  • lun show -v <lun-name>

so what to do, but take to Google.  It is a bit difficult to get a good Google “Search Item”, but managed to do OK.

Here are some relevant entries:

Since both Kendra back in May 29th, 2012 and MS Premier SQL Server Engineering on May 23rd, 2013 says to use SysInternals’s Process Monitor and I am ‘ve a big fan of Mark Russinovich,  I took to it.

In SysInternals – Process Monitor, filtered for :

  • Process Name –> sqlservr.exe
  • Operation –> WriteFile

Here is SysInternal’s Process Monitor results page:

MS SQL Server - Log File - Write Patterns

This much is obvious:

  • In the “Details” column, the length of most Log Files writes are 61,440 bytes (60 KB)
  • This holds true for both entries written to our local drive (D:) and our Network Drive (Y:)
  • Testimonial to how MS SQL Server Log files are written, our entries are written sequentially and when one adds up the offset to the size, one will arrive at the new line — i.e if one takes 127,044,608 (offset) + 61,440 (length), one will arrive @ 127,106,048 (the start of the next line)
  • Other important facts are the I/O Flags : Non-cached and Write-Through.  SQL Servers writes are not cached, as they are persisted directly to disk

The fact that log entries are persisted directly to disk might explain what we see on our SAN.  The SAN is reporting that our writes are misaligned – The SAN is expecting us to come in bursts, but we write out to the Lun per each transaction commits.

In conclusion, it appears that for SQL Server Log files we will more likely than not report misaligned LUNs.


Addendum – 2013.04.02

Data Storage for VDI – Part 8 – Misalignment

On March 3rd, 2013, John Martin said…

Partial writes on an Oracle redo log file (or any other file which is written to sequentially) are handled pretty well by the existing partial write mechanisms inside of ONTAP. For the most part these are held in memory until the subsequent writes to the log file come in and these are combined internally into a single 4K block. The real killer is partial overwrites which I think I covered off in a blog post here



Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s