Configuring your SQL File Systems
Configuring Your SQL File Systems
SQL Server will perform much better when the file systems are configured correctly. In this blog entry, I will show you how to validate the configuration on your database file system. In my next blog, we will look at the setup and performance of your files.
First, let’s start off with file systems. We need to make sure of the following:
· Partition starting offset
· Stripe Unit Size
· File Allocation Unit (cluster) size
Configuring these three settings has been commonly known to increase I/O performance by 10% to 30%.
Partition Starting Offset
What is this and why is it important? Before Windows Server 2008, when creating a file system the first 63 sectors were hidden. Windows Server stores data in tracks that consist of 64 sectors. Since 63 doesn’t equal 64, this creates a problem. When the operating system goes to write to this file system, it must first fill the 64th sector before beginning to write on the next track. This causes there to be multiple I/O’s to read this information which is inefficient.
Here is what you want your offsets to be set to:
· 32K - 32,768 (Exactly – if you are set to 32,256 this is misaligned)
· 64K – 65,536
· 128K – 131,072
· 256K – 262,144
· 1M – 1,048,576
To determine the offset for each filesystem, use the wmic command to get this information:
C:> wmic partition get blocksize, startingoffset, name, index
In the example above, Disk #2, Partition 0 is pointing to a file system that is not aligned correctly. Instead of 32,256, it should be set to 32,768. This would be for file systems that are not used by SQL Server. For file systems used by SQL Server, you want the starting offset to be set to 1,048,576 which is shown above Disk #13, Partition 0. In Windows Server 2008 and above, this is the default starting offset.
Caution: This does not work for dynamic disk. You must use the fsutil command which is not covered in this blog.
So what do you do if your file systems are misaligned? You have to backup the files on that file system and then re-format the drive to align it properly. That it is why it is very important to set them up correctly.
Stripe-Unit Size
With RAID technology, data is striped across multiple physical disks. The stripe-unit size is the amount of data stored on one disk before data is subsequently stored on another disk. This parameter should be set to closely match the size of the system I/O requests. The typical stripe-unit size is 8K, 16K, 32K, or 64K. You will need to check with your SAN Engineer to determine what the stripe-unit size is set to.
The formula to determine if these values are in line is Partition_Offset/Stripe_Unit_Size and it must return an integer value(whole number).
Here are two examples, one good and one bad on this configuration:
Example 1
· Partition offset = 32,768 (this disk is properly aligned)
· Stripe Unit Size = 65,536 (this is a proper stripe_unit_size)
· Partition_Offset/Stripe_Unit_Size = 32768/65535 = .5
· Still misaligned, the result of the formula is not an integer
Example 2
· Partition Offset = 65,536
· Stripe Unit Size = 65,536
· Partition_Offset/Stripe_Unit_Size = 65536/65536 = 1.0
· Volume is aligned properly
File Allocation Unit
File Allocation Unit which is sometimes referred to as a cluster, is the unit of disk space allocated for files and directories. This is the smallest logical amount of space that can be allocated to hold a file. On most file systems, you would want this to be as small as possible so there is not much wasted space, but on a file system that holds data files a large file allocation unit is desirable.
The formula to determine proper alignment is Stripe_Unit_Size/File_Allocation_Unit and it must also be an integer.
You can determine the allocation unit size using the chkdsk command:
C:> chkdsk E:
Example 1
· Stripe Unit Size = 65,536
· File Allocation Unit = 65,536
· Stripe_Unit_Size/File_Allocation_Unit = 65536/65536 = 1.0
· This is aligned properly
Example 2
· Stripe Unit Size = 65,536
· File Allocation Unit = 32,768
· Stripe_Unit_Size/File_Allocation_Unit = 65536/32768 = 2.0
· This is aligned properly
Example 3
· Stripe Unit Size = 32,768
· File Allocation Unit = 65,536
· Stripe_Unit_Size/File_Allocation_Unit = 32768/65536 = .5
· This is not properly aligned
By making sure that these parameters are setup correctly, you can ensure that your file systems are configured to maximize your I/O operations.
Caution: Please check with your disk vendor for their guidance on these settings. Most disk and SAN vendors provide best practices and guidance on these settings. In all situations you should test these type of settings to determine the optimal configuration for your company. For information on how to use SQLIO to baseline and test your disk subsystem you can reference the following blog: https://blogs.technet.com/b/lobapps/archive/2010/09/27/using-sqlio-to-baseline-performance-tips-and-tricks.aspx