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. PhysicalDisk: Avg. Disk Queue Length
If you encounters value 2 or more when SQL Server is under peak usage, you have I/O bottleneck.
- 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.
- 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.
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现