MS SQL Server分析数据库的I/O性能

「爱情、让人受尽委屈。」 2022-06-12 09:11 266阅读 0赞

[原文链接][Link 1]

摘要： 系统有的时候响应会明显很慢，有的时候可以是因为访问人数增加导致，有的时候可能因为数据库的磁盘I/O访问次数频繁导致。MS SQL Server提供了一些动态管理视图和函数供我们分析磁盘I/O性能。 　　1、sys.

系统有的时候响应会明显很慢，有的时候可以是因为访问人数增加导致，有的时候可能因为数据库的磁盘I/O访问次数频繁导致。MS SQL Server提供了一些动态管理视图和函数供我们分析磁盘I/O性能。

1、sys.dm\_io\_virtual\_file\_stats

<table style="border-collapse:collapse; border-spacing:0px; background-color:rgb(221,221,221); max-width:100%; display:block; width:612.727px; overflow:auto; border-style:solid"> 
 <tbody style=""> 
  <tr style=""> 
   <td style="margin:0px; padding:6px 12px; vertical-align:top; border:1px solid rgb(221,221,221)"> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">SELECT &nbsp;DB_NAME(vfs.database_id) AS database_name ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.database_id ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.FILE_ID ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">num_of_bytes_read / NULLIF(num_of_reads, 0) AS avg_bytes_per_read ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">num_of_bytes_written / NULLIF(num_of_writes, 0) AS avg_bytes_per_write ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.io_stall ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.num_of_reads ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.num_of_bytes_read ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.io_stall_read_ms ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.num_of_writes ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.num_of_bytes_written ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">vfs.io_stall_write_ms ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">size_on_disk_bytes / 1024 / 1024. AS [size_on_disk_mbytes(MB)] ,</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">mf.physical_name</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">FROM &nbsp; &nbsp;sys.dm_io_virtual_file_stats(DB_ID('master'), 1) AS vfs</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">AND vfs.FILE_ID = mf.FILE_ID</span> 
    </div> 
    <div style="margin:10px 0px; padding:0px"> 
     <span style="font-size:18px">ORDER BY avg_total_latency DESC</span> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

如果sys.dm\_io\_virtual\_file\_stats(null,null)那么会显示所有默认数据库和曾经附加过的数据库的信息，这里只想看master数据库的I/O信息，对数据库上执行后结果分析如下：

![14982672_20140522170057109lv.jpg][]

2、sys.dm\_io\_cluster\_shared\_drives和sys.dm\_io\_cluster\_valid\_path\_names

如果你的数据库架构采用了集群部署，那么可以通过这两个查到关于集群节点的信息。未来sys.dm\_io\_cluster\_shared\_drives将被废弃，采用sys.dm\_io\_cluster\_valid\_path\_names代替。

最新内容请见作者的GitHub页：http://qaseven.github.io/

如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@service.aliyun.com 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。

![Center][]

[原文链接][Link 1]

[Link 1]: http://click.aliyun.com/m/26368/
[14982672_20140522170057109lv.jpg]: /images/20220612/66c91efd86ef42dab619cc88e4b3f93c.png
[Center]: /images/20220612/10f82b17c4fa44dbb764d16145c909c8.png