I recently read some information regarding aligning the Disk for use with SQL
Server from Mr. Denny. My understanding is, SQL Server handles data in chunks of 64KB, so the disk drive SQL Server writes to containing data and log files should be configured to work in 64KB chunks. The articles I am referring to are listed below, and I am wondering what
other peoples thoughts are. I am also interested in understanding how to
determine whether my disks are aligned and/or how to align them. Based on the
articles, I see my Offset is 32KB on my drives, but I am also trying to work
with the formula in the Microsoft KB article to determine how/where exactly I
become aligned. The formula from the KB article, also listed below, is:
((Partition offset) * (Disk sector size)) / (Stripe unit size)
1. Verification, Does SQL Server read/write data to disk in 64KB chunks? I thought it handled data in 8KB pages, and was under the impression that it reads/writes data that way.
2. Where do I find the values listed in the formula above? I am a little
confused how they got the values to determine where the alignment occurred in the Microsoft KB article.
3. Do you only do the alignment on drives where SQL Server is writing
4. Does setting your offset to 64KB affect the way the OS stores data on the
drives? Meaning, a 65KB file will actually use 128KB of space? And in that case the remaining 63KB is wasted? Or am I totally lost and/or taking the thought process too deep?
5. I was told by someone the default offset in 64-bit Windows OS's as well as Windows Server 2008 32-bit is 64KB. Is there any truth to that?
I guess I am looking for more detail as to how it works, as well as understanding how to figure out my own environment.
The articles I am referencing are listed below.
Thanks in advance for your help.
September 11, 2008 3:51 PM
September 28, 2008 5:25 AM