性能计数器(Performance Counter)是量化系统状态或活动的一个数值,Windows
Performance
Monitor在一定时间间隔内(默认的取样间隔是15s)获取Performance
Counter的当前值,并记录在Data Collections中,通过Performance
Monitor能够查看系统的性能数据,是故障排除的极佳工具。Performance
Counter数量很多,如果不了解计数器的功能,在选择计数器时,往往不知所措。由于SQL
Server
是IO密集型的应用程序,经常需要进行大量的读写操作,从Disk读取数据到内存,将内存中的数据写入到Disk,因此,Disk和内存是SQL
Server的生命线,监控SQL Server
的性能,经常用到的性能计数器是Disk和内存。

SQL Server 使用的资源受到操作系统的调度,同时,SQL
Server在内部实现了一套调度算法,用于管理从操作系统获取的资源,主要是对内存和CPU资源的调度。一个好的数据库系统,必定在内存中缓存足够多的信息,以减少从物理硬盘中读取数据的次数;如果内存是系统瓶颈,那么SQL
Server一定会运行的非常慢。监控SQL
Server的内存压力,需要从Widnows级别上,对内存使用的整体使用情况进行监控:从SQL
Server级别上,监控SQL Server对内存资源的使用情况。

一,Disk性能监控

一,从Windows级别来监控内存资源的使用

1,Disk的结构

操作系统能够调度的内存,有两个来源:物理内存和虚拟内存。物理内存是内存硬件提供的高速访问设备,虚拟内存是物理内存的扩展,操作系统开辟一块物理Disk空间,作为内存空间使用,用于存储缓存数据的文件,叫作缓存文件(Paging
File),路径名是C:pagefile.sys,默认是隐藏的。操作系统透明地使用Paging
File来存储数据,Application是无法控制和感知数据是存储在物理内存还是在虚拟内存中,即,操作系统决定使用物理内存,或Paging
file来存储缓存数据。一般,通过Performance
Monitor来监控Windows级别的内存资源使用情况。

典型的机械Disk的结构主要有:磁头(head),磁道(track),扇区(sector),盘面(Platter),柱面(cylinder)和簇(cluster)。如图,

1,监控物理内存

菲律宾太阳娱乐集团 1

常用的系统级别的内存计数器跟硬缺页中断有关:

当磁盘旋转时,若磁头保持在一个位置上,则每个磁头都会在磁盘表面划出一个圆形轨迹,这些圆形轨迹叫做磁道。磁盘上的每个磁道被等分为若干个弧段,这些弧段是磁盘的扇区,每个磁道上的扇区数量是相等的,每个扇区存放512个字节的信息,磁盘驱动器在向磁盘读取和写入数据时,以扇区为单位。若干个连续的扇区组合为一个簇,文件存取是以簇为单位的。

  • Memory:Page
    Faults/sec :每秒发生的Page Fault的数量,Page Fault包括Hard
    Fault 和 Soft Fault,Hard fault表示需要从Disk中读取数据页,Soft
    fault表示需要从Physical Memory中读取数据页,Soft
    Fault不会影响性能,由于Hard
    Fault需要访问Disk,会产生显著的延迟。
  • Memory:Pages
    Input/sec:每秒发生的Hard Fault的数量,用于计算Hard
    Fault的百分比: Pages Input / Page Faults = % Hard Page
    Faults,如果百分比经常大于40%,说明系统需要经常访问Disk获取数据,在一定程度上说明系统存在内存压力。
  • Memory:Pages/sec:每秒从Disk读取或写入Disk的Page数量,表示内存和Disk交互的Page的数量:将Page存储到Disk或从Disk读取数据到内存的Page的数量。

硬盘通常由重叠的一组盘片构成,每个盘面都被划分为数目相等的磁道,并从外缘的”0″向中心开始编号,具有相同编号的磁道形成一个圆柱,称之为磁盘的柱面。磁盘的柱面数与一个盘面上的磁道数是相等的。由于每个盘面都有自己的磁头,因此,盘面数等于总的磁头数。所谓硬盘的CHS,是指Cylinder(柱面)、Head(磁头)、Sector(扇区),硬盘的容量=柱面数×磁头数×扇区数×512B。

如下图,Page Faults/sec的数量,均值在6000/s左右,Pages
Input/sec波动明显,时高时低,持续的时间很短,均值在50/s左右,两者的比例关系均值低于1%,低于40%,可以认为内存压力较小。Pages/sec
和 Pages
Input/sec几乎完全重合,说明,操作系统当时在进行大量的物理读操作。

菲律宾太阳娱乐集团 2

菲律宾太阳娱乐集团 3

扇区是能独立寻址的最小单位,簇是资源分配的最小单位。Disk的一次读写操作,由寻道,旋转延迟和数据传输组成,由于寻道和旋转延迟占用了读写操作的大部分时间,Disk在执行每次读写操作时,采取就近原则,读写连续的N个扇区,读写的数据量是4KB的整数倍。

2,监控虚拟内存

2,顺序读写和随机读写

操作系统会同时消耗物理内存和虚拟内存,虚拟内存计数器主要有两个:

随机读写是指数据分布在不同的磁道上,Disk的磁头必须移动磁道,才能读取到相应的数据;顺序读写是指数据分布在同一磁道的相邻扇区中,在读写数据时,Disk的磁头不需要移动磁道。由于,Disk的磁道移动是机械运动,“很慢”,占读写数据所用时间的绝大部分,因此,Disk的顺序读写速度远远高于随机读写速速,应尽量避免随机读写。Disk,固态硬盘盒内存的读写速度对比:

  • Paging
    File:% Usage 用于监控Paging file实例的使用比例
  • Process: Paging File Bytes
     用于监控虚拟内存的大小

菲律宾太阳娱乐集团 4

存储在虚拟内存中的数据越多,说明物理内存数量和实际需求量的差距越大,比值
% Usage 仅仅作为参考值,如果长时间接近100%,那么系统很可能出现异常。

3,硬盘性能计数器

二,从SQL Server级别上,监控SQL Server对内存资源的使用情况

在OS
Level上,Windows在一块物理硬盘上分成多个逻辑分区,每一个逻辑分区叫做一个Logical
Disk,通过盘符标识,运行在Windows上的Application使用盘符来寻址。对于分配在同一块物理硬盘上的逻辑分区,共享物理硬盘的读写带宽,相当于在一块物理硬盘上工作。因此,Disk计数器分为两组:PhysicalDisk

LogicalDisk,LogcialDisk计数器记录每个逻辑分区的读写计数,用于分析特定的Application在不同的逻辑分区上的Disk
IO活动和性能参数;PhysicalDisk计数器记录整个物理Disk的性能指标,用于了解Disk的响应速度,主要使用PhysicalDisk计数器,分析Disk的性能问题。

1,从Buffer Pool计数器监控服务器内存总体使用情况

系统级经常用到的Disk性能计数器是PhysicalDisk计数器,LogcialDisk仅供参考:

由于Buffer Pool是SQL
Server内存最活跃,使用最多的部分,所以也是最容易出现性能瓶颈的部分,计数值尤其重要:

  • %Disk Time
    :表示Disk的忙碌程度,是Disk处理读写请求的时间的百分比,经常大于100%,建议使用%IdleTime反推出Disk处于读写状态的百分比
  • Disk Reads/sec
    :每秒向Disk请求读操作的次数
  • Disk
    Transfers/sec:Disk每秒执行读写操作的次数
  • Disk Reads Bytes/sec
    :在Disk执行读操作时,每秒从Disk读取的字节数量
  • Disk
    Bytes/sec:当Disk执行读写操作时,每秒从Disk读取到内存的,或从内存写入到Disk的字节数量,好的Disk,其值在20-40MB之间,差的Disk,其值在20MB以下。
  • Avg. Disk Queue
    Length :提供Disk阻塞程度的主要度量值,表示在 sample
    interval期间,Disk等待处理的IO请求队列的平均长度,即等待被Disk处理的IO请求的数量,队列的长度要考虑到RAID,如果存储后台使用100块物理disk,那么该计数值达到100,这个值是正常的,理论上,每块物理disk的队列长度不应该长时间大于2.
  • Avg.
    Disk sec/Transfer:Disk每一次读写操作所用的平均时间
  • Avg.
    Disk sec/Read:Disk每一次读操作所用的平均时间 
  • Avg.
    Disk sec/Write:Disk每一次写操作所用的平均时间
  • Lazy
    Writes/sec
    :被LazyWriter刷新的buffer数量,如果是脏页,那么将buffer写入到Disk,并将buffer空间标记为Free,如果不是脏页,那么该buffer空间也被标记为Free,LazyWriter的作用是维护一定数量的Free
    buffer,SQL Server使用Free buffer来加载新的数据页。
  • Page
    Life Expectancy
    :PLE,数据页驻留在内存中的时间。如果SQL
    Server没有新的内存需求,或有空闲的内存来完成新的内存需求,那么Lazy
    Writer不会被处罚,Page会一直驻留在Buffer Pool中,那么Page Life
    Expectancy会维持在一个比较高的水平;如果Page
    Life总是高高低低,表明SQL Server存在内存压力。PLE的参考数值是:Max
    Server
    Memory/4GB*300s,如果PLE值长期低于参考值,内存可能存在瓶颈。
  • Page
    Reads/sec
    :每秒从Disk读取的数据页数,即物理读的次数,如果用户访问的数据都缓存在内存中,那么SQL
    Server不需要从物理Disk上读取页面。由于物理IO的开销大,Page
    Reads操作一定会影响SQL Server的性能。
  • Free
    list stalls/sec
    :等待一个Free Page的请求数量,SQL
    Server申请从Disk加载一个Page到内存中,必须在内存中分配一个Buffer,Buffer
    Manager负责维护Free Buffer List,如果Free List没有任何Free
    Buffer,那么请求必须等待,直到有空闲的Buffer使用,才能将Disk中的Page加载到内存中。

avg.Disk
sec/(Transfer,Read,Write),能够很好的反映Disk的IO速度,所以这三个计数值经常用来衡量Disk的IO速度:

根据图表数据分析,SQL
Server执行大量的物理读操作,导致PLE大幅降低;从Free List Stall和 Lazy
Write的测量值推断,SQL Server内存压力较小:

  • 很好:<10ms
  • 一般:10-20ms
  • 有点慢:20-50ms
  • 非常慢:>50ms
  • PLE:大幅度降低,从50Ks降低到均值2Ks左右,说明内存数据页被大量替换;
  • Free List Stalls/sec:
    波动明显,总体数值很小,说明系统中的Free Buffer能够满足SQL
    Server的需求;
  • Lazy
    Write/sec:均值在4/sec,比较小;
  • Page
    Reads/sec:均值在4000/sec,说明SQL
    Server在进行大量的物理读操作

二,系统物理内存性能计数器

 菲律宾太阳娱乐集团 5

SQL
Server在运行的过程中,会持续地向内存中加载大量数据,如果数据长期驻留在内存中,那么SQL
Server 不需要申请Disk
IO请求,就能直接访问数据,快速响应用户的请求。如果SQL
Server访问的数据不在内存中,将会产生一个Hard Page Fault,那么SQL
Server首先指示存储引擎将数据页从Disk加载到内存中,产生PageIOLatch等待,等到数据被加载到内存之后,SQL
Server在内存中访问数据,处理用户请求,由于Disk
的IO速度较慢,延迟高,大量的Hard Page Fault将严重影响SQL
Server响应用户请求的速度,因此,常用的系统级内存计数器跟缺页中断有关:

BCHR(Buffer cache hit ratio)表示:SQL Server
直接从内存中读取数据的百分比,跟预读有很大的关系。一次命中意味着在SQL
Server读取数据时,数据存在于内存中,跟数据驻留在内存中的时间长短,以及内存是否有压力关系不大,仅供参考。

  • Memory:Page
    Faults/sec :每秒发生的Page Fault的数量,Page Fault包括Hard
    Fault 和 Soft Fault,Hard fault表示需要从Disk中读取数据页,Soft
    fault表示需要从Physical Memory中读取数据页,Soft
    Fault不会影响性能,由于Hard
    Fault需要访问Disk,会产生显著的延迟。
  • Memory:Pages
    Input/sec:每秒发生的Hard Fault的数量,用于计算Hard
    Fault的百分比: Pages Input / Page Faults = % Hard Page
    Faults,如果百分比经常大于40%,说明系统需要经常访问Disk获取数据,在一定程度上说明系统存在内存压力。
  • Memory:Pages/sec:每秒从Disk读取或写入Disk的Page数量,表示内存和Disk交互的Page的数量:将Page存储到Disk或从Disk读取数据到内存的Page的数量。

逻辑读是指直接从内存中读取数据,物理读是指从物理Disk文件中加载数据到内存,从SQL
Server角度来看,BCHR=逻辑读/(逻辑读+物理读)。

三,SQL Server的Buffer Manager计数器

如果数据缓存在内存中,那么SQL
Server从内存中直接读取数据,而不需要从物理Disk加载到内存。物理Disk能够执行预读操作,操作系统将物理Disk上的数据预先加载到内存中,在SQL
Server进程访问数据时,该数据已经存在于内存中了。虽然SQL
Server申请了物理读操作,但是,BCHR的测量值没有体现物理读操作,这是因为,在SQL
Server读取数据时,数据是存在于内存中的,SQL Server执行的是逻辑读操作。

Buffer Manager计数器用于监视SQL
Server如何使用内存数据页和计划缓存,读取和写入数据页时的Disk
IO。由于Buffer Pool是SQL
Server内存最活跃,使用最多的部分,所以也是最容易出现性能瓶颈的部分,计数值尤其重要:

推荐阅读《Great SQL Server Debates: Buffer Cache Hit
Ratio》:

  • Buffer Cache hit ration:从Buffer
    Pool中直接读取,不需要从Disk中读取的数据页的百分比,也叫命中率,这个计数器表示,在SQL
    Server读取数据时,数据存在于内存中,跟数据驻留在内存中的时间和内存压力关系不大,仅供参考。
  • Page
    Writes/sec:每秒写入到Disk的数据页数,和内存使用关系不大,跟用户修改的数据量有关
  • CheckPoint
    Pages/sec:将数据刷新到Disk的Dirty
    Pages的数量,和内存使用关系不大,跟用户修改的数据量有关,如果用户对数据库做了很多修改操作,那么内存中修改过的数据脏页就会比较多,每次刷新的脏页数量就会比较大
  • Lazy
    Writes/sec
    :被LazyWriter刷新的buffer数量,如果是脏页,那么将buffer写入到Disk,并将buffer空间标记为Free,如果不是脏页,那么该buffer空间也被标记为Free,LazyWriter的作用是维护一定数量的Free
    buffer,SQL Server使用Free buffer来加载新的数据页。
  • Page
    Life Expectancy
    :PLE,数据页驻留在内存中的时间。如果SQL
    Server没有新的内存需求,或有空闲的内存来完成新的内存需求,那么Lazy
    Writer不会被处罚,Page会一直驻留在Buffer Pool中,那么Page Life
    Expectancy会维持在一个比较高的水平;如果Page
    Life总是高高低低,表明SQL Server存在内存压力。PLE的参考数值是:Max
    Server
    Memory/4GB*300s,如果PLE值长期低于参考值,内存可能存在瓶颈。
  • Page
    Reads/sec
    :每秒从Disk读取的数据页数,即物理读的次数,如果用户访问的数据都缓存在内存中,那么SQL
    Server不需要从物理Disk上读取页面。由于物理IO的开销大,Page
    Reads操作一定会影响SQL Server的性能。
  • Free list
    stalls/sec
    :等待一个Free
    Page的请求数量,SQL
    Server申请从Disk加载一个Page到内存中,必须在内存中分配一个Buffer,Buffer
    Manager负责维护Free Buffer List,如果Free List没有任何Free
    Buffer,那么请求必须等待,直到有空闲的Buffer使用,才能将Disk中的Page加载到内存中。

BCHR only responds to significant memory
pressure in conjunction with I/O subsystem pressure, or possibly
fragmentation i.e. under conditions that impedes page read-ahead to the
point that SQL Server becomes much less effective at populating the data
cache with the required pages, before the query processor actually
requires them for use.

经常使用后四种计数器,探测系统的内存压力,前三种,仅供参考,在此,感谢 wy123 的帮助。

2,从Memory Manager计数器监控服务器内存总体使用情况

四,SQL Server的Memory Manager计数器

在一个非常繁忙的系统中,Lock内存和授予内存是常用的计数器:

Memory
Manager计数器用于监控服务器内存总体使用情况,在一个非常繁忙的系统中,Lock内存和授予内存是常用的计数器:

  • Total
    Server Memory (KB):SQL Server当前使用的内存总量
  • Target
    Server Memory (KB):SQL Server能够使用的内存总量
  • Lock
    Memory (KB):SQL Server用于锁的内存总量
  • Grant
    Workspace Memory (KB):授予内存,SQL
    Server用于执行hash,排序和创建Index操作而消耗的内存总量
  • Memory
    Grants Pending
    (KB):等待内存授予的进程数量,如果进程不能获得指定数量的内存,那么进程将不会开始执行
  • Total
    Server Memory (KB):SQL Server当前使用的内存总量
  • Target
    Server Memory (KB):SQL Server能够使用的内存总量
  • Lock Memory (KB):SQL
    Server用于锁的内存总量
  • Grant Workspace Memory
    (KB):授予内存,SQL
    Server用于执行hash,排序和创建Index操作而消耗的内存总量
  • Memory Grants Pending
    (KB):等待内存授予的进程数量,如果进程不能获得指定数量的内存,那么进程将不会开始执行

 分析图表,除了Grant Workspace Memory
有变化之外,其余4个计数值都没有变化,说明SQL
Server执行的操作需要授予内存,而Memory Grants Pending
计数值很小,几乎为0,说明SQL Server 不存在内存压力。

五,使用Performance Counter监控SQL Server数据库系统的整体性能

菲律宾太阳娱乐集团 6

发表评论

电子邮件地址不会被公开。 必填项已用*标注