Storage Area Network (SAN) for DBA's

I talked to my friends about how to improve the performance of SQL Server and found some problems. For SQL Server, in addition to following the best practices of OS and applications, what are the optimization methods?

Hardware-level optimization is also very important, especially storage optimization. After talking about this, we started to explain the SAN issue from the DBA's perspective.

Many factors affect the disk I/O performance, such as shared or dedicated disks, RAID level, bus speed, and HBA configuration. There is another important but often overlooked factor:Disk Partition alignment(Alignment in disk partitions ).

Some other non-disk factors may also cause bottlenecks:

-Hba and Nic

-FC processing capability and iSCSI Port

-Number of server and storage paths

-Vswitch

It is important to understand and consider the limitations of related components in the architecture. The following are restrictions on some components on the market:

 

But how are these associated with SQL Server?

The basic unit of data storage in SQL Server is Page (8 KB ). the space allocated to data files (mdf or ldf) in the database is logically divided into consecutive pages numbered from 0 to N. Disk IO operations are implemented on pages.

A hard disk consists of several slices (Platter. A circular Disk is an electronic media used to store information. Each side of the slice has thousands of Tracks ). A thin disk has a magnetic track of the same diameter to form a Cylinder ).

The magnetic column makes no sense for new devices because they are not arranged in concentric circles. However, this is helpful for understanding the ins and outs of terms.

Each disk surface has a dedicated header for I/O. The track is divided into sectors. The sectors are the smallest read/write fragments of the disk. The new device can provide 1 kb, 3 kb, or 4 kb sectors.

RAID technology is used to strip data through a set of physical disks. This data distribution is complementary to the way the operating system requests data.

Before subsequent data is stored on the next disk in the array, the granularity of the data stored on the current disk in the array is calledStripe unit size(Stripe-unit size ). (TRANSLATOR: This is a detour. Simply put, after data is striped, it is divided into several small pieces and stored on the disk of the array. This "Block" is called the stripe unit, and its size is called the stripe unit size .) The set of band units from the first disk in the array to the last disk is calledStrip(Stripe ).

 

Is this partition alignment? Does this affect SQL Server performance?The following illustration will explain these.

The black dotted line that overwrites the red gradient line represents the boundary of a single disk in a group of arrays. For Windows Versions earlier than Windows Server, 63 sectors are retained to record disk hardware information. Other partitions are available to users.

Generally, the slice size is 512 bytes, the band unit size in the figure is 64 KB, and the default NTFS Cluster size is 4 kb. (TRANSLATOR: A cluster is a set of slices and the smallest unit of storage space allocated on the disk)

The absence of disk alignment forces the cluster of the eighth user data to span two strip units. From the last blank sector of the first strip unit to the second strip unit. In this case, the remaining part of the entire partition is continued. Because each nth cluster that spans two stripe units executes one read or write operation, it will lead to two IO operations.

We recommend that the size of the file allocation unit (that is, the cluster size) for SQL Server be 64 KB. The preferred 64 kB cluster size of SQL Server causes default partition misplacement, forcing the user data cluster to span two strip units. Starting from the first available sector of the first strip unit until the second strip unit. This will continue the rest of the partition. This configuration is destined for reading and writing of each user data cluster in the future, and will span two channels or strip units.

 

The following is an experiment data to test disk performance, based on Windows Server2003 & SQL Server 2005. Run a query to obtain information from SQL Server. Dbcc dropcleanbuffers is executed every time to clear the Cache (Buffer Cache) to ensure that the results are trusted. We have monitored the performance counters Avg. Disk Transfer/Sec used to measure Disk latency. The results show that the disk alignment Duration and Latency performance is significantly improved, increasing by more than 30%.

To verify disk alignment, you need to understand the following concepts:

Shard start offset(Starting Partition Offset): the start Offset of the Partition in the volume is used to determine whether the default data page is allocated to the hidden sector in the early volume.

 Stripe unit size(Stripe Unit Size): the granularity of the data stored on the current disk in the array before subsequent data is stored on the next disk in the array. This is provided by the SAN administrator.

File Allocation unit size(File Allocation Unit Size): the legendary cluster Size. The minimum unit for allocating disk space. This value is determined when the OS is formatting the partition.

For correct disk partitioning, two rules must be followed: the following two calculation results must be integer values.

Partition_Offset/Stripe_Unit_Size
Stripe_Unit_Size/File_Allocation_Unit_size

The first one in the previous two is very important for performance optimization.

Examples of disk Non-Alignment:

The starting offset of a disk partition is 32256 bytes (31.5Kb), and the size of the band unit is 65536 bytes (64Kb ). Partition_Offset/Stripe_Unit_Size = 0.4921875, not an integer, so the disk is not aligned.

Disk alignment example scenario:

The starting offset of a disk partition is 1048576 bytes (1 Mb), and the size of the band unit is 65536 bytes (64Kb ). Partition_Offset/Stripe_Unit_Size = 8, which is an integer, so the disk is aligned.

 

After the above explanation, the following questions need to be answered:How can I know the alignment of a disk?

We can use the WMIC command to obtain the offset of a disk. The syntax is as follows:

Wmic partition get BlockSize, BootPartition, DeviceID, DiskIndex, HiddenSectors, NumberOfBlocks, StartingOffset, Name, Index

This command can check the value of SaartingOffset. You can also learn StartingOffset through DISKPART, and select the disk you want to analyze and execute the list partition command to know the first offset. To check the Offset of a dynamic disk, use the dmdiag tool that enables the-v Switch.

If you can use the following command to check the size of the file allocation unit:

Fsutil fsinfo ntfsinfo [drive]

The following figure shows all the commands above:

Summary

In order to achieve the best performance of a disk. To create a disk partition on Windows 2003, you must strictly verify the correct relationship between the disk partition size and the cluster size, and use it as the best practice. In Windows 2008, partitions are aligned by default.

When the server is updated from Windows 2003 to 2008, existing partitions are not automatically aligned and need to be rebuilt to optimize performance.

Bytes ----------------------------------------------------------------------------------------------------------------

Joe. TJ translation, original address: Storage Area Network (SAN) for DBA's

Bytes ---------------------------------------------------------------------------------------------------------------

My summary:

1. Disk Partition Alignment Best Practices for SQL Server is excerpted from many original documents.

2. I saw this article when I was looking for SAN-related resources. After reading this article, I found that there was nothing about SAN, and I was suspected of being a title party. However, the introduction to storage is meaningful and translated.

3. "missing disk alignment forces the cluster of the eighth user data to span two strip units .", This is a translation of the original text and reference Disk Partition Alignment Best Practices for SQL Server.

I am personally puzzled by this. The 64 kb band unit is 128 sectors, 63 hidden sectors are subtracted, and 65 sectors are available to users. Each user data cluster is 4 kb = 8 sectors, then 8 user data clusters are 64 sectors.

65-64 = 1, so it should be 9th user data clusters that span two strip units, rather than 8th.