使用sys.dm_io_virtual_file_stats了解你的数据库IO

2024-01-13 16:18

本文主要是介绍使用sys.dm_io_virtual_file_stats了解你的数据库IO,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

因为sys.dm_io_virtual_file_stats 返回数据和日志文件的 I/O 统计信息,包括对文件发出的读取/写入次数以及总字节数,所以这个函数既可以看到IOPS也可以看到吞吐量,还可以计算出单个IO的大小。另外也可以看到IO的等待时间。能够帮助我们很好的了解数据库的IO状况。
配合Perfmon一起使用可以很快的找到IO瓶颈。
下面的文章来自微软的以为高级工程师:
http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx

One of the DMVs I try to utilize on any engagement where customers are complaining about disk issues is the sys.dm_io_virtual_file_stats DMV where you can look at the IO stalls for both reads and writes. The sys.dm_io_virtual_file_stats DMV will show an IO Stall when any wait occurs to access a physical data file. IO Stalls are recorded at the file level and you can also obtain the IO Stalls at the database level directly out of the DMV.

By getting this information it is very easy to ORDER BY io_stall_read_ms, io_stall_write_ms, or by io_stall which is an accumulation of reads and writes.

One addition step I have made in the script below is mapping to the sys.master_files catalog view and using the substring function to get the physical disk drive letter. You will now be able to see IO Stall activity at the file, database, and the drive letter. You can then use Reporting Services or simply use Excel to get a quick view of which of these is absorbing most of the IO Stall impact.

If you use Excel 2007, one of the interesting strategies is to use the Chart Advisor from Live Labs.
http://www.officelabs.com/projects/chartadvisor/Pages/default.aspx

This analysis can help make decisions around table partitioning and potentially file and index placement. Of course, this will all depend on the customer's SAN and other constraints.

Note: Mount points will make getting the drive letter less effective. If you are using mount points then just ignore the drive letter column.

SELECT a.io_stall, a.io_stall_read_ms, a.io_stall_write_ms, a.num_of_reads,
a.num_of_writes,
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
db_name(a.database_id) AS dbname,
b.name, a.file_id,
db_file_type = CASE
WHEN a.file_id = 2 THEN
'Log'
ELSE
'Data'
END,
UPPER(SUBSTRING(b.physical_name, 1, 2))AS disk_location
FROM sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN sys.master_files bON a.file_id = b.file_id
AND a.database_id = b.database_id
ORDER BY a.io_stall DESC

For those looking at disk issues, I have pasted the general guidance on the avg. reads/sec and avg. writes/sec values for perfmon. By using the script above and the guidance here on perfmon, you should be able to take the next steps in addressing disk performance issues with your customers.

I/O Bottlenecks

SQL Server performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQL Server constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. Similarly, the log records need to be flushed to the disk before a transaction can be declared committed. And finally, SQL Server uses TempDB for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQL Server.

Access to log files is sequential except when a transaction needs to be rolled back while access to data files, including TempDB, is randomly accessed. So as a general rule, you should have log files on a separate physical disk than data files for better performance. The focus of this paper is not how to configure your I/O devices but to describe ways to identify if you have I/O bottleneck. Once an I/O bottleneck is identified, you may need to reconfigure your I/O subsystem.
If you have a slow I/O subsystem, your users may experience performance problems such as slow response times, and tasks that abort due to timeouts.
You can use the following performance counters to identify I/O bottlenecks. Note, these AVG values tend to be skewed (to the low side) if you have an infrequent collection interval. For example, it is hard to tell the nature of an I/O spike with 60-second snapshots. Also, you should not rely on one counter to determine a bottleneck; look for multiple counters to cross check the validity of your findings.

  • Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.
  • Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number:
    • Less than 10 ms - very good
    • Between 10 - 20 ms - okay
    • Between 20 - 50 ms - slow, needs attention
    • Greater than 50 ms - Serious I/O bottleneck
  • Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.
  • Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
  • Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
  • Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

When using above counters, you may need to adjust the values for RAID configurations using the following formulas.

  • Raid 0 -- I/Os per disk = (reads + writes) / number of disks
  • Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
  • Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
  • Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks

For example, you have a RAID-1 system with two physical disks with the following values of the counters.

  • Disk Reads/sec -  80
  • Disk Writes/sec - 70
  • Avg. Disk Queue Length - 5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck."

另外可以利用下面的脚本定期IO信息做比对分析:

SET NOCOUNTON

DECLARE @IOStatsTABLE (
[database_id][smallint]NOT NULL,
[file_id][smallint]NOT NULL,
[num_of_reads][bigint]NOT NULL,
[num_of_bytes_read][bigint]NOT NULL,
[io_stall_read_ms][bigint]NOT NULL,
[num_of_writes][bigint]NOT NULL,
[num_of_bytes_written][bigint]NOT NULL,
[io_stall_write_ms][bigint]NOT NULL)
INSERTINTO @IOStats
SELECT database_id,

                vio
.file_id,
                num_of_reads
,
                num_of_bytes_read
,
                io_stall_read_ms
,
                num_of_writes
,
                num_of_bytes_written
,
                io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vio
DECLARE @StartTime datetime, @DurationInSecsint

SET @StartTime= GETDATE()
WAITFOR DELAY'00:05:00'
SET @DurationInSecs= DATEDIFF(ss, @startTime, GETDATE())
SELECT DB_NAME(vio.database_id)AS [Database],
        mf
.nameAS [Logical name],
        mf
.type_descAS [Type],
(vio.io_stall_read_ms- old.io_stall_read_ms)/ CASE(vio.num_of_reads-old.num_of_reads)WHEN 0THEN 1ELSE vio.num_of_reads-old.num_of_readsEND AS[Ave read speed(ms)],
        vio
.num_of_reads- old.num_of_readsAS [Noof reads over period],
CONVERT(DEC(14,2),(vio.num_of_reads- old.num_of_reads)/ (@DurationInSecs *1.00))AS [Noof reads/sec],
CONVERT(DEC(14,2),(vio.num_of_bytes_read- old.num_of_bytes_read)/ 1048576.0)AS [Tot MBread over period],
CONVERT(DEC(14,2),((vio.num_of_bytes_read- old.num_of_bytes_read)/ 1048576.0)/ @DurationInSecs)AS [Tot MBread/sec],
(vio.num_of_bytes_read- old.num_of_bytes_read)/ CASE(vio.num_of_reads-old.num_of_reads)WHEN 0THEN 1ELSE vio.num_of_reads-old.num_of_readsEND AS[Ave read size(bytes)],
(vio.io_stall_write_ms- old.io_stall_write_ms)/ CASE(vio.num_of_writes-old.num_of_writes)WHEN 0THEN 1ELSE vio.num_of_writes-old.num_of_writesEND AS[Ave write speed (ms)],
        vio
.num_of_writes- old.num_of_writesAS [Noof writes over period],
CONVERT(DEC(14,2),(vio.num_of_writes- old.num_of_writes)/ (@DurationInSecs *1.00))AS [Noof writes/sec],
CONVERT(DEC(14,2),(vio.num_of_bytes_written- old.num_of_bytes_written)/1048576.0)AS [Tot MB writtenover period],
CONVERT(DEC(14,2),((vio.num_of_bytes_written- old.num_of_bytes_written)/1048576.0)/ @DurationInSecs)AS [Tot MB written/sec],
(vio.num_of_bytes_written-old.num_of_bytes_written)/ CASE(vio.num_of_writes-old.num_of_writes)WHEN 0THEN 1ELSE vio.num_of_writes-old.num_of_writesEND AS[Ave write size (bytes)],
        mf
.physical_nameAS [Physicalfile name],
        size_on_disk_bytes
/1048576AS [File sizeon disk(MB)]
FROM sys.dm_io_virtual_file_stats(NULL,NULL) vio,
        sys
.master_files mf,
        @IOStats old
WHERE mf.database_id= vio.database_idAND
        mf
.file_id= vio.file_idAND
        old
.database_id= vio.database_idAND
        old
.file_id= vio.file_idAND
((vio.num_of_bytes_read- old.num_of_bytes_read)+ (vio.num_of_bytes_written- old.num_of_bytes_written))> 0
ORDERBY ((vio.num_of_bytes_read- old.num_of_bytes_read)+ (vio.num_of_bytes_written- old.num_of_bytes_written))DESC
GO

这篇关于使用sys.dm_io_virtual_file_stats了解你的数据库IO的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/602055

相关文章

Python通用唯一标识符模块uuid使用案例详解

《Python通用唯一标识符模块uuid使用案例详解》Pythonuuid模块用于生成128位全局唯一标识符,支持UUID1-5版本,适用于分布式系统、数据库主键等场景,需注意隐私、碰撞概率及存储优... 目录简介核心功能1. UUID版本2. UUID属性3. 命名空间使用场景1. 生成唯一标识符2. 数

SpringBoot中如何使用Assert进行断言校验

《SpringBoot中如何使用Assert进行断言校验》Java提供了内置的assert机制,而Spring框架也提供了更强大的Assert工具类来帮助开发者进行参数校验和状态检查,下... 目录前言一、Java 原生assert简介1.1 使用方式1.2 示例代码1.3 优缺点分析二、Spring Fr

Android kotlin中 Channel 和 Flow 的区别和选择使用场景分析

《Androidkotlin中Channel和Flow的区别和选择使用场景分析》Kotlin协程中,Flow是冷数据流,按需触发,适合响应式数据处理;Channel是热数据流,持续发送,支持... 目录一、基本概念界定FlowChannel二、核心特性对比数据生产触发条件生产与消费的关系背压处理机制生命周期

java使用protobuf-maven-plugin的插件编译proto文件详解

《java使用protobuf-maven-plugin的插件编译proto文件详解》:本文主要介绍java使用protobuf-maven-plugin的插件编译proto文件,具有很好的参考价... 目录protobuf文件作为数据传输和存储的协议主要介绍在Java使用maven编译proto文件的插件

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys

SpringBoot线程池配置使用示例详解

《SpringBoot线程池配置使用示例详解》SpringBoot集成@Async注解,支持线程池参数配置(核心数、队列容量、拒绝策略等)及生命周期管理,结合监控与任务装饰器,提升异步处理效率与系统... 目录一、核心特性二、添加依赖三、参数详解四、配置线程池五、应用实践代码说明拒绝策略(Rejected

C++ Log4cpp跨平台日志库的使用小结

《C++Log4cpp跨平台日志库的使用小结》Log4cpp是c++类库,本文详细介绍了C++日志库log4cpp的使用方法,及设置日志输出格式和优先级,具有一定的参考价值,感兴趣的可以了解一下... 目录一、介绍1. log4cpp的日志方式2.设置日志输出的格式3. 设置日志的输出优先级二、Window

Ubuntu如何分配​​未使用的空间

《Ubuntu如何分配​​未使用的空间》Ubuntu磁盘空间不足,实际未分配空间8.2G因LVM卷组名称格式差异(双破折号误写)导致无法扩展,确认正确卷组名后,使用lvextend和resize2fs... 目录1:原因2:操作3:报错5:解决问题:确认卷组名称​6:再次操作7:验证扩展是否成功8:问题已解

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构

使用Docker构建Python Flask程序的详细教程

《使用Docker构建PythonFlask程序的详细教程》在当今的软件开发领域,容器化技术正变得越来越流行,而Docker无疑是其中的佼佼者,本文我们就来聊聊如何使用Docker构建一个简单的Py... 目录引言一、准备工作二、创建 Flask 应用程序三、创建 dockerfile四、构建 Docker