Performance Monitor4:监控SQL Server的IO性能

时间:2022-06-19 02:44:39

SQL Server数据库系统的IO性能受到物理硬盘的IO延迟和SQL Server请求执行的IO操作的影响。在监控硬盘性能时,最主要的度量值(metric)是IO延迟,IO延迟是指从应用程序创建IO请求,到硬盘完成IO请求的时间延迟。如果物理硬盘不能及时完成IO请求,跟不上请求负载的速度,那么SQL Server就容易出现性能问题。SQL Server内部在执行一些特定的操作时,会和硬盘做读写交互,这也会影响物理硬盘响应SQL Server的IO请求的性能,使查询进程处于PageIOLatch或WriteLog等待。

一,使用性能计数器(Performance counter)监控硬盘 IO

首先要了解操作系统的存储管理,硬盘在操作系统分为:物理硬盘(Physical Disk)和逻辑硬盘(Logical Disk)。Windows可以在一个Physical Disk上划出若干个逻辑分区,每一个逻辑分区是一个Logical Disk。对于分配在同一个Physical Disk上的Logical Disks,其读写操作共享Physical Disk的IO带宽。Windows给每一个Logical Disk分配一个盘符,App通过盘符来读写数据。

对应地,硬盘的性能有两组计数器:Logical Disk 和 Physical Disk,其中逻辑硬盘是物理硬盘的逻辑分区:

  • Logical Disk 计数器按照逻辑分区记录每个逻辑分区的读写IO信息,由于App通过盘符来读写数据,通过logical Disk 计数器可以了解不同App向不同盘符发出的读写请求有多少。
  • Physical Disk 计数器是按照物理磁盘,记录每一个物理磁盘的读写IO,如果该物理硬盘上有多个逻辑分区,那么把所有逻辑分区的IO加和到一起,统计物理硬盘的IO性能,监控硬盘的响应速度,能够真正了解硬件的实际IO量。推荐监控某一块Physical Disk,而不要粗暴地监控所有的物理硬盘,多数情况下,一块Physical Disk的IO很忙,而其他Physical Disk很空闲,原因是数据库文件分布的逻辑硬盘是同一块物理硬盘,导致单块物理硬盘需要处理的IO请求过多,为了避免这种情况的发生,需要把数据库文件均匀分布在不同的物理硬盘上。

那么,Windows性能监控器都有哪些硬盘的性能计数器了?

1,Disk Queue Length

硬盘队列长度是等待被Physical Disk处理的IO请求的数目。如果一个App发出一条读请求,但是目标Disk正在处理其他IO Task,那么这个新的请求就会被放在Disk queue中,Disk queue Length就是1,硬盘的请求队列的长度,能够衡量硬盘的工作负载,队列长度越长,说明硬盘接收到的IO请求越多,完成一次IO请求需要的处理时间就越长,从一定程度上表明,硬盘性能不能满足业务的需求了。

  • Avg. Disk Queue Length
  • Avg.Disk Read Queue Length
  • Avg.Disk Wirte Queue Length
  • Current Disk Queue length

2,传输(Transfer)时间

Transfer是Disk 的一次完整的I/O动作,表示从寻道,读写数据,到传输完成。在统计时,Transfer 是 Read 和 Write的加和。

  • Avg. Disk sec/Transfer : 磁盘每一次读写所用的平均时间。
  • Disk Transfers/sec : 磁盘每秒处理的读写次数。
  • Avg.Disk Bytes/Transfer:Disk 每次IO传送的Bytes数

3,读写时间百分比

硬盘的工作时间(elapsed time)是指硬盘用于执行read/write操作的时间

  • % Disk Time:硬盘的工作时间( elapsed time)和处理IO请求的总时间的比值。
  • % Disk Read Time
  • % Disk Write Time
  • % Idle Time

4,IO拆分

一次IO拆分成多次IO来实现,IO拆分的原因是文件出现碎片,一次IO请求读取的非连续的数据段,那么硬盘子系统会把该请求分成多次执行,测量IO拆分的比例能够反映文件存储的分散程度。

还有一个原因会导致IO拆分,这就是一次读取的数据过大,导致无法通过一次IO请求返回,这就需要把IO请求拆分成多次。

Split IO/Sec reports the rate at which I/Os to the disk were split into multiple I/Os. A split I/O may result from requesting data of a size that is too large to fit into a single I/O or that the disk is fragmented.

二,在系统级别监控物理Disk的IO性能

使用性能监控器来侦测IO性能,用于监控IO性能的计数器主要是物理硬盘的读写:

  • Physical Disk\Disk Reads/sec
  • Physical Disk\Disk Writes/sec

这两个计数器的性能指标:

  • <10 ms 没有性能问题
  • 10~20ms 存在问题
  • 20~50ms 性能较低
  • >50ms 存在严重的性能问题

1,监控物理Disk的IO延迟

在Windows级别上对Physical Disk的IO延迟进行分析,主要依赖于Performance Monitor的计数器,衡量物理Disk的IO延迟的计数器主要有三个:

  • Avg. Disk sec/Transfer:Disk每一次读写操作所用的平均时间
  • Avg. Disk sec/Read:Disk每一次读操作所用的平均时间
  • Avg. Disk sec/Write:Disk每一次写操作所用的平均时间

avg.Disk sec/(Transfer,Read,Write),能够很好的反映Disk的IO速度,推荐的衡量Disk的IO速度的基线(baseline):

  • 很快:<10ms
  • 一般:10-20ms
  • 有点慢:20-50ms
  • 非常慢:>50ms

2,分析Data Collector收集的计数器数值

下图是产品环境中一台Server的计数器数值图表,将IO延迟的度量值按比例放大1000倍,这样图表显示的单位就是ms。

  • %Idle Time:在60%左右浮动,说明Disk不是很忙碌
  • Avg.Disk sec/Write:大多数情况下都是10ms以下,偶尔波动,说明Disk的写延迟比较低
  • Avg.Disk sec/Read:读延迟大多数情况下都是在40ms以上,鲜有低于40ms,偶尔达到峰值,说明Disk的读延迟非常高
  • Avg.Disk sec/Transfer:读写延迟的均值在30ms左右,时有波动,在%Idle Time曲线不波动时,Disk的读写延迟也有波动,说明Disk的读写延迟不稳定

初步判断,Disk的读写延迟非常高,Disk的IO性能较差,IO速度慢

Performance Monitor4:监控SQL Server的IO性能

3,监控物理Disk的IO次数

根据Disk的IO次数来界定Disk性能,没有统一的阈值,一般通过监控计数值来获取一个趋势,设置一个基线,如果在Disk比较忙碌时,遇到异常的谷值,那么就需要查看是否出现参数嗅探问题和Disk IO密集的查询,异常的谷值一般是由查询语句请求的数据量太多造成的,需要对查询语句进行性能调优。

系统级经常用到的Disk性能计数器是PhysicalDisk计数器:

  • Avg. Disk Queue Length :提供Disk阻塞程度的主要度量值,表示在 sample interval期间,Disk等待处理的IO请求队列的平均长度,即等待被Disk处理的IO请求的数量
  • % Idle Time:Disk的空闲程度,可以反推出Disk的忙碌程度
  • Disk (Reads/Writes/Transfers)/sec:每秒Disk执行读写操作数量

队列长度波动很大,在%Idle Time 升高时,IO数量降低,没有发现明显的异常谷值。

Performance Monitor4:监控SQL Server的IO性能

4,监控物理Disk读写的数据量

这几个计数值,对监控物理Disk的读写性能,意义不大,仅仅作为参考。

  • Avg.Disk Bytes/(Read,Write,Transfer)表示:在物理Disk执行读写操作时,物理Disk从Disk读取到内存的字节数量,从内存写入到Disk的字节数量,以及两者的总字节数量
  • Disk Bytes/sec:在物理Disk执行读写操作时,数据从Disk传输到内存,或从内存写入到Disk的字节速度,好的Disk,其值在20-40MB之间,一般Disk,其值在20MB以下

Performance Monitor4:监控SQL Server的IO性能

三,SQL Server内部操作对Disk IO性能的影响

SQL Server能够缓存从Disk加载的数据页,正常情况下,大部分操作不需要任何物理读操作,不需要Disk的物理IO参与就能完成,但是,有一些操作,必须和物理Disk进行IO操作,才能完成。SQL Server和物理Disk进行IO交互的操作:

  • 对于内存中没有缓存的数据,第一次访问时,需要将数据从数据文件读取到内存中,SQL Server访问的任何数据必须缓存到内存中,如果不在内存中,SQL Server发送读请求,将数据页从物理Disk读取到内存中,这个过程叫做物理读;如果数据存在于内存中,SQL Server直接访问,这个过程叫做逻辑读。
  • 在任何修改操作提交之前,预写事务日志记录到日志文件,在CheckPoint和LazyWriter运行时,数据被写入数据文件。
  • 执行CheckPoint时,将缓存中的脏页写入数据文件,脏页是指载入内存之后被修改过的数据页,内存中的数据和数据文件中的数据不一致,由CheckPoint触发的物理写操作和内压没有关系,和用户修改的数据量有关,用于控制还原的时间间隔。
  • 当Buffer Pool空间不足,Free Buffer List减少到临界值时,LazyWriter进程主动将内存中的一些很久没有访问过数据页面和执行计划清空,如果数据页面是脏页,那么将其写入到数据文件,LazyWriter和内存压力有关,由于内存可用的Free Buffer不足导致LazyWriter进程执行清理操作。
  • IO密集型操作,比如检查数据库的一致性(DBCC CheckDB),重建索引,更新统计信息,数据库备份等,会带来大量的Disk IO操作

SQL Server只会读取数据文件,只要数据缓存在内存中,理想情况下,SQL Server不会执行任何物理读操作,也不需要从物理Disk加载数据到内存,SQL Server执行读取操作性能和内存的缓存能力有直接关系,也和用户读取的数据量有关。

SQL Server的写操作分为写数据文件和写日志文件。写入日志文件的数据量,完全由数据修改量决定,和内存压力没有关系;写入数据文件的数量,主要和修改量有关。LazyWriter和内存压力有关系,一旦内存有压力,LazyWriter自动启动,负责清理最久未被访问的缓存,释放内存,增加可用的Free buffer数量。

因此,SQL Server请求的物理Disk的读操作数量和内存有直接关系,内存越充足,缓存的数据量越多,物理Disk的读操作的数量就会越少,逻辑读的数量不会减少;SQL Server请求的物理Disk的写操作数量和用户执行的数据修改量有直接关系,和内存是否存在压力关系很微小。在执行物理disk的读写请求时,SQL Server的查询进程产生PageIOLatch等待,表示进程正在执行物理读操作,该等待可以从DMV:sys.dm_exec_requests 查看到:

select  r.session_id,
r.blocking_session_id as blocking,
r.wait_type as Current_Wait_Type,
r.wait_resource,
r.last_wait_type,
r.wait_time,
r.status,
r.command,
r.cpu_time,r.reads,r.writes,r.logical_reads,
r.total_elapsed_time,
r.start_time,
db_name(r.database_id) as database_name,
SUBSTRING( st.text,
r.statement_start_offset/2+1,
( CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text))
ELSE (r.statement_end_offset - r.statement_start_offset)/2
END
)
) as IndividualQueryStatement
from sys.dm_exec_requests r
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where (r.wait_type<>'MISCELLANEOUS' or r.wait_type is null)
and r.session_id>50
and r.session_id<>@@spid

Performance Monitor4:监控SQL Server的IO性能

PageIOLatch 等待:表示进程正在从物理Disk加载数据到内存,即进程在进行物理读操作,从Reads字段能够看到物理读的数量

WriteLog 等待:表示事务正在修改数据,SQL Server将预先将事务日志记录写入到事务日志文件

参考文档:

Memory - Lazy Writer and Checkpoint

SQL Server disk performance metrics – Part 1 – the most important disk performance metrics

Measuring Disk Latency with Windows Performance Monitor (Perfmon)

Performance Monitor4:监控SQL Server的IO性能的更多相关文章

  1. Resolving SQL Server Disk IO bottlenecks

    网上看到这篇文章挺不错的,直接翻译过来.在尝试诊断SQL Server性能时,不要仅仅依赖某个单一的诊断数据,比如CPU的使用率.SQL Server磁盘性能,就得出结论却忽略的问题的根源.实际上,使 ...

  2. Performance Monitor3:监控SQL Server的内存压力

    SQL Server 使用的资源受到操作系统的调度,同时,SQL Server在内部实现了一套调度算法,用于管理从操作系统获取的资源,主要是对内存和CPU资源的调度.一个好的数据库系统,必定在内存中缓 ...

  3. SQL Server添加MDW性能监控报表&lpar;转载&rpar;

    10.2 Data Collector与MDW Data Collection功能是SQL SERVER 2005版本提供的数据库监控报表的功能,通过定时地对数据库的语句运行情况,服务器各种资源的监控 ...

  4. windows系统与SQL SERVER 2008数据库服务性能监控分析简要

    软件系统性能测试体系流程介绍之windows系统与SQL SERVER 2008数据库服务性能监控分析简要 目前大部分测试人员对操作系统资源.中间件.数据库等性能监控分析都是各自分析各自的监控指标方式 ...

  5. 监控 SQL Server &lpar;2005&sol;2008&rpar; 的运行状况

    Microsoft SQL Server 2005 提供了一些工具来监控数据库.方法之一是动态管理视图.动态管理视图 (DMV) 和动态管理函数 (DMF) 返回的服务器状态信息可用于监控服务器实例的 ...

  6. 用脚本定时监控SQL Server主从一致性

    用脚本定时监控SQL Server主从一致性 首先说一下我们的环境 我们使用的是事务复制,复制是单向的,主服务器和从服务器都在同一个机房,当然不同机房也可以,只需要改一下IP和端口 下面的脚本在我们的 ...

  7. 第三篇——第二部分——第六文 监控SQL Server镜像

    原文:第三篇--第二部分--第六文 监控SQL Server镜像 原文出处:http://blog.csdn.net/dba_huangzj/article/details/26846203 要优化, ...

  8. 采用Opserver来监控你的ASP&period;NET项目系列&lpar;二、监控SQL Server与Asp&period;Net项目&rpar;

    前言 之前有过2篇关于如何监控ASP.NET core项目的文章,有兴趣的也可以看看. 今天我们主要来介绍一下,如何使用Opserver监控我们的SQL Server 和ASP.NET项目的异常监控 ...

  9. 监控SQL Server正在执行的SQL语句和死锁情况

    原文:监控SQL Server正在执行的SQL语句和死锁情况 SELECT [Individual Query] = SUBSTRING(qt.TEXT, er.statement_start_off ...

随机推荐

  1. C&num;托管代码与C&plus;&plus;非托管代码互相调用

    http://www.cnblogs.com/Jianchidaodi/archive/2009/03/11/1407270.html#1473515 http://www.cnblogs.com/J ...

  2. 编译android版libmpg

    环境:ubutnu 12.04,android SDK 1. 下载libmpg的一个android工程,得到一个Android-libmpg-master.zip.https://github.com ...

  3. css3 transiton

    div { width:100px; height:100px; background:yellow; transition-property:width; transition-duration:1 ...

  4. BZOJ 2789&colon; &lbrack;Poi2012&rsqb;Letters&lpar; BIT &rpar;

    直接求逆序对就行了...时间复杂度O(nlogn) ------------------------------------------------------------------------- ...

  5. JXL 读取 Excel java中jxl导出数据到excel的例子 上传文件

    2010-10-14 19:17:06 com.opensymphony.xwork2.util.logging.commons.CommonsLogger info 信息: Entferne Dat ...

  6. 设备指纹识别之User Agent 解析

    设备指纹识别之User Agent 解析User Agent 解析 zoerywzhou@163.com http://www.cnblogs.com/swje/ 作者:Zhouwan 2017-4- ...

  7. NodeJS 连接接MySQL

    NodeJS 连接接MySQL MySQL是常用数据库,作为后端模块,nodejs可以提供了mysql接口 安装 $ npm install mysql 测试代码 var mysql = requir ...

  8. 深度学习网络压缩模型方法总结&lpar;model compression&rpar;

    两派 1. 新的卷机计算方法 这种是直接提出新的卷机计算方式,从而减少参数,达到压缩模型的效果,例如SqueezedNet,mobileNet SqueezeNet: AlexNet-level ac ...

  9. scala函数等号省略

    例1: 此时没有写返回值,但是有等号,函数会自己推断返回值,此时的返回值是Int def add(x:Int, y:Int) = { x+y} 例2: 此时没有写返回值,也没有等号,无论函数内部有没有 ...

  10. JavaScript(四):函数

    JavaScript中的函数分为两种:系统函数和自定义函数,这里主要讲解自定义函数. 一.自定义函数 1.语法: 注意: 传入的参数是可选的. 例如: <!DOCTYPE html> &l ...