SQL Server性能计数器架构

1. WINDOWS性能计数器介绍

操作系统可以使用性能监视器图形工具来测量系统、应用程序、服务、驱动程序的性能信息。通过性能计数器可以查看SQL Server对象、性能计数器以及其他对象的行为，这些对象包括处理器、内存、缓存、线程和进程。每个对象都有一个相关的计数器集，用于测量设备的使用情况、队列长度、延迟情况。

为了方便批量管理，可以通过命令语句查看和添加计数器。PerfMon.exe中可以查看性能对象、性能计数器和对象实例，可通过添加计数器来查看相关描述信息。TypePerf.exe可以把Windows操作系统的性能计数器数据输出到命令窗口或写入到支持该功能的日志文件格式中。

1. SQL Server计数器收集、统计架构图

从图中可以看出

1. 生产服务器通过ODBC访问监控监控服务器的SQLPerfData，将数据写入该数据库。
2. 通过作业计算将写入SQLPerfData的数据写入到SQLPerfDataStat数据库。

   1. SQL Server计数器部署方案

我们通过ODBC直接访问到监控服务器的数据库，每15秒钟将数据传输到监控服务器；SQL Server数据库需要监控的性能包括：CPU、IO、内存、网络、SQLServer阻塞、并发、缓存等；我们选择了64个计数器，每个服务器的计数器实例个数不同，经过筛选最终每个服务器达到百个以上计数器实例；计数器的详细属性和用处请查看计数器属性详解。如此多的计数器，我们采用脚本批量部署，具体方式可以查看文档《SQL Server性能计数器部署_HuangCH_20141202》

1. SQL Server计数器收集汇总检查方案

如上图所示，计数器架构分别存储三个数据库SQLPerfData、SQLPerfDataStat、SQLPerfDataStat_Histroy；这三个计数器的功能具体如下：

1. SQLPerfData接收计数器信息

DisplayToID：计数器自动生成，记录计数器启动信息

CounterDetails：计数器自动生成，记录计数器的基本信息

CounterData：记录计数器传入的数据

?

?

1. SQLPerfDataStat统计计数器信息

基本信息模块：

CounterDetails_Collect：用于同步 CounterDetails表数据，用于轮训统计

CounterDetails_Collect_bak：无用计数器备份

CounterDetails_Dts：统计传输控制表，以服务器为单位，记录服务器统计传输时间。

CounterDetails_Report：同步CounterDetails_Collect表信息，通过优化，用于报表显示作用。

?

数据模块：

CounterData_FifteenSeconds：15秒数据收集，用于同步CounterData表数据。

CounterData_OneMinute：一分钟数据收集，15秒数据均值计算而成；适合查看当天数据。保留7天数据。

CounterData_FiveMinute：五分钟数据收集，一分钟数据均值计算而成；适合查看4天内数据。保留7天数据。

CounterData_HalfHour：30分钟数据收集，一分钟数据均值计算而成；适合查看一周内数据。保留7天数据。

CounterData_OneHour：一小时数据收集，一分钟数据均值计算而成；适合查看一个月内数据。保留永久

CounterData_SixHour：六小时数据收集，一分钟数据均值计算而成；适合查看一年内数据。保留永久

?

基线模块：

CBaseCounterData_OneMinute：同比基线，可以计算未来一周的数据走势。保留7天数据。

RBaseCounterData_OneMinute：环比基线，可以计算未来一天的数据走势。保留7天数据。

?

手动维护模块：

CounterTypeDetails：计数器类型和描述信息记录表。

MonitorContorl：手动维护，计数器报警监控控制表。

3、SQLPerfDataStat_Histroy归档计数器信息

CounterData_OneMinute_bak：一分钟数据备份，保留永久。

CounterData_FiveMinute_bak：五分钟数据备份，保留永久。

CounterData_HalfHour_bak：30分钟数据备份，保留永久。

基线模块：

CBaseCounterData_OneMinute_bak：同比基线备份，保留7天。

RBaseCounterData_OneMinute_bak：环比基线备份，保留7天。

1. SQL Server基线算法

SQL Server计数器算法采用同比和环比两种方式。

同比：可以计算未来一周的基线数据，取近八周同一天同一时刻的均值。同比可以消除由于业务变动带来的波动，具有报警对比价值。下图红线同比数据，表示历史上该时段存在异常升高。

环比：可以计算未来一天的基线数据，取近两周每天同一时刻的均值。环比数据可以说明数据近期走势。具有优化对比价值。从上同比图可以看出117-27-139-238服务器cpu在历史上存在异常升高，下图红线环比比数据，却与蓝线同步，说明此异常在近期已经正常。

?

1. SQL Server性能报警监控

性能目前监控cpu、磁盘空间，用户并发三者的计数器数值情况，如下图示，具体请查看文档：

?

1. 计数器属性详解

常规计数器

收集操作系统服务器的服务器性能信息，包括Processor、磁盘、网络、内存

1. Processor 处理器
   1. % Processor Time指处理器用来执行非闲置线程时间的百分比。通过此计数器可以确定服务器当前CPU使用百分比。
   2. % Privileged Time是在特权模式下进程线程执行代码所花时间的百分比。当调用 Windows 系统服务时，此服务经常在特权模式运行，以便获取对系统专有数据的访问。
2. PhysicalDisk物理磁盘（某个RAID）
   1. Avg. Disk Queue Length等待请求的队列长度，若此值过大，说明磁盘存在瓶颈，自然磁盘延迟的值也会过大。
   2. Avg. Disk sec/Read；Avg. Disk sec/Transfer；Avg. Disk sec/Write这三个计数器表示磁盘每次读写需要多少时间，表示磁盘的延迟情况，以秒为单位，若延迟超过25mm表示磁盘性能较差。
3. LogicalDisk逻辑磁盘（某个卷）
   1. % Free Space某个磁盘的空闲百分比；Free Megabytes 磁盘剩余大小MB
   2. Disk Read Bytes/sec；Disk Transfers/sec；Disk Write Bytes/sec这三个计数器表示每秒磁盘的读写情况，表示磁盘的IO性能（iops）
4. Memory 内存
   1. Available Mbytes可以内存大小MB，此内存能立刻分配给一个进程或系统使用。它等于分配给待机(缓存的)、空闲和零分页列表内存的总和。
   2. Cache Bytes 系统使用的内存
   3. Commit Limit可以申请的内存总大小（虚拟内存和物理内存）；Committed Bytes已经使用的内存大小（虚拟内存和物理内存）；均是以Byte（字节）为单位。
   4. Free System Page Table Entries指系统当前未使用的页表项数目。表示内存是否空闲。
   5. Page Faults/sec 每秒出错页面的数量，也就是未在内存中命中的页面数。因此伴随着Pages/sec计数器是由于出错页面从磁盘读取数据页的速度。此值越大说明内存可能存在一定瓶颈，或者存在大量数据存取。
5. Network Interface 网络接口
   1. Current Bandwidth指以位/每秒估计的网络接口的当前带宽。对于那些带宽不变的或无法估计出准确数据的界面，这个值为名义带宽。
   2. Bytes Received/sec；Bytes Sent/sec；Bytes Total/sec每秒种发送和接收的字节数，表示当前网络的使用情况。
   3. Output Queue Length输出数据包队列，此值大于2说明传输存在延迟。
   4. Packets Outbound Discarded 被选为丢弃的数据包数目，丢弃的原因可能是释放缓冲空间。Packets Outbound Errors 由于错误不能传输的数据包数目
   5. Packets Received Discarded 被选为丢弃的数据包数目，丢弃的原因可能是释放缓冲空间。Packets Received Errors 由于错误不能传输的数据包数目

SQL Server计数器

SQL Server中的Access Methods对象提供监视访问数据库中的逻辑数据的计数器；Buffer Manager计数器监视对磁盘上的数据库也的物理访问。

1. Access Methods 存取方式

Access Methods监视用于访问数据库中存储的数据的方法有助于确定是否可以通过添加或修改索引、添加或移动分区、添加文件或文件组、对索引进行碎片整理或者重写查询来提高查询性能。?Access Methods?计数器也可用于监视数据库中数据、索引和可用空间的数量，从而指示每个服务器实例的数据卷和碎片。索引的碎片过多会降低性能。

1. Page Splits/sec每秒由于索引页溢出而发生的页拆分数。此数据越大表示某表的修改频率较高，且要确认该索引是否适合建立该字段上。
2. Workfiles Created/sec每秒创建的工作文件数
3. Worktables Created/sec每秒创建的工作表数

1. Buffer Manager 缓存管理

   监视用于存储数据页的内存；读取和写入数据库页时的物理io等，通过此对象可以判断是否存在物理内存瓶颈可以了解服务器的查询性能等等。

   1. Buffer cache hit ratio命中高速缓冲区页的百分比。若此值过低说明可能内存存在一定瓶颈
   2. Database pages缓冲池中数据库内容的页数
   3. Page life expectancy页指示页面在没有引用的情况下，在此节点的缓冲池中停留的时间（以秒计）。此值若降低说明，服务器频繁查询数据库中数据。
   4. Checkpoint pages/sec Checkpoint的执行频率，若执行的频率过高。则说明服务器过于频繁的修改数据。
   5. Lazy writes/sec 若懒写入过于频繁，说明没有空闲的缓冲区供使用，内存可能存在一定瓶颈。"惰性编写器"是一个系统进程，用于成批刷新脏的过期的缓冲区（包含更改的缓冲区，必须将这些更改写回磁盘，才能将缓冲区重用于其他页），并使它们可用于用户进程。
2. Memory Manager 内存管理?

   监视总体的服务器内存使用情况，以估计用户活动和资源使用，有助于查明性能瓶颈。

   1. Memory Grants Pending指定等待工作空间内存授权的进程总数。指等待分配内存的进程数量，若此值过高或内存或磁盘存在一定瓶颈。
   2. Target Server Memory (KB)；Total Server Memory (KB)理想上能使用内存量与内存管理器提交的内存量。若此两值存在差异则说明内存异常。
3. General Statistics 常规统计
   1. User Connections；Logins/sec；Logouts/sec 用户并发情况
   2. Processes blocked当前阻塞数量
   3. Temp Tables For Destruction等待被清除系统线程破坏的临时表/表变量数。无用的临时表和表变量。
4. SQL Statistics; Wait Statistics 请求与等待统计
   1. Batch Requests/sec；SQL Compilations/sec；SQL Re-Compilations/sec每秒的请求、编译和重编译的次数，这些对于优化供参考。
   2. Lock waits等待锁的进程数。
   3. Log write waits等待写入日志缓存的进程数
   4. Page IO latch waits与页 I/O 闩锁相关的统计信息。用于判断内存或磁盘的瓶颈情况
   5. Page latch waits与页闩锁（不包括 I/O 闩锁）相关的统计信息。用于判断内存或磁盘的瓶颈情况
5. Databases 针对数据库级别
   1. Log Flush Wait Time；Log Flushes/sec日志刷新等待和频率，若数据异常则可能存在磁盘瓶颈
   2. Transactions/sec 事务数
6. Locks 锁
   1. Lock Requests/sec；Lock Timeouts/sec锁请求频率；锁超时频率
   2. Number of Deadlocks/sec 死锁情况。