Microsoft DIsk Tuning Notes ...

ms-disk-tune.txt

Basic Disk Partition Offsets: wmic.exe

Windows can be interrogated for disk-related information via Windows Management Instrumentation (WMI). A straightforward method for obtaining partition starting offsets of Windows basic disks is this Wmic.exe command.

Command Line Syntax
wmic partition get BlockSize, StartingOffset, Name, Index

The value for Index is the same as disk number in the Disk Management Microsoft Management Console (MMC) snap-in (Diskmgmt.msc); wmic volume can also be used to map disk indexes and drive letters.


Essential Correlations: Partition Offset, File Allocation Unit Size, and Stripe Unit Size

Use the information in this section to confirm the integrity of disk partition alignment configuration for existing partitions and new implementations.
There are two correlations which when satisfied are a fundamental precondition for optimal disk I/O performance. The results of the following calculations must result in an integer value:

Partition_Offset ÷ Stripe_Unit_Size
Stripe_Unit_Size ÷ File_Allocation_Unit_Size

Of the two, the first is by far the most important for optimal performance. The following demonstrates a common misalignment scenario: Given a starting partition offset for 32,256 bytes (31.5 KB) and stripe unit size of 65,536 bytes (64 KB), the result is 0.4921875. This is not an integer; therefore the offset & strip unit size are not correlated. This is consistent with misalignment.

However, a starting partition offset of 1,048,576 bytes (1 MB) and a stripe unit size of 65,536 bytes produces a result of exactly 8, an exact integer, which is consistent with alignment.

Note that file allocation unit (cluster) size commonly correlates with common stripe unit sizes. The performance question here is usually not one of correlation per the formula, but whether the cluster size is the NTFS default of 4,096 bytes or has been explicitly defined at 64 KB, which is a best practice for SQL Server.

Stripe Unit Size
Windows does not have a reliable way to determine stripe unit sizes. These values are obtained from vendor disk management software or from your SAN administrator.

File Allocation Unit Size
Run this command for each drive to see the file allocation unit size reported in bytes per cluster.

fsutil fsinfo ntfsinfo c:

The Bytes Per Cluster value, which contains the file allocation unit size, is highlighted here.

D:\>fsutil fsinfo ntfsinfo b:
NTFS Volume Serial Number :       0xa2060a7f060a54a7
Version :                         3.1
Number Sectors :                  0x00000000043c3f5f
Total Clusters :                  0x000000000008787e
Free Clusters  :                  0x000000000008746e
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x0000000000010000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x0000000000043c3f
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x000000000001cf20

An appropriate value for most installations should be 65,536 bytes (that is, 64 KB) for partitions on which SQL Server data or log files reside. In many cases, this is the same size for Analysis Services data or log files, but there are times where 32 KB provides better performance. To determine the right size, you will need to do performance testing with your workload comparing the two different block sizes.


How to measure disk performance?

In this section we are going to discuss which counters are the key to measuring disk performance. Generally we have 4 counters used for performance measurement: Disk Bytes/sec, % Idle Time, Disk sec/Transfer and Avg. Disk Queue Length.
 
Disk Bytes/sec
From the formula, Disk Bytes/sec is actually how many bytes have been completed in every second. There are two things could impact this counter value:
 
1. How much stress is generated to the disk or volume?
Let’s assume if there are no problems with disk performance and stress has not reached the storage bottleneck. Then, this counter value will be determined the stress IO load generated by the application such as a stress tool.
 
2. Disk performance
If the IO load has exceeded the storage bottleneck, this counter value will not be able to be increased with load increasing.
 
Conclusion: Since this counter value could be affected by IO load from an application we cannot use it as the key to determine disk performance.
 
% Idle Time
This counter value indicates how long the disk is in idle status without outstanding IO. It can help to determine how busy the disk is. However, even if the disk is busy with 0% Idle Time, we cannot say it suffers from a performance issue as it may still be able to complete all IOs in time.
 
Avg. Disk Queue Length
This counter indicates on average how many IOs are outstanding. If the disk can always complete IO immediately, the value should be 0. Therefore, it’s also a value to determine how busy the storage is. But it does not impact the application directly as the application does not care how many total IOs are outstanding. The application is concerned with how fast every IO can be completed. In practice, if we see the queue depth is more than 10 we may say the storage is busy and could delay the IO in the queue. However if every IO can be completed fast there will be no impact to the application, which means the delay is still acceptable.
 
Disk sec/Transfer
This counter indicates how fast the IO is completed on average. This is one of the keys to an application’s performance as discussed above.