使用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

相关文章

postgresql使用UUID函数的方法

《postgresql使用UUID函数的方法》本文给大家介绍postgresql使用UUID函数的方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录PostgreSQL有两种生成uuid的方法。可以先通过sql查看是否已安装扩展函数,和可以安装的扩展函数

如何使用Lombok进行spring 注入

《如何使用Lombok进行spring注入》本文介绍如何用Lombok简化Spring注入,推荐优先使用setter注入,通过注解自动生成getter/setter及构造器,减少冗余代码,提升开发效... Lombok为了开发环境简化代码,好处不用多说。spring 注入方式为2种,构造器注入和setter

MySQL中比较运算符的具体使用

《MySQL中比较运算符的具体使用》本文介绍了SQL中常用的符号类型和非符号类型运算符,符号类型运算符包括等于(=)、安全等于(=)、不等于(/!=)、大小比较(,=,,=)等,感兴趣的可以了解一下... 目录符号类型运算符1. 等于运算符=2. 安全等于运算符<=>3. 不等于运算符<>或!=4. 小于运

虚拟机Centos7安装MySQL数据库实践

《虚拟机Centos7安装MySQL数据库实践》用户分享在虚拟机安装MySQL的全过程及常见问题解决方案,包括处理GPG密钥、修改密码策略、配置远程访问权限及防火墙设置,最终通过关闭防火墙和停止Net... 目录安装mysql数据库下载wget命令下载MySQL安装包安装MySQL安装MySQL服务安装完成

使用zip4j实现Java中的ZIP文件加密压缩的操作方法

《使用zip4j实现Java中的ZIP文件加密压缩的操作方法》本文介绍如何通过Maven集成zip4j1.3.2库创建带密码保护的ZIP文件,涵盖依赖配置、代码示例及加密原理,确保数据安全性,感兴趣的... 目录1. zip4j库介绍和版本1.1 zip4j库概述1.2 zip4j的版本演变1.3 zip4

Python 字典 (Dictionary)使用详解

《Python字典(Dictionary)使用详解》字典是python中最重要,最常用的数据结构之一,它提供了高效的键值对存储和查找能力,:本文主要介绍Python字典(Dictionary)... 目录字典1.基本特性2.创建字典3.访问元素4.修改字典5.删除元素6.字典遍历7.字典的高级特性默认字典

MySQL进行数据库审计的详细步骤和示例代码

《MySQL进行数据库审计的详细步骤和示例代码》数据库审计通过触发器、内置功能及第三方工具记录和监控数据库活动,确保安全、完整与合规,Java代码实现自动化日志记录,整合分析系统提升监控效率,本文给大... 目录一、数据库审计的基本概念二、使用触发器进行数据库审计1. 创建审计表2. 创建触发器三、Java

使用Python构建一个高效的日志处理系统

《使用Python构建一个高效的日志处理系统》这篇文章主要为大家详细讲解了如何使用Python开发一个专业的日志分析工具,能够自动化处理、分析和可视化各类日志文件,大幅提升运维效率,需要的可以了解下... 目录环境准备工具功能概述完整代码实现代码深度解析1. 类设计与初始化2. 日志解析核心逻辑3. 文件处

一文详解如何使用Java获取PDF页面信息

《一文详解如何使用Java获取PDF页面信息》了解PDF页面属性是我们在处理文档、内容提取、打印设置或页面重组等任务时不可或缺的一环,下面我们就来看看如何使用Java语言获取这些信息吧... 目录引言一、安装和引入PDF处理库引入依赖二、获取 PDF 页数三、获取页面尺寸(宽高)四、获取页面旋转角度五、判断

C++中assign函数的使用

《C++中assign函数的使用》在C++标准模板库中,std::list等容器都提供了assign成员函数,它比操作符更灵活,支持多种初始化方式,下面就来介绍一下assign的用法,具有一定的参考价... 目录​1.assign的基本功能​​语法​2. 具体用法示例​​​(1) 填充n个相同值​​(2)