未加星标

Get To Know How SQL Server Manages Disk I/O

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2019 | 作者 红领巾 ] 0人收藏点击收藏

SQL users often face the issue of slow SQL Server database, which is caused by I/O related bottlenecks in SQL Server. I/O subsystem is a major element of the performance of SQL Server because the pages keep moving in and out of buffer pool. Similarly, tempDB and SQL transaction logs also cause I/O traffic. Therefore, users have to ensure proper performance of I/O subsystem. In this write-up, we will describe how SQL Server manages disk I/O to identify and avoid bottlenecks.

Using Performance Monitor

Performance Monitor is a popular way to check how much load the I/O subsystem is having. For this, set any of the following performance counters:

PhysicalDisk Object: Avg. Disk Queue Length:This counter indicates average number of queued read and write requests of the chosen physical disk. The more the value, the more disk operation is in queue.

Avg. Disk Sec/Read and Avg. Disk Sec/Write:This counter displays the average time taken to read/ write data from/ to disk. Any higher value than 20 ms requires attention.

Physical Disk: %Disk TimeIt shows the ratio of elapsed time when the disk drive is engaged with read or write requests. Its standard value is less than 50 percent.

Disk Reads/Sec and Disk Writes/Sec:These counters show the rate of read and write operations on I/O disk, which should not be more than 85 percent of the disk capacity.

The disk capacity can be determined by gradually accelerating the load of the system. A good method to do so is to use SQLIO.

Dynamic Management Views

Some Dynamic Management Views (DMVs) can also be used to check I/O bottlenecks:

Sometimes, users try to access a page to read and write, but that is not available in the buffer pool at that time. This results in the occurrence of an I/O latch wait. Based on the type of request that has been made, it gives rise to waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH. These waits also indicate I/O bottleneck. Find out about the latch wait statistics by sys.dm_os_wait_stats DMV. Also, compare waiting_task_counts and wait_time_ms values of normal time during poor performance to find out I/O problems.


Get To Know How SQL Server Manages Disk I/O

Use these DMVs to find out the pending I/O requests and disks responsible for the bottleneck.


Get To Know How SQL Server Manages Disk I/O
Perform Best Practices of Disk Fragmentation and Disk Configuration

Check disk configuration and disk fragmentation to manage disk I/O.

(i)Disk Fragmentation:

Since NTFS file fragmentation decreases the disk performance, disk defragmentation should be done on a regular basis. At the same time, a policy regarding the defragmentation process should exist.

In certain situations, a SAN performs worse when the defragmentation is enabled, so SANs need to be handled individually, like one case at a time. If there is fragmentation in Index, NTFS I/O utilization also becomes high. However, in case of SANs that offers better performance on random I/Os, it will have different impact.

(ii)Disk Configuration:

Some approaches about disk configuration should be performed for the proper management of disk I/O. For example, log files should be kept separately from the data files on a physical disk.

In database data files that are being used heavily, the I/O profiles are random. At the same time, the I/O profiles are sequential in case of all database log files. The only exception is the transactions that require rollback. Use internal disks only for database log files, as these disks perform best in case of sequential I/O, but badly for random I/O.

All the data of the database and their log files should have their own data packs. It is advised to place the database log files on two internal disk drives configured as RAID 1, for the most comprehensive performance. Keep the database data file on a SAN system and make sure that it gets accessed only by SQL Server as well as receive controlled query and report. Any kind of Ad-hoc access must not be allowed.

One every possible instance, enable write caching and also, protect the cache power defect and all other possible failures.

For OLTP system, keep OLAP and OLTP environments separate to avoid bottleneck. Avoid unnecessary I/Os by creating indexes where necessary.

Final Words

Since SQL Server bottleneck can be avoided by disk I/O management, people often want to know how SQL Server manages disk I/O. This post discussed various techniques of disk I/O management that can be done on SQL Server. Users can easily follow the processes mentioned here for smooth performance of SQL Server.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

代码区博客精选文章
分页:12
转载请注明
本文标题:Get To Know How SQL Server Manages Disk I/O
本站链接:https://www.codesec.net/view/628507.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(158)