如何确定I/O瓶颈-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

如何确定I/O瓶颈

简介:

Detecting and Resolving I/O Bottleneck

By : Kasim Wirama, MCDBA

 

I/O subsystem is one of critical component in SQL Server. I/O subsystem is used when SQL Server moves page between memory and I/O subsystem. With intensive activity of DML and DDL, SQL Server generates significant log entries and to tempdb database if the activities are creation and operations of table variable, temporary table, sorting, create and rebuild indexes and row versioning technology. Let’s look how to detect I/O bottleneck and solution to this issue.

 

Here are performance counter for detecting I/O bottleneck :

 

  1. 1.       PhysicalDisk: Avg. Disk Queue Length

If you encounters value 2 or more when SQL Server is under peak usage, you have I/O bottleneck.

  1. 2.       PhysicalDisk: Avg. Disk Sec/Read and Avg. Disk Sec/Write

These counter names gives information about average value on how fast your disk operates under read and write activity. You need to pay attention for I/O subsystem when the value is more than 20 ms.

  1. 3.       PhysicalDisk: Disk Reads/sec and Disk Writes/sec

These counter names gives rate of read and write operation. If the value is at least 85 percent of disk capacity, the I/O subsystem experiences bottleneck.

 

Unfortunately these counters above measures I/O subsystem on hard disk level not in file level. If you have several files in an I/O subsystem, you need to have information from DMV sys.dm_io_virtual_file_stats, looking at io_stall_read_ms and io_stall_write_ms. Run the DMV several times in intended duration to get delta of these values.

 

You might have 3 possibilities of I/O subsystem issue. It might be caused by inefficient queries that effects to I/O intensive operations, lack of indexes or the disk subsystem needed to be upgraded to accommodate anticipated workload. You can find out I/O intensive query by querying DMV sys.dm_exec_query_stats and sort descending order for sum of total_logical_reads and total_logical_writes. To find out lack of indexes in an underlying table you issue DMV query below :

 

SELECT t1.object_id, t2.user_seeks, t2.user_scans, t1.equality_columns, t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1, sys.dm_db_missing_index_group_stats AS t2, sys.dm_db_missing_index_groups AS t3
WHERE database_id = DB_ID(‘your database’) AND object_id = OBJECT_ID(‘your table’) AND t1.index_handle = t3.index_handle AND t2.group_handle = t3.index_group_handle

SQL Server 2005 exposes I/O performance information through least performance impact DMV, so that you can quickly spot and fix the I/O bottleneck issue.




    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2011/03/28/1997668.html,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章