如何确定I/O瓶颈

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: Detecting and Resolving I/O Bottleneck By : Kasim Wirama, MCDBA   I/O subsystem is one of critical component in SQL Server.

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.

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
ly~
|
12天前
|
存储 监控 Linux
如何确定 FileRun 性能的瓶颈所在?
监控服务器资源使用情况,包括CPU使用率、内存使用量、磁盘I/O性能和网络带宽占用,确保FileRun运行顺畅。同时,分析数据库性能,如查询执行时间和连接数,以及检查FileRun内部操作日志,评估用户行为和并发访问对系统的影响。
ly~
25 4
|
5月前
|
Java 数据库 图形学
论系统的木桶理论与性能瓶颈
论系统的木桶理论与性能瓶颈
61 7
|
算法 前端开发 应用服务中间件
高并发环境如何有效缓解带宽压力
高并发环境如何有效缓解带宽压力
性能瓶颈总结1
性能瓶颈总结1
85 0
性能瓶颈总结1
|
消息中间件 缓存 NoSQL
瓶颈分析|学习笔记
快速学习瓶颈分析
|
网络协议 算法 Java
高并发场景下如何优化服务器的性能?
最近,有小伙伴在群里提问:Linux系统怎么设置tcp_nodelay参数?也有小伙伴说问我。那今天,我们就来根据这个问题来聊聊在高并发场景下如何优化服务器的性能这个话题。
434 0
高并发场景下如何优化服务器的性能?
cudaMemcpy的性能问题
cudaMemcpy的性能问题
233 0
|
存储 弹性计算 NoSQL
突破内存应用瓶颈,让IT成本下降40%的秘诀
近两年5G、大数据、云计算一直为行业热点,数字化进程不断加速,全行业数据开始爆发式增长。面对数据的迅猛增长,企业一方面享受着数据化转型带来的红利,另一方面也承担着大内存运行实例的高额开支。传统内存面临挑战,持久内存方案开始受到了行业更多的关注。
突破内存应用瓶颈,让IT成本下降40%的秘诀
|
消息中间件 缓存 应用服务中间件
高并发下怎么优化能避免服务器压力过大?
用户多,不代表你服务器访问量大,访问量大不一定你服务器压力大!我们换成专业点的问题,高并发下怎么优化能避免服务器压力过大? 1,整个架构:可采用分布式架构,利用微服务架构拆分服务部署在不同的服务节点,避免单节点宕机引...
1345 0