SQL Server : Auditing disk configuration

Disks or SAN volumnes presented for SQL Server are best formatted and aligned for optimal use.
Auditing an existing SQL server however, you will want to check whether this is the case.

Checking Cluster Size (File Allocation unit)
Use FSUTIL, like this -

C:\>fsutil fsinfo ntfsinfo s:
NTFS Volume Serial Number :       0x385ed1bb5ed171dc
Version :                         3.1
Number Sectors :                  0x00000000207fcb54
Total Clusters :                  0x00000000040ff96a
Free Clusters  :                  0x00000000017b10dc
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000022cfc0000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x000000000207fcb5
Mft Zone Start :                  0x00000000002a9720
Mft Zone End   :                  0x00000000008dff40

Bytes Per Cluster is the figure we are after. In the example above it is 4096 bytes (4K). This is NTFS default and is less than optimal for SQL Server (64K reads & writes).


Checking Partition Alignment
There are 2 methods of doing this. The first is WMIC -

C:\>wmic partition get BlockSize, StartingOffset, Name, Index
BlockSize  Index  Name                   StartingOffset
512        0      Disk #0, Partition #0  16384
512        0      Disk #1, Partition #0  32256
512        0      Disk #2, Partition #0  32256
512        0      Disk #3, Partition #0  32256
512        0      Disk #4, Partition #0  32256
512        0      Disk #5, Partition #0  32256

If WMIC is not available, DISKPART commands can reveal how partition alignment is configured.

C:\> DISKPART

DISKPART> select volume s:

Volume 5 is the selected volume.

DISKPART> list partition

Partition ### Type Size Offset

------------- ---------------- ------- -------

* Partition 1 Primary 37 GB 32 KB

DISKPART> exit


In the examples above the majority of partitions are aligned to 32K (32256 bytes), again less than ideal.

See Disk Formatting & Partition Alignment for SQL Server