How To Use Database Disks with Maximum Performance

To understand this article, we need to know the following concepts.

Page: It is the most basic unit in SQL Server storage. Each Page consists of 8 KB fields.

Extent: Consists of a combination of 8 physical pages. Their size is 64 KB. Extents help manage pages efficiently. There are two kinds of Extent.

• Mixed Extent: Consists of a combination of pages of the different objects.
• Uniform Extent: Consists of a combination of pages of the same object.

Allocation Unit Size: The smallest space on a disk to hold a file. The default value is 4 KB. The recommended best practice for SQL Server is 64 KB.

Suppose that SQL Server wants to read 1 Uniform Extent (64 kb) data. And suppose that this data is not in memory. If we choose 4 KB as the allocation unit size when formatting your disk, it will need to access the disk 16 times to perform this read operation. Each extra access to the disk will reduce performance. When you format the disk to 64 KB, you will have to access only 1 times when we want to read SQL Server 1 Uniform Extent (64 KB).

As the data size increases, the effect of this process on performance will gradually increase. You can find the Allocation Unit Size that your disk is using by typing the following command in the cmd command line.

You should write the name of your disk instead of “o”. You can see the allocation unit size  in the section “Bytes Per Cluster:”.

As shown, the allocation unit size of our disk is 65536 bytes (64 KB)

Below you can see how to select the allocation unit size when formatting our disk.

Right-click on the disk and click Format. We then select Allocation Unit Size as 64 KB. Of course, do not forget to copy the data on your disk to another location before doing this. If you have database files on this disk, you must move your databases to another disk before performing this operation.