Decree: Set your partition offset and block size – make SQL Server faster

If I’ve said it once, I’ve said it 1,024 times: your SQL data and log drives need a 1024 KB starting offset, and a 64 KB block size*.

Your SQL data and log drives need a 1024 KB starting offset, and a 64 KB block size.

If you read this blog, you’re likely a data professional. And if you’re not sure what I’m talking about, then here’s the very bare bones basics you need to know:

  1. The disk system under SQL Server is not set up optimally by default.
  2. It’s super easy to set it up optimally, if you take care of it at the beginning. That is, when the drive is first created, before you’ve got data sitting on it.
  3. Just tell your SAN guy / hardware guy, when he goes to set up ANY new drive for SQL Server, that it needs to be formatted with a 1024 KB starting offset, and a 64 KB block size. He’ll say “okie dokie” and all will be well.

Again, this is super easy, and free, IF you pay attention when you’re setting up a new drive. Got a new server? New SAN? Assigning a new drive to SQL? Starting offset and block size, baby.

Important edit: I recently ran across an interesting new twist to the partition offset saga. In short, I ran my standard PO/block size check on a disk, and then found that the disks were GUID Partition Table (GPT) disks, and actually REQUIRED a 128MB partition offset. After poking around for quite a while, I got confirmation from our own Denny Cherry (siteTwitter), that as long as the starting offset is “a multiple of 64k you’re fine”. And because any whole MB is a multiple of 64KB (16 * 64KB = 1MB), 128MB is a perfectly fine starting offset.

How to Check Your Disks

You have are a number of ways available to check your partition offset and block size, but I find these the most reliable and simple. You can run both commands from the a command prompt (run in Administrator mode).

Partition offset:

powershell.exe “get-wmiobject win32_diskpartition | select systemname, name, index, startingoffset | format-table -autosize”

Returns something like this:

systemname name                  index startingoffset
---------- ----                  ----- --------------
myserver    Disk #0, Partition #0     0        1048576
myserver    Disk #0, Partition #1     1     1049624576
myserver    Disk #0, Partition #2     2     1322254336
myserver    Disk #0, Partition #3     3     2505048064
myserver    Disk #0, Partition #4     4   239810379776
myserver    Disk #0, Partition #5     5   244105347072

This is on my laptop, so there’s only one disk (Disk #0). You’re only interested in the Partition #0 for each disk, because that’s the partition at the start of the disk (which contains the partition offset). My laptop has a proper starting offset: 1048576 bytes = 1048576/1024 = 1024KB.

Block Size:

You CAN retrieve “BlockSize” using the Powershell statement above, but I have found it to be consistently inaccurate. Instead, do this for each disk:

FSutil FSinfo NTFSinfo F:

Returns something like this:

NTFS Volume Serial Number :       0x9c78028378025c80
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x000000001ba03fff
Total Clusters :                  0x00000000037407ff
Free Clusters  :                  0x000000000053950a
Total Reserved :                  0x00000000000007d0
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x00000000174c0000
...

All you’re looking for is Bytes Per Cluster. On my laptop, it’s 4096 bytes. Hey, I don’t have dedicated SQL drives on here. But what you want to see is Bytes Per Cluster : 65536.  Again, that’s 65536 bytes / 1024 = 64KB, which is what you want for the disks that will hold SQL data and log files.

Your SQL Data and Log Drives Need a 1024Kb Starting offset, and a 64Kb Block Size

“Windows Server 2008 partition alignment defaults to 1024 KB (that is, 1,048,576 bytes). This value provides a durable solution. It correlates well (as described later) with common stripe unit sizes such as 64 KB, 128 KB, and 256 KB …”

Conclusion and Invitation

So, be sure to put this in the best practices documents, and your standard for new SQL Server installations. And be sure to give your SQL data and log drives a 1024 KB starting offset, and a 64KB block size.

Remember that all my blogs are licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License, so steal this entire blog post, put it on your blog (with a link back to me and a note that says something like “reprinted by permission from Jen McCown”). Spread the word.

And, with very very few exceptions, your SQL data and log drives need a 1024 KB starting offset, and a 64Kb block size.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

* As always, there are few rules without exceptions. Some systems may indeed benefit from a different block size; but that should be determined via testing. It should be deliberate.  If I can get the free world (of SQL Server) checking their block size, and setting it on purpose, then I have done good.

8 thoughts on “Decree: Set your partition offset and block size – make SQL Server faster

  1. Justin Dearing

    So as a dev who mainly cares about setting up SQL on his own laptop, or VMs where they are stupid enough to give the dev sa on a few questions come to mind:

    1) Is this a bad idea for non-SQL things or just optimizations based on old assumptions? Will this make file copying slow? Will this make my postgres, mysql, mongodb, or MS Access files slower?

    2) Is there some sort of perfmon metric I can monitor on my system so I can look at and see that my system is working extra hard in the way that misalligned disk partitions and incorrect block sized partitions tends to cause systems to work? While the entire internet seems to agree with you that this is the right way to partition your sql systems, its good to be able to recognize the symptoms on ones own server.

    1. Jen McCown Post author

      There’s no perfmon counter you can use to monitor that specifically, unless maybe you do a side-by-side comparison of seconds per read/write with a properly formatted and an improperly formatted disk. Generally, you notice there’s disk performance issues, and the offset/block size is something you know about and look for. For deeper testing, if you’re really into it, I’d start by hunting around the links and authors in this blog: http://www.sqlskills.com/blogs/paul/are-your-disk-partition-offsets-raid-stripe-sizes-and-ntfs-allocation-units-set-correctly/

      And since the formatting we’re talking about is at the disk write level, it will affect everything, not just SQL. SQL likes to write in 64k blocks, but does Windows? Office? MySQL? I have no idea. A hardware guy like Denny Cherry or Wes Brown (etc) would know better than I. There appear to be some resources out there (e.g. this for mysql: http://www.mysqlperformanceblog.com/2011/06/09/aligning-io-on-a-hard-disk-raid-the-theory/).

  2. Pericles Sevegnani

    Hey Jen, what about setting up a new drive with your reccomendation and then restore a database from another server with another disk config. Will it work the same way as a new one?
    Tks and best regards from Brazil.

    1. Jen McCown Post author

      This is a disk issue, so it doesnr matter where the database comes from. As long as the disk is formatted properly, all is well. Does that help?

  3. Jose

    Hi, wondering about a setup with SAN, Hyper-V and SQL VMs. Should all levels match in terms of block size, starting from the SAN, CSV volumes and up to the volumes on top of the VHDs on the (always on) cluster nodes?
    Thank You

  4. Pingback: Microsoft SQL Server DBA Setup Checklist and Recommendations | Question Driven

Comments are closed.