VIII. I/O optimization (1) disk Cluster

I. Hard Disk Structure

1. Basic hard disk structure

The most basic component of a hard disk is a disk coated with magnetic media made of hard metal materials.

The number of disks varies with the disk capacity. Each disk has two sides, and each side has a Head). It is used to distinguish between Head numbers. The disk is divided into many slice areas, each of which is called a Sector). Each slice can store 128x2 n N = 0.1.2.3) bytes of information. In DOS, each slice is 128 × 2 ^ 2 = 512 bytes. The disc surface is centered around the disc. The concentric circles with different radius are called Track ). Cylinder is a Cylinder consisting of tracks with the same radius of disks in a hard disk ).

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/21232133U-0.png "title =" structured structure .png "alt =" 142.166300.png"/>

Sector, track, or cylinder) and the number of Magnetic Heads constitute the basic parameters of the hard disk structure. These parameters can be used to obtain the capacity of the hard disk. The formula is: storage capacity = number of magnetic heads × Number of Magnetic Track cylinders) quantity × number of sectors × number of bytes per sector

2. Disk Cluster

"Cluster" is the minimum unit for DOS distribution. When creating a small file, if it is a byte, it occupies not only one byte of space on the disk, but a whole cluster. DOS depends on different storage media, such as floppy disks and hard disks), hard disks of different capacities, and cluster sizes. The cluster size can be obtained in the disk parameter block BPB. The concept of cluster is only applicable to data areas.

By default, if the disk cluster size is not specified during formatting, the system selects the default cluster value based on the partition size. In fact, when formatting in the NTFS file system, you can add the "/a: UnitSize" parameter after the "Format" command to specify the size of the disk cluster. UnitSize indicates the value of the cluster size, NTFS supports 512/1024/2048/4096/8192/16 K/32 K/64 K. For example, "format d:/fs: NTFS/a: 2048" indicates that the d disk is formatted using the NTFS file system, and the disk cluster value is 2048B.

Generally, you do not need to manually set the size of the disk cluster. You can use the default settings. For example, when using the NTFS file system to format partitions, the system automatically selects the default cluster size, such as 4 kb, based on the partition size.

In the NTFS file system, the size of the cluster affects the arrangement of Disk Files. Setting an appropriate cluster size can reduce the disk space loss and the number of shards. If the disk cluster is too large, the disk storage efficiency will be affected. If the disk cluster is too small, although the utilization efficiency will be improved, a large number of disk fragments will be generated.

3. Reasons for disk I/O bottlenecks

There are two main factors that affect disk read performance: track time and polling latency. When querying data, there are two disk read Methods: sequential read and random read. When a random read occurs, the head needs to move and locate the Track and Sector. This takes a long time. Once the head is positioned, there is basically no difference between the time consumed to read a single Sector and several successive Sector.

When SQL Server reads data files, it usually reads the corresponding records in the logical order of records. If the logical adjacent data pages are not physically distributed, the performance will be greatly compromised by the back-and-forth movement of the head.

Ii. disk storage units in SQL Server

1. page) and zone extend)

SQL Server stores 8 KB pages of data. Even if you only need to read one record, SQL Server reads the entire page of the record.

When allocating data pages, eight consecutive pages form a zone extend) for unified allocation management.

2. Pre-read

The SQL Server Enterprise Edition provides a pre-read mechanism. When you read a page, the adjacent page is read to the memory at the same time, and the maximum size of the page is 1 MB. If the data to be accessed in the next step is located on the adjacent page just read, it will greatly benefit from the pre-read.

3. Suggestions

Based on the organizational structure and pre-read mechanism of the zone, we can format the disk as a 64 kB cluster. When reading an 8 KB page, the actual disk reads 64 KB. However, because the cluster is a continuous sector, the impact on the performance of read-only data is negligible.

Therefore, we can format the disk partition dedicated to SQL Server storage into a 64 kB cluster, which can improve performance without wasting space.

Iii. Slice alignment

1. Volume Alignment)

If an NTFS Volume is created on a RAID device, Volume alignment is usually required. If the volume alignment fails, the performance may degrade significantly. Because volume is generally called a partition in China, it is commonly known as "partition alignment ".

In the early days, a disk uses bytes as a sector. As hard disk capacity increases, this standard is not only cumbersome, but also reduces efficiency. Therefore, the subsequent hard disk capacity will be changed to 4096 Bytes per sector, which is commonly known as "4 K Sector ". It is possible that the NTFS sector write point is located exactly between two physical 4KB sectors. That is to say, even if NTFS writes the smallest amount, two physical sectors will be used, obviously, this will have a great impact on writing and reading.

For example, the upper block shows that LBA 0 is aligned with the first physical 4KB block, which is called Alignment 0; the lower block shows the 2nd 512-byte block that the LBA0 is aligned to the physical 4 kb slice. This is called Alignment 1.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/2123214R6-1.png "title =" fan district to Qi .png "alt =" 102707877.png"/>

Because there are eight possible LBA start points, seven sectors may not be aligned, these events will cause the "Read-Modify-write" event similar to Alignment 1.

2. 4 K alignment

For Windows 7 and Windows Server 2008 operating systems, if you use a built-in system tool for formatting, the formatted partition will be "4 K aligned" by default, you do not need to perform any further settings. If you are still using a lower version of the operating system, you need some tools to achieve "4 K alignment. For example: Disk performance may be slower than expected when you use multiple disks in Windows Server 2003, in Windows XP, and in Windows 2000 http://support.microsoft.com/default.aspx? Scid = kb; EN-US; 929491

Conclusion:

Format the disk partition into a 64 kB Cluster Based on the Organization Characteristics of the SQL Server data page.

This article is from the "MSSQL we have Chased Together" blog. For more information, contact the author!