Troubleshooting SQL Server Reading Notes-disk I/O Configuration

Chapter 2 Disk I/O Configuration.

For SQL Server, disk I/O configuration is mainly for database workloads. Consider and weigh two points:

1. disk capacity VS Disk Throughput

A 1 TB database is placed on a 2 TB disk. The capacity is enough, but can the Disk Throughput meet the workload? RAID is usually used. An appropriate RAID level is also a result of balancing capacity and throughput.

2. Sequential io vs. Random I/O

Database Log File operations are usually sequential IO, and data files usually have a lot more random IO operations. The sequential I/O performance of disks is higher than that of random I/O, because the former requires moving the head, and the latter does not.

It is important to isolate databases on storage in different I/O modes of workload.

Chose the right RAID level)

Benefits of RAID: 1. Enhanced IO performance 2. Increased IO throughput 3. Increased available capacity of a single logical device 4. data redundancy

The RAID level depends on the workload type. For example, if the database log files are written in sequence, the RAID write performance must be considered. The data files must be balanced between reading, writing, and available capacity.

RAID 0: provides data strip and efficient I/O performance, but does not provide data redundancy protection. Therefore, SQL Server is generally not used.

RAID 1: provides full data redundancy protection, low IO performance, and high costs. You can consider placingSingle. If multiple transaction log files are placed, the sequential IO operations of each log file are intertwined,

It becomes random IO, and the performance will decrease.

RAID 5 (6): provides data redundancy protection with less capacity loss and efficient read performance. However, the write performance is relatively low. Each time a piece of data is updated, you need to re-calculate and update the parity data. When a disk fails, the performance of the entire RAID will drop sharply,

Because the data on the invalid disk is read, it must be verified and calculated. RAID6 is an extension of RAID5. It only contains two copies of verification data on different disks, but the available capacity is only half of the total disk size and at least four disks.

In this way, RAID10 is better. You can consider placing data files that read more than write.

RAID10: first form RAID1 and then use RAID1 to form RAID0. The capacity is only half of the total disk capacity, providing data redundancy protection and relatively fast write performance. A maximum of one disk can be deactivated in each raid 1 group.

Read performance is slower than RAID 5 of the same number of disks.

RAID01: first form RAID0 and then use RAID0 to form RAID1. Only one group of RAID0 is allowed to expire. When any disk fails, the entire RAID01 is invalid. The risk of data loss is higher than that of RAID 10.

In addition to the hard performance of the disk, there are also some important factors that affect its performance:

1. cache size and configuration of the RAID Controller 2. RAID strip size 3. Partition alignment 4. NTFS formatted file cluster size

Make sure to perform a benchmark test to check whether the configuration of the IO subsystem is correct,Cannot be obsessed with theoretical data. Recommended benchmark testing tools: SQLIO and IOmeter, and SQL Server stress testing tool SQLIOSim

It is best to physically isolate data files, log files, and tempdb.

Data Files: when reading far more data files than writing, read-only, or write latency does not affect system performance, it can be placed in RAID5 or RAID6. on the contrary, you can consider RAID10.

Log Files: You can consider RAID 1 and raid 10. Multiple frequently written log files are placed on the same physical disk or disk fragments increase the write pressure.

Tempdb: its function determines that it is a write-intensive database. It is best to physically isolate it from the user database and place it in raid1 or RAID10. Of course, it can also be placed on SSD and RAMDisk.

You can create multipleSame ConfigurationData files to reduce system page emergency problems.

 

Das vs. SAN

The DAS can be used simply by attaching it, with predictable performance, no additional experience maintenance required, and no advanced SAN features (such as supporting clusters, disk array images, and array-based replication ). Relatively cheap.

SAN is suitable for enterprise-level storage and is relatively expensive. HoweverIt is used to optimize storage usage, not necessarily to optimize storage performance.

Additional storage experience or external resources (SAN administrator or supplier) are required for troubleshooting ).

 

Diagnose disk I/O Problems

Two important performance counters: Avg. Disk sec/Read and Avg. Disk sec/Write.

Generally, their values are <10 ms = Good, 10 ~ 20 ms = a little slow, 20 ~ 50 ms = very slow,> 50 ms = performance problems.

IO bottlenecks include PAGEIOLATCH _ *, ASYNC_IO_COMPLETION, IO_COMPLETION, or WRITELOG.

 

Common disk I/O Problems

Before diagnosing disk problems, make sure that the system does not have other bottlenecks.

1. Only considering capacity rather than performance

A 1 tb oltp database is placed on a 2 TB disk with sufficient capacity and performance. SAN networks and disks are not exclusively owned by SQL Server, and SQL Server does not know whether the LUN is an independent physical disk.

Most of the time, it is shared with other applications. When diagnosing faults, it is important to pay special attention to whether the I/O indicators are only caused by SQL Server.

2. Error load isolation

Data files, log files, and tempdb must be physically isolated because IO methods are different. Pay special attention to whether the storage units allocated by SAN to SQL Server are physically isolated from other applications.

3. Incorrect partition alignment

Refer to Storage Area Network (SAN) for DBA's

4. Incorrect SAN bandwidth Configuration

Do not be obsessed with theoretical values or the data provided by suppliers. Make sure that the performance of the SAN meets the workload, especially the multi-channel SAN.

 

Summary

For storage, you must have a clear plan and be tested (benchmark and pressure ).