SQLServer性能监控指标说明

性能监控指标说明
--MSSQL SERVER 2005
赵小艳
2010年6月30日
从MSSQL SERVER2005开始，微软开始了漫长的向ORACLE学习之旅，本文以oracle优化为基础，整理了一部分MSSQL SERVER2005监控指标，这些指标不能全部反映MSSQL SERVER2005的性能信息，因此还需要从更多的开发的角度后继完成优化过程，其中最重要的一环是设计。

由于下一步的工作就是实际的优化，请大家指出文档中的不足和错误之处。

本文整理用到的资料：清华大学出版社的《SQL SERVER 2005性能调优》，微软技术资料库，MSDN。

1. 配置硬件 (5)
1.1. 内存 (5)
1.1.1. 物理地址空间 (5)
1.1.2. 虚拟地址空间 (5)
1.1.3. 虚拟内存管理器 (5)
1.1.4. 3GB内存 (5)
1.1.5. PAE (6)
1.1.6. AWE (6)
1.1.7. 选择/3GB 、/PAE、还是/AWE (6)
1.1.8. 64bit系统内存配置 (6)
1.1.9. 内存预计大小 (6)
1.2. I/O (7)
1.2.1. 网络IO (7)
1.2.2. 磁盘IO (7)
1.2.3. 存储设计 (7)
1.2.4. 配置分区 (7)
1.2.5. 扇区对齐 (8)
1.2.6. 卷的类型 (8)
1.2.7. NTFS分配空间的大小 (8)
1.2.8. 磁盘破碎 (8)
1.3. CPU (9)
1.4. 32bit x86处理器 (9)
1.4.1. 多核 (9)
1.4.2. 超线程 (9)
1.4.3. 64bit (9)
1.4.4. X64或IA64 (9)
1.4.5. 高速缓存 (10)
1.5. 系统架构 (10)
1.5.1. SMP (10)
1.5.2. NUMA (10)
1.5.3. SOFT NUMA (10)
2. 系统监控指标 (11)
2.1. 内存 (11)
2.1.1. 外部压力 (11)
2.1.2. 内部内存压力 (11)
2.1.3. 确认内存瓶颈 (11)
2.2. 磁盘 (12)
2.2.1. 基于配置的磁盘瓶颈 (12)
2.2.2. 基于模式的磁盘瓶颈 (12)
2.2.3. 指标 (13)
2.3. Cpu (13)
2.3.1. 基于配置的cpu瓶颈 (14)
2.3.2. 基于模式的cpu瓶颈 (14)
2.4. 数据库镜像性能 (14)
3. 服务器设置 (14)
3.1. Cpu (14)
4. 数据库等待事件 (15)
4.1. 常见等待类型 (15)
5. 数据库指标 (16)
5.1. 指标采集和排列 (16)
5.1.1. 操作系统资源 (16)
5.1.2. 重要统计信息 (17)
5.1.3. 等待事件 (18)
5.1.4. 数据文件和日志文件 (18)
5.1.5. TEMPDB使用 (21)
5.1.6. 系统当前资源高消耗sql (22)
5.1.7. 当前阻塞和物理io多的会话请求信息 (22)
5.1.8. 查询计划多的sql语句 (24)
5.1.9. 索引使用情况 (25)
6. 优化方法 (26)
6.1. 统计信息 (26)
6.2. 绑定变量 (26)
6.3. 提示 (27)
7. 错误 (27)
1.配置硬件
1.1.内存
1.1.1.物理地址空间
物理地址是处理器用来访问位于其总线上的所有部件的地址集合。

PAE地址总线是36bit。

64bit服务器生产商将地址总线限制在44bit。

1.1.
2.虚拟地址空间
在32bit系统中，进程有4gb虚拟地址空间，虚拟内存由内核和用户进程共享。

内核和用户进程各使用2g。

1.1.3.虚拟内存管理器
虚拟内存管理器（vmm）管理所有内存，并在系统中所有使用内存的进程间共享。

虚拟内存管理器使每一个进程在需要内存时提供4gb的虚拟内存的错觉，事实上所有进程争夺有限的物理内存。

操作系统的内存有限，为了让进程有使用4gb虚拟内存的错误，用交换空间来保证所有进程的虚拟内存空间的使用。

如果系统有足够的物理内存，那么vmm只要分发内存并在进程结束时清理内存即可。

如果没有足够的物理内存，vmm必需为每一个进程在需要时提供所需的内存。

Vmm通过页面文件暂存进程有一段时间没有访问的数据来实现，这个过程叫分页。

进程的数据经常被分页或者交换到磁盘。

1.1.4.3GB内存
增加32bit系统中进程内存数量的一种方法是将分配给内核的一部分空间拿来用作用户模式地址空间。

/3GB选项将边界移动到3GB，每个进程额外获得1GB的vas。

此时内核只有1gb虚拟内存，有可能会产生问题导致崩溃。

/USERVA是windows server2003新增的功能，提供从内核中获取指定的不同地址空间数量的方式，使每个进程额外获得0gb到1gb的地址空间，与3GB方式相同，当然也会导致崩溃。

1.1.5./PAE
Intel引入用来突破32bit地址总线的限制，将地址总线扩展为36bit。

允许32bit最多访问64GB内存，要求是：企业版或者更高版本的windows服务器，必需运行在windows 2003 sp1之上的版本。

这个配置也是在boot.ini中。

也可以在datacenter版本的os下，也会使用PAE。

1.1.6.AWE
地址窗口扩展（AWE）是windows的api，允许32bit的进程映射虚拟地址空间以外的内存。

进程可以使用物理内存存储数据，但是必须映射到vas中。

AWE是内存到内存的传递，因此速度更快。

允许进程使用多于2gb的内存，但是仍然局限于4GB。

因此建议和PAE联合使用。

MS建议在64bit上也使用AWE。

当然在64bit的sql server上无法激活AWE，如果账号有LOCK PAGES IN MEMORY的高级用户权限，将自动使用AWE来访问内存。

1.1.7.选择/3GB 、/PAE、还是/AWE
如果物理内存大于等于16GB，不支持3GB和PAE联合使用。

这是因os要求内核地址空间必须要大于1gb，才能管理16gb的内存空间。

不要3GB和PAE，这两个选项都修改资源数量，/3GB将非分页池的大小从256mb减少到128mb，/PAE不减少非分页池的大小，却将每次分配的大小从4字节倍增到8字节。

联合使用对系统进行了两次扼杀。

少于4GB物理内存，均不适用。

4GB物理内存，如果内存是瓶颈，使用3GB
4GB物理内存以上，使用/PAE和/AWE。

尽量使用64bit。

1.1.8.64bit系统内存配置
X64和IA64之间有轻微不同的内存布局，并提供不同大小的VAS。

IA64提供7TB，X64提供8TB。

Windows server 2003 sp1目前支持的最大物理内存为2TB，对于sql server而言，不用担心使用/PAE或者/3GB。

他有足够的虚拟地址空间使用。

1.1.9.内存预计大小
min server memory 和max server memory
内存估计大小=（每个所有者 64 字节 + 32 字节）*锁的数量+(3 * network_packet_size （默认4KB） + 94 KB)*会话数
如果会话有多个活动结果集，那么内存使用量为(3 + 3 * num_logical_connections) * network_packet_size + 94 KB
Sql server启动时根据配置（系统最大物理内存数，系统最大线程数和其他参数）设置缓冲区大小。

Sys.dm_os_sys_info中记录当前占用操作系统资源的杂项信息。

1.2.I/O
I/O包含网络io和磁盘io。

数据库对磁盘性能相当敏感。

1.2.1.网络IO
设计时尽量减少返回给客户端的数据量。

1.2.2.磁盘IO
1.2.3.存储设计
磁盘驱动器有两种：SCSI或者ATA。

ATA驱动器是低端市场的桌面计算的选择。

SCSI成本高，价值体现在数据完整性，可伸缩性，可靠性和高占空比（预期磁盘驱动器用法）。

高占空比意味着驱动器被设计为用于持续的7*24小时使用。

串行ATA(SATA)和串行SCSI（SAS）是现今最常见的新驱动器。

串行代表磁盘驱动器的接口，基本上与并行产品相同，不同在于磁盘内加了新的高速接口。

直连式存储（DAS）是讲一个服务器直接连接到磁盘阵列上，提供最佳的性能，但是在企业级应用中的上百个服务器有不同的磁盘空间需求，但是受限于所能提供的磁盘规模，导致大量的空间浪费。

存储区域网络（SAN）提供了一个公共存储池，给网络提供健壮动态的存储。

使用主机总线适配器来进行数据传送，使用大量高速缓存。

HBA队列长度是在SAN中能够并行请求的数目限制，默认在8到32之间，由于SQL SERVER的io是密集型，因此该值应当设置为64或者更大。

如果没有使用SAN，可以将设备管理器队列长度设置为64到128。

HBA驱动器有两种：SCSIPort和StorPort。

StorPort比SCSIPort新。

数据文件布置一般建议将数据文件、日志文件和tempdb存放在不同的物理轴上。

1.2.4.配置分区
磁盘安装后需要配置分区，目前有两种：MBR和GPT。

现在只有windows server 2003 sp1以上才支持GPT，并且在failover clustering中不支持，目前仍使用MBR，但是GPT是大势所趋。

1.2.5.扇区对齐
扇区是磁盘最小存储空间，通常为512字节。

磁道是位于磁盘一个圆周上的，由扇区组成的圆周轨迹。

每磁道有63个扇区。

块是扇区的逻辑称谓。

由于不同存储供应商在定义磁道时的差异导致对齐错误，会出现写入跨越2个条带，从而影响性能。

有两种方法进行扇区对齐。

DISKPAR（快对齐）和DISKPART（KB对齐），windows 2003 sp1更合适的是DISKPART。

1.2.6.卷的类型
卷是磁盘分区的逻辑抽象，分基本卷和动态卷。

基本卷提供多年以来使用的简单高效的存储方式；动态卷在windows 2000引入，支持额外的特性，如跨区可扩展卷和软件驱动RAID。

随着硬件的发展，动态磁盘被放弃了。

1.2.7.NTFS分配空间的大小
在windows中格式化一个分区时，会指定NTFS分配单元的大小，微软实验室测试表明，对于数据文件和日志文件的分区，最佳单元大小是64KB。

1.2.8.磁盘破碎
在SQL SERVER中破碎可能一两种形式发生：发生在数据陈旧并经历了大量的插入、更新、删除时；发生在创建文件时，文件系统没有足够的连续空间在单独的片段中创建文件，结果是导致一个文件分布在磁盘表明的多个文件片段中。

当sql server文件一旦被创建后就不会变得更加破碎，如果创建时没有连续的空间，文件在多个片段中创建，如果此后进行了碎片整理，那么文件就不会呈碎片状，将来也不会。

理想的场景是对sql server文件使用专用磁盘，并设置合适的大小，并禁用自动增长。

如果没有专用的驱动器用于数据库，而且构建工作从头开始，那么最好的方法是：
1.安装os
2.整理磁盘碎片
3.安装所有应用程序
4.整理磁盘碎片
5.以最大大小创建数据文件和日志文件
6.停止sql server,检查碎片，有需要整理磁盘碎片
7.禁用自增长，或者将增量设置大一些。

8.周期性进行磁盘碎片整理。

1.3.CPU
1.4.32bit x86处理器
以后会被64bit取代。

1.4.1.多核
摩尔定律是处理器的能力每18个月就会翻番，现在有变为每10个月就翻番的趋势。

多核是指同一个芯片上有个cpu。

Ms的per-processor的许可证发给每个插槽，而不是每个内核。

对用户来说4个双核cpu同8个单核服务器性能相当，但是许可成本减半。

1.4.
2.超线程
线程是运行在cpu上的执行单元，cpu每次只能执行一个线程，它通过在线程间切换产生并发的错觉。

超线程是intel处理器的技术，试图通过复制架构状态来提供两个逻辑cpu，完成同时使用处理器不同部件执行不同任务的工作。

使用超线程提供理论上30%性能的好处，实践中，仅仅是1.1到1.15倍cpu的能力。

如果工作负荷是从高速缓存中行紧致循环，那么超线程不会有好处。

如果选择并行计划时，并行计划所作的事情是假定每个处理器在给定的时间内能够完成相同数量的工作这个前提下，将要做的工作分割并指派给可用的处理器上，在使用超线程时，任何不是当前执行的线程将被停止，所以并行计划在超线程下得不到性能提高。

在windows server2000上意识不到超线程，但是2003 sp1可以使用，随着处理器的更新换代，超线程越来越好。

有机会通过超线程获得好处，但是由于测试成本以及应用程序的成本，一般来说客户会关闭超线程。

1.4.3.64bit
64bit有一个潜在的缺点，就是缺少64bit驱动程序。

有些应用还未开发出支持64bit的应用。

1.4.4.X64或IA64
X64处理器运行速度超过3Ghz，IA64在1.6Ghz左右。

IA64处理器的高速缓存大于X64处理器的高速缓存。

IA64越来越在专用机器上出现，包括高性能工作站，大型高可伸缩性系统。

1.4.5.高速缓存
高速缓存产生的原因在于处理器速度远远高于主存储器，存储器无法跟上处理器对内存的渴望。

处理器设计者为了解决这一问题，增加了多层高速缓存。

处理器分L1,L2,L3，离处理器越远，规模越大速度更慢。

L1只保存地址查找、数据和指令，L2,L3保存内存的任意备份。

Processor Cache被实现为透明的look-thru cache，芯片上有着控制功能用来管理填充高速缓存的过程，管理高速缓存条目。

Sql server2000启动时占用29M大小内存，使用sqlcmd连接到数据库并执行一个简单的查询，内存增0.5m。

SQL SERVER2005启动就有50m，同样的命令，内存增加3m左右。

Sql server2000的sqlservr.exe大小为8.9m，SQL SERVER2005该文件大得多，为28m，增加的代码为高速缓存重用部分，包含查询计划重用，增加的内存则是查询计划存储空间等。

1.5.系统架构
主要是指机器架构，如单插槽，双插槽，四插槽，甚至对称多处理器（SMP）.
1.5.1.SMP
对称多处理器通过一个系统总线连接到一个共享内存上。

SMP加剧了处理器和内存之间的速度问题。

系统处理器有数十个的时候，系统总线会成为瓶颈。

1.5.
2.NUMA
非一致性内存访问（NUMA）是一种超越SMP的可伸缩性的架构，NUMA系统中有多个系统总线，每一个系统总线能连接的处理器最大为4，最多4个cpu共享一块内存。

以下语句确认系统是否使用NUMA
Select distinct memory_node_id from sys.dm_os_memory_clerks
返回0说明未使用NUMA。

AMD处理器都作为NUMA实现的。

1.5.3.SOFT NUMA
这是SQL SERVER2005的新特性，允许将cpu聚合成soft-NUMA节点，如果没有硬件NUMA,但是cpu个数很多，可以提高性能。

建议使用SOFT NUMA来对cpu进行分组。

2.系统监控指标
注意：凡是sqlserver的指标值，都可以使用以下sql语句获得：
select cntr_value from sys.dm_os_performance_counters
where object_name='MSSQL$ZXY:Buffer Manager'and counter_name='total pages'
以2.1.1第2点的Sql server:Buffer manager→total pages为例，其中蓝色字体代表实例名称，将红色字体替换为Buffer manager，粉色字体替换为total pages即可。

需要注意的是复制时注意空格要和原来一样。

2.1.内存
2.1.1.外部压力
1、Process→working set 显示每一个进程虚拟地址空间中最近引用的页面规模。

消耗内存最
多的进程就是消耗大量内存的应用。

指一个进程的4GB虚拟地址空间中被映射到RAM 中的部分的大小，通常是该进程的虚拟内存中的活跃部分。

2、Sql server→Sql server:Buffer manager→total pages 显示sql server已获得的页面数
3、Sql server→Sql server:buffer manager→target pages 显示sql server的buffer pool必需的
理想页面数。

2.1.2.内部内存压力
内部压力通常是收缩缓冲池导致的。

缓冲池规模变小，通常是sp_configure的max server memory的值减小导致。

或者是其他数据库对象消耗了内存空间导致，如：作业，扩展存储过程，com对象，sqlclr，链接服务器。

通过dbcc memorystatus查看buffer counts部分，确定内存是否存在压力。

其中committed memory和target memory最重要。

Committed memory表示sql server已经得到的内存数量，target memory表示有效运行所需的内存数量。

当两个存在差别过大，说明可能存在内存压力。

2.1.
3.确认内存瓶颈
1、SQLServer:Buffer Manager→Page Life Expectancy 显示数据页在缓冲池中驻留的时
间长度（单位是秒）。

值越大，系统越健康。

如果存在内存瓶颈，这个值会低于300s 或者更少。

2、SQLServer:Buffer Manager→Buffer cache hit ratio 显示数据库内存命中率，所请求的
数据或者说页面在缓冲池（物理内存）被找到的次数。

如果这个值很低，说明内存不足。

这个值至少大于98%。

3、缓冲池的大小由两个参数决定：sp_configure的min server memory和max server
memory。

当其他应用使用了这些内存后，操作系统就不会再将这些内存分给sql server.
4、SQLServer:Buffer Manager→Stolen pages显示了内存被其他进程挪用的页面。

当这
个指标与目标内存页面数比例较大时可能存在问题。

5、SQLServer:Memory Manager→Memory Grants Pending显示等待内存授权的进程队列。

这个指标值为0时理想状态。

6、SQLServer:Buffer Manager→Checkpoint pages/sec显示检查点操作每秒写入磁盘的
脏页数目。

如果这个值很高，说明缺少内存。

7、SQLServer:Buffer Manager→Lazy writes/sec 显示每秒将脏页从小写到磁盘的次数。

这个值应该尽可能接近0，当大于20或者更多，确信缓冲池不够。

8、Memtoleave大小基于工作者线程的数目动态变化，是一个独立的内存地址范围，供
链接服务器，扩展存储过程，com对象或第三方ddl使用。

2.2.磁盘
1、PhysicalDisk→AVG.Disk Aueue Length显示每一个磁盘的队列长度。

不要选择_TOTAL对象，
而是查看某驱动盘符的队列长度，如果参数值持续大于2，则可能影响性能。

2、PhysicalDisk→AVG.Disk sec/Read显示每次读取的平均磁盘时间。

3、PhysicalDisk→AVG.Disk sec/Write显示每次写入的平均磁盘时间。

4、如果存在磁盘性能问题，控制器使用率等问题，上面两个计数器的平均值高于正常值。

理想情况下值小于10毫秒。

5、SQL Server→Access Methods→Full scans/sec显示每秒请求完全索引扫描或者全表扫描
的数目，如果扫描频率每秒大于1，那么说明索引缺少或者索引比较差。

6、SQL Server→Access Methods→Page Splits/sec显示每秒页面拆分的次数。

可以通过适当
的索引维护或者好的填充因子来避免发生。

2.2.1.基于配置的磁盘瓶颈
1、数据文件和日志文件布局（将这两类文件隔离，并且将每一类文件也隔离）
2、数据文件和日志文件自动增长管理（手动管理）
3、Tempdb配置（指定初始化大小）
4、Tempdb内部争用
5、磁盘/控制器争用（用存储厂商的性能监控工具）
2.2.2.基于模式的磁盘瓶颈
1、索引不当或者缺少索引，使用数据库优化顾问（database tuning advisor）来解决。

或者
查看sys.dm_db_index_usage_stats。

2、文件和文件组，将读取数据量大且频繁的表单独放到文件或者组合到文件组中，可以适
当降低io并便于移植以提高性能。

3、分区，降低io
4、索引/表碎片，当索引和表的碎片较多时会产生页拆分（类似于oracle的行链接和行迁
移）。

产生的原意有两种：内部和外部。

内部碎片是指8kb数据页包含数据很少。

外部
碎片是指数据不是顺序存放的。

在dm_db_index_physical_stats提供当前碎片级别的所有信息；只有有聚簇索引的表才能重建索引；使用alter index命令重建索引。

2.2.
3.指标
1、SQL Server→General Statistics→Temp Tables Creation Rate显示每秒创建的临时表和临时
变量的数目。

2、SQL Server→General Statistics→Temp Tables For Destruction显示不再需要的正在等到销
毁的临时表的数目。

3、Sys.dm_db_task_space_usage和sys.dm_exec_requests查看每一个服务器进程使用的
tempdb空间，具体见5.1.5。

2.2.4.存储性能压力测试指标
1.IOPS是每秒能够为输入输出请求提供服务的度量。

得到的子系统实际吞吐量和io请求
的大小的函数。

2.Block size提供sql执行的IO的大小的指示
3.MB/sec每秒有多少MB的数据进入或者离开子系统。

tency为每一个io完成所必须等待的时间长度。

5.DISK Queue length磁盘队列的长度，说明当前io请求时排队等候的io请求的数目。

2.2.5.度量IO性能
%DISK Read Time,%Disk Time,%Disk Write Time %Disk Idle Time说明采用周期内磁盘读取，踩入，使用和空闲各占的百分比。

Avg.Disk Bytes/sec/Read, Avg.Disk Bytes/sec/Transfer, Avg.Disk Bytes/sec/Write说明每次读取、写入或者传送的平均字节数。

Avg.Disk Queue length, Avg.Disk Read Queue length, Avg.Disk Write Queue length说明了平均磁盘队列长度，磁盘队列长度是等待被送到磁盘的请求的数目。

Avg.Disk sec/Read, Avg.Disk sec/Transfer, Avg.Disk sec/Write说明io的延迟，每次读取或者写入所占用时间的长短。

Disk Bytes/sec, Disk Read Bytes/sec, Disk Write Bytes/sec说明以Bytes/sec为单位显示吞吐量。

Disk Read/sec, Disk Transfer /sec, Disk Write/sec显示得到的IOPS的数目。

2.3.Cpu
1、System→Processor Queue Length显示系统队列长度，如果平均值大于3，那么说明cpu
存在瓶颈。

2、Processor→%Privilege Time显示操作系统内部操作所花费的时间。

3、Processor→%User Time显示用户模式操作花费的时间
4、Processor→%User Time→sqlservr显示sql server进程消耗的cpu时间的确切数量。

这个
最好从性能监视器来看。

5、SQL Server→SQL Statistics→SQL Compilations/sec显示查询计划的编译次数。

6、SQL Server→SQL Statistics→SQL Re-Compilations/sec显示查询计划的重编译次数。

7、当SQL Compilations/sec和SQL Re-Compilations/sec次数过高的话，说明可能未使用绑定
变量导致计划重新编译，或者说重新编译次数比编译次数过高的话，那么说明存在应用上的瓶颈。

2.3.1.基于配置的cpu瓶颈
很难有建议或者计数器来协助sp_configure的cpu相关配置（Affinity Mask,Hyper Threading,Priority Boost,Maximum Degree of Parallelism,Max Worker Threads），故不加以描述。

2.3.2.基于模式的cpu瓶颈
当SQL Compilations/sec和SQL Re-Compilations/sec次数过高的话，说明可能未使用绑定变量导致计划重新编译，或者说重新编译次数比编译次数过高的话，那么说明存在应用上的瓶颈。

2.4.数据库镜像性能
目前不关心，以后补充。

3.服务器设置
3.1.Cpu
建议不使用超线程
如果系统有8颗以上cpu并且没有硬件NUMA,那么建议配置soft-NUMA。

系统是否NUMA判断如下：
Select
Case count(distinct parent_node_id) when 1 then ‘NUMA disabled’ else ‘NUMA enabled’ end as a From sys.dm_os_schedulers where parent_node_id<>32
4.数据库等待事件
SQL Server→Wait Statistics→Lock Waits显示进程等待获得锁的时间。

SQL Server→Wait Statistics→Memory Grant Queue Waits显示进程在队列中等待内存锁花费的时间。

SQL Server→Wait Statistics→Page I/O Latches
4.1.常见等待类型
WAITFOR:是人工挑起的等待，不用关注。

LAZYWRITER_SLEEP:表示lazywiter进程处于睡眠状态并等待运行的时间，不用关注。

SQLTRACE_BUFFER_FLUSH：当系统等待sql trace缓冲区被写到磁盘时出现的等待。

一般可以看做永远存在的特性。

SLEEP_BPOOL_FLUSH:检查点等待时，会计数。

RESOURCE_SEMAPHORE:所有hash，sort，批量复制以及索引创建等操作均要使用工作区。

一般来说工作区大小为25%到70%之间。

说明了内存压力。

CXPACKET：显示并行正在发生，在oltp系统中，他不应该是主要等待事件，但是代表了主动生成cpu时间的线程，如果是主要等待事件，说明存在cpu瓶颈。

SOS_SCHEDULER_YIELD:当任务自动产生处理器时间并等待再次被调度时发生，如果这个等待值比较高，说明存在cpu等待。

RESOURCE_SEMAPHORE_QUERY_COMPILE显示系统中并发编译的次数。

这个次数过多说明需要查询参数化。

I/O_COMPLETION,ASYNC_I/O_COMPLETION:显示等待非数据页面的I/O,或者长期运行的I/O绑定操作，说明了磁盘瓶颈。

PAGEIOLATCH_*显示将数据页从磁盘检索到内存的时间，说明了IO压力
LCK_M_*等待获得某个资源的锁。

LOGBUFFER：说明正在等待日志缓存写入事务日志文件，大的值说明日志文件磁盘io瓶颈。

LOGMGR_RESERVE_APPEND：日志文件太小，添加日志文件。

PAGELATCH_*说明在任务等待不处于I/O请求中的缓冲区闩锁时发生，说明有连接同时想访问缓存中同一个数据页。

LATCH_*
5.数据库指标
5.1.指标采集和排列
5.1.1.操作系统资源
5.1.1.1.采集方式
下面的语句获得SQL Server 可用资源及其已占用资源的有用杂项信息的指标，可以每15分钟采集一回。

select cpu_count as系统的逻辑CPU数,hyperthread_ratio as逻辑内核数与物理内核数的比,round(physical_memory_in_bytes/1024/1024,0)as物理内
存,round(virtual_memory_in_bytes/1024/1024,0)as虚拟内
存,bpool_committed as缓存池已提交内存页数,bpool_commit_target as缓存池目标内存页数,bpool_visible as进程虚拟地址空间可用内存页数,max_workers_count as最大工作线程数--,process_kernel_time_ms as 内核态毫秒
数,process_user_time_ms as 用户态毫秒数,sqlserver_start_time as 启动时间from sys.dm_os_sys_info
5.1.1.2.指标说明
5.1.2.重要统计信息
5.1.2.1.采集方式
下面语句获得高速缓冲区命中率的值，采集可以每30s采集一次
select round(cast(sum(case when counter_name='Buffer cache hit ratio' then cntr_value else 0 end)as decimal(38,16))/sum(case when
counter_name='Buffer cache hit ratio base'then cntr_value else 0 end)*100 ,2)as b
from sys.dm_os_performance_counters where counter_name like'Buffer cache hit ratio%'
下面的语句返回计划缓冲区不同池的命中率，可以每30s采集一次：
select instance_name,round(cast(sum(case when counter_name='Cache Hit Ratio'then cntr_value else 0 end)as decimal(38,16))/sum(case when counter_name='Cache Hit Ratio Base'then cntr_value else 0
end)*100 ,2)as b
from sys.dm_os_performance_counters where
object_name='MSSQL$ZXY:Plan Cache'
group by instance_name
having sum(case when counter_name='Cache Hit Ratio Base'then cntr_value else 0 end)<>0
下面语句获得每秒索引扫描和全部扫描次数，可以每30s采集一次：
select sum(case when counter_name='Full Scans/sec'then cntr_value else 0 end)as full_table_scans,
sum(case when counter_name='Range Scans/sec'then cntr_value else0 end) as range_scans
from sys.dm_os_performance_counters where counter_name in('Full Scans/sec','Range Scans/sec')
5.1.2.2.指标说明
5.1.3.等待事件
按照平均等待事件来说明数据库等待事件
5.1.3.1.采集方式
select wait_type,waiting_tasks_count,wait_time_ms,max_wait_time_ms,signal_wait_time_ms from sys.dm_os_wait_stats where waiting_tasks_count>=1
order by wait_time_ms/waiting_tasks_count desc
5.1.3.2.指标说明
5.1.3.3.等待事件说明
参见第四章。

5.1.4.数据文件和日志文件
5.1.4.1.采集方式
下面分别说明数据库空间，数据库空间使用等信息，采集周期可以是每天一次，或者每周一次。

数据库空间：
select name as数据库空间,data_space_id as空间编号,type as空间类型,is_default as默认数据空间标志from sys.data_spaces
数据库空间使用信息：
sp_helpdb
返回信息如下：
sp_helpdb数据库名称
取返回的第二个结果集，具体信息如下：
所有数据库对应的数据文件和日志文件io信息：
select dbid,eqp.fileid,NumberReads,BytesRead /1024/1024 as
bytesread,IoStallReadMS,
Numberwrites,Byteswritten /1024/1024 as
bytewitten,IoStallwriteMS,IoStallMS
from(select*from sysfiles)aa cross apply fn_virtualfilestats
(null,aa.fileid) eqp
所有数据库临时表空间文件空间分配信息：
select as数据库, as文件的逻辑名称,c.filename as文件物理名称, version_store_reserved_page_count as统一区总页
数,unallocated_extent_page_count 未分配取总页数,
user_object_reserved_page_count as统一区用户对象用户对象总页
数,internal_object_reserved_page_count
as统一区文件内部用户对象总页数, mixed_extent_page_count as混合区总页数
from sys.dm_db_file_space_usage a inner join sysdatabases b on
a.database_id=
b.dbid
inner join sysfiles c on a.file_id=c.fileid
5.1.4.2.指标说明
数据库空间：
所有数据库临时表空间文件空间分配信息：
5.1.5.TEMPDB使用
5.1.5.1.采集方式
select obj.objectid,sum(er_objects_alloc_page_count)as user_objects_alloc_page_count, sum(er_objects_dealloc_page_count)as user_objects_dealloc_page_count,
sum(tmp.internal_objects_alloc_page_count)as internal_objects_alloc_page_count,
sum(tmp.internal_objects_dealloc_page_count)as internal_objects_dealloc_page_count
from sys.dm_db_task_space_usage tmp
left outer join sys.dm_exec_requests req on tmp.session_id=req.session_id
outer apply sys.dm_exec_sql_text(req.sql_handle)as obj
where obj.objectid is not null
group by obj.objectid
5.1.5.2.指标说明
5.1.
6.系统当前资源高消耗sql
5.1.
6.1.采集方式
返回系统当前正运行的资源高消耗信息的sql：
select objectid ,total_cpu_time,total_exec_count,num_of_statment, total_logical_reads/total_exec_count as avg_logical_reads,
total_logical_writes/total_exec_count as avg_logical_writes,
total_physical_reads/total_exec_count as avg_physical_reads,text from(
select qs.sql_handle,sum(qs.total_worker_time)as total_cpu_time, cast(sum(qs.execution_count)as decimal(38,16))as total_exec_count, count(*)as num_of_statment,
sum(total_logical_reads)as total_logical_reads,
sum(total_logical_writes)as total_logical_writes,
sum(total_physical_reads)as total_physical_reads
from sys.dm_exec_query_stats qs
group by qs.sql_handle)dt
cross apply sys.dm_exec_sql_text(dt.sql_handle) eqp
where objectid is not null
5.1.
6.2.指标说明
5.1.7.当前阻塞和物理io多的会话请求信息
5.1.7.1.采集方式
以下语句返回系统中有阻塞和物理io多的会话，可以每15s采集一次
select
session_id,request_id,start_time,status,command,sql_handle,plan_handl e,
database_id,user_id,connection_id,blocking_session_id,wait_type,wait_ time,wait_resource,
open_transaction_count,open_resultset_count,transaction_id,cpu_time,t otal_elapsed_time,reads,writes,logical_reads,lock_timeout,granted_que ry_memory
from sys.dm_exec_requests
where(logical_reads<>0 and reads/logical_reads>=0.2)or blocking_session_id <>0
5.1.7.2.指标说明
5.1.8.查询计划多的sql语句
5.1.8.1.采集方式
以下语句采集查询计划多的前25个sql，可以每12小时采集一次，这些语句有如下缺点：1可能是没有使用绑定变量。

2可能是过于复杂
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
5.1.8.2.指标说明
5.1.9.索引使用情况
5.1.9.1.采集方式
以下语句采集指定数据库中所有索引使用情况，当user_scan远大于user_seeks说明需要查看数据库对象是否可以做性能调优，可以每12小时采集一次。

select object_name(i.object_id) as objectname,
,
er_updates,
er_seeks,
er_scans,
er_lookups,
case when
s.index_id is null or
(er_updates > 0 and er_seeks = 0
and er_scans =0 and er_lookups =0)then0else1end as user_flag from sys.indexes i
left join sys.dm_db_index_usage_stats s
on s.object_id= i.object_id and
i.index_id = s.index_id and s.database_id = [数据库id] where objectproperty(i.object_id,'IsIndexable')= 1
order by object_name(i.object_id)asc
5.1.9.2.指标说明
6.开发原则
6.1.数据类型
数据类型选择原则：
●选择能满足列需求的最小类型
●尽可能选择定长数据类型
●考虑large value的存放时，尽量考虑检索的必要性（数据在行外影响性能），在行内数
据量比较大
●如果使用计算列过多，可以将计算列的结果存储起来，缓解io压力。

6.2.存储架构
6.2.1.页
7.优化方法
7.1.统计信息
是否有统计信息，统计信息是否最新会影响性能。

7.2.绑定变量
有助于数据库引擎识别重用执行计划，提高执行计划查找。