Disk Partitioning w/SQL Server 2000/2005/2008
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)
Questions:
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
data/log files?
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.
http://support.microsoft.com/kb/929491
http://itknowledgeexchange.techtarget.com/sql-server/how-much-performance-are-you-loosing-by-not-aligning-your-drives/#comments
Thanks in advance for your help.
John
Software/Hardware used:
Software/Hardware used:
ASKED:
September 11, 2008 3:51 PM
UPDATED:
September 28, 2008 5:25 AM
Answer Wiki:
Since no one else answered, I'll go ahead and do so.
1. SQL does everything in 8k pages which are grouped together into 64k extents. All reads and writes to the disks are done one extent at a time.
2. I'm not sure where that formula came from. Everything I've read from the storage vendors says set it to 64k.
3. I set it on all drives on all servers. Exchange, Oracle, SQL Server, File Servers.
4. No, setting the alignment does not effect the way Windows stores the files on the hard drives. It simply moves the first place that Windows writes to from the 33th block on the disk to the 65th block on the disk.
5. Windows 2003 64bit has the same 32k offset as the 32bit OS. Windows 2008 actually defaults to a 128k offset. This should be just fine as it is basically the same as a 64k offset with a 64k file written at the beginning of the disk.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
