How to identify IO bottlenecks in SQL Server

Original: How to identify IO bottlenecks in SQL Server

Original from:

http://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/

Problem:

We may often encounter a situation where SQL Server databases are shut down frequently. After analyzing memory and CPU usage, we need to continue to investigate whether the root cause is in I/O. How do we identify if SQL Server has an I/O-related bottleneck?

Solve:

When data pages are often moved out of the buffer pool, the I/O subsystem becomes one of the key factors in SQL Server performance issues. The transaction log and tempdb also generate significant I/O pressure. Therefore, you must make sure that your I/O subsystem works as expected. Otherwise you will be the victim of response time growth and frequent timeouts. In this article, you will describe how to use the built-in tools to identify I/O-related bottlenecks and provide some disk configuration methods:

Performance counters (performance Monitor):

You can use performance counters to check the load on the I/O subsystem. The following counters can be used to check disk performance:

PhysicalDisk Object:Avg.DiskQueue Length: calculates the average read and write request queue from the physical disk. An excessively high value indicates that the disk operation is in a waiting state. When this value is longer than 2 over the peak of SQL Server, it is important to note that. If you have more than one hard disk, you need to divide these values by 2. For example, there are 4 hard disks, and the queue is 10, then the average is 10/4=2.5, although it also proves to be concerned, but cannot use the value of 10.

Avg.Disk Sec/read and Avg.Disk sec/write: Displays the average time that is read from disk or written to disk. 10ms is a good performance, 20 of the following can be accepted. Above this value proves that there is a problem.

Physical Disk:%disk Time: The rate of duration when the disk is busy reading or writing requests. According to the thumb law, this value should be less than 50%.

Disk reads/sec and disk writes/sec counters show the rate of read and write operations on disks. These two values should be less than 85% of the capacity of the disk. When this value is exceeded, the disk's access time will grow exponentially.

The ability to calculate the incrementally increasing load can be done in the following ways. One way is to use Sqlio. You should find the throughput relatively stable, but slowly grow.

You can use the following formula to calculate the RAID configuration:

Raid 0: I/o per disk = (reads + writes)/number Ofdisks
Raid 1: I/o per disk = [reads + (WRITES*2)]/2
Raid 5: I/o per disk = [reads + (WRITES*4)]/ number of disks
Raid Ten: I/o per disk = [reads + (WRITES*2)]/ number of disks

For example: For RAID 1, if you get the following counter:

Disk reads/sec = 90
Disk writes/sec =75

According to the formula:[reads + (WRITES*2)]/2 or [+ (75*2)]/2 = 120i/os per disk.

dynamic management view (DMVs):

There are many swimming DMVs that can be used to check I/O bottlenecks:

When a page is used for read or write access and the page does not exist or is unavailable in the buffer pool, an I/O latch wait (I/O latch) is raised and it is pageiolatch_ex/pageiolatch_sh (depending on the type of request). These waits indicate an I/O bottleneck. You can use Sys.dm_os_wait_stats to find the information for the latch waits. If you save the waiting_task_counts and Wait_time_ms values under normal operation of SQL Server, and compare this value, you can identify the I/O problem:

SELECT *

From Sys.dm_os_wait_stats

where wait_type like ' pageiolatch% '

ORDER BY wait_type ASC

Pending I/O requests can be found in the following query and are used to identify the bottleneck that the disk is responsible for:

Io_stall,

Io_pending_ms_ticks,

From sys.dm_io_virtual_file_stats (null, NULL) IOVFS,

Sys.dm_io_pending_io_requests as Iopior

where Iovfs.file_handle = Iopior.io_handle

Disk Fragmentation:

We recommend that you check disk fragmentation and configure the disks that are used for SQL Server instances. Fragmentation in the NTFS file system can have a serious performance impact. The disk needs to defragment frequently and specify a defragmentation plan. Studies have shown that in some cases the SAN performs worse after defragmenting a fragment. Therefore, the SAN must be treated according to the actual situation.

Index fragmentation on NTFS can also cause high I/O to be useful. But this is not the same as the effect in Sans.

Disk Configuration/Best practices:

As a general rule, you should store log files and data files separately for better performance. The I/O characteristics for heavy-load data files (including tempdb) are random reads. For log files, sequential access is required, unless the transaction needs to be rolled back.

The built-in disks can only be used for database log files because they have good performance for sequential I/O, but are low on random I/O performance.

The data and log files for the database should be placed on the corresponding dedicated disk. Ensure good performance. It is recommended that log files be placed on two internal disks and configured as RAID 1. Data files reside in San systems that are only used for SQL Server access and are only controlled by queries and reports. Special access should be banned.

Write buffers should be allowed, where possible, and can be used for power outages.

To minimize the impact of I/O bottlenecks on OLTP systems, you should not mix OLAP and OLTP environments. and ensure that your code is optimized and that you have the appropriate indexes to avoid unnecessary I/O.

How to identify IO bottlenecks in SQL Server