Hey,
Thanks to a massive rollout internally here the blogs have been few and far between, and unfortunately that’s likely to be the case for the next 3 months, but I’ll try to add something of value when I do manage one!
Hope the new year is treating everyone well, and to help your January hangover (and possibly to help any new hardware you got breathe better), here’s a small article on why you should take a little time to get your disks set up right when you’re deploying a new SQL Server.
The script I’m using to generate these results is Glenn Berry’s excellent Diagnostic Queries script. When you download it, just CTRL-F and search for “stall”. This will give you your I/O stall (read/write times) per database, per file.
So my scenario is I have two HP DL380 servers, both with 14GB of RAM, 2 quad core 2ghz CPU’s, and 6 x 146GB 10K SAS drives in a Raid 5 configuration. Both are running Windows Server 2003, and SQL Server 2005 x64 edition. Same partitioning, same drive letters, same rack in the server room, practically identical in every way. Even down to the workload they’re running, they ran the same workload over the same period of time so my test results wouldn’t be biased (I’ve been planning this one for a while).
The only difference is that on Server 1 the drives were partitioned through Disk Manager, and were formatted with the NTFS default 4K block size. On Server 2 the disks were partitioned and aligned using Diskpart (This article will take you through it. It says exchange but it’s exactly the same procedure) , and were formatted with NTFS partitions having 64K block sizes.
So on to the results, then the explanation after. It’s one user database running an ERP system.
Server Avg. Read Time Avg. Write Time
Server 1 149.4ms 3ms
Server 2 74ms 3ms
(Note : Reads are bad overall anyway down to code, but it’s the same code run on both servers so the improvement is still impressive)
So by setting up the disks according to best practices, I reduced my read times by 50%, and had no negative impact on write times. Success!
So why the difference on otherwise exactly the same hardware? There are two reasons. The first one is aligning the partitions. If your partition is aligned, the offset of the partition will match the offset of the disk/array. In English, your request will hit the same block on the raw disk, as on the partition, so your disk won’t have to span blocks/check offsets to fetch your data, returning your data in a single I/O.
 |
| Happy Server 🙂 |
If the partition is not aligned, potentially your partition doesn’t start at an offset of 0, it could start 1/4 way into a raw disk block. So every block on your partition, will span two blocks on the raw disk, requiring two I/O’s. (very rough explanation, there are a billion variations on disk setup which would make this a filthy lie, but this is the principle)
 |
| Sad Server 🙁 |
The second reason is that while a SQL data page is 8KB (even at that, it will require two blocks of storage on 4K NTFS), it deals in extents when it’s pulling data, which are 8 page groups. So 8 * 8K equalling 64KB means that on a 4KB block sized drive, it requires 16 blocks to be read. On a 64KB block sized drive, 1 block. I had to state the obvious…
So there you go, a quick explanation and demo of why it’s important to deploy your server optimally from day one, because if you’ve already got it in production and it’s not working well, the only fix is to rebuild the sucker. You could have a saving grace though, if you deployed on Windows Server 2008, it does the alignment for you. For Windows Server 2003 and previous, it will not auto-align your partitions.
If you track back to my deployment document post it has my recommended best practices in there, which include disk alignment among a few other tips.
So that’s it for now, hope everyone is enjoying 2012 so far. Also off topic, but SQL Saturday 105 is rapidly closing in on us, and if you haven’t registered yet, get on it, because you’ll kick yourself if you miss this excellent free training day. Get on over and grab yourself a spot http://sqlsaturday.com/105/eventhome.aspx
Thanks,
Stephen