Disk Partitioning w/SQL Server 2000/2005/2008

5 pts.
Tags:
Partitioning
SQL Server 2000
SQL Server 2005
SQL Server 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. Thanks in advance for your help. John
ASKED: September 11, 2008  3:51 PM
UPDATED: April 3, 2014  7:08 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following