SQLServer性能监控指标说明

-性能监控指标说明 -MSSQL SERVER 2005小艳2010年6月30日前言从MSSQL SERVER2005开场，微软开场了漫长的向ORACLE学习之旅，本文以oracle优化为根底，整理了一局部MSSQL SERVER2005监控指标，这些指标不能全部反映MSSQL SERVER2005的性能信息，因此还需要从更多的开发的角度后继完成优化过程，其中最重要的一环是设计。由于下一步的工作就是实际的优化，请大家指出文档中的缺乏和错误之处。本文整理用到的资料：清华大学的?SQL SERVER 2005性能调优?，微软技术资料库，MSDN。目录1.配置硬件21.1.存21.1.1.物理地址空间21.1.2.虚拟地址空间21.1.3.虚拟存管理器3存3331.1.7.选择/3GB 、/PAE、还是/AWE3系统存配置41.1.9.存预计大小441.2.1.网络IO41.2.2.磁盘IO41.2.3.存储设计41.2.4.配置分区51.2.5.扇区对齐51.2.6.卷的类型5分配空间的大小51.2.8.磁盘破碎661.4.32bit *86处理器61.4.1.多核61.4.2.超线程67或IA6471.4.5.高速缓存71.5.系统架构771.5.2.NUMA81.5.3.SOFT NUMA82.系统监控指标82.1.存82.1.1.外部压力82.1.2.部存压力82.1.3.确认存瓶颈92.2.磁盘92.2.1.基于配置的磁盘瓶颈102.2.2.基于模式的磁盘瓶颈102.2.3.指标10102.3.1.基于配置的cpu瓶颈112.3.2.基于模式的cpu瓶颈112.4.数据库镜像性能113.效劳器设置11114.数据库等待事件114.1.常见等待类型125.数据库指标125.1.指标采集和排列125.1.1.操作系统资源125.1.2.重要统计信息145.1.3.等待事件145.1.4.数据文件和日志文件15使用185.1.6.系统当前资源高消耗sql185.1.7.当前阻塞和物理io多的会话请求信息195.1.8.查询方案多的sql语句215.1.9.索引使用情况226.优化方法236.1.统计信息236.2.绑定变量236.3.提示237.错误231. 配置硬件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. /PAEIntel引入用来突破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物理存，如果存是瓶颈，使用3GB4GB物理存以上，使用/PAE和/AWE。尽量使用64bit。1.1.8. 64bit系统存配置*64和IA64之间有轻微不同的存布局，并提供不同大小的VAS。IA64提供7TB，*64提供8TB。Windows server 2003 sp1目前支持的最大物理存为2TB，对于sql server而言，不用担忧使用/PAE或者/3GB。他有足够的虚拟地址空间使用。1.1.9. 存预计大小min server memory 和 ma* server memory存估计大小=每个所有者 64 字节 + 32 字节*锁的数量+(3 * network_packet_size默认4KB + 94 KB)*会话数如果会话有多个活动结果集，则存使用量为(3 + 3 * num_logical_connections) * network_packet_size + 94 KBSql server启动时根据配置系统最大物理存数，系统最大线程数和其他参数设置缓冲区大小。Sys.dm_os_sys_info中记录当前占用操作系统资源的杂项信息。1.2. I/OI/O包含网络io和磁盘io。数据库对磁盘性能相当敏感。1.2.1. 网络IO设计时尽量减少返回给客户端的数据量。1.2.2. 磁盘IO1.2.3. 存储设计磁盘驱动器有两种：SCSI或者ATA。ATA驱动器是低端市场的桌面计算的选择。SCSI本钱高，价值表达在数据完整性，可伸缩性，可靠性和高占空比预期磁盘驱动器用法。高占空比意味着驱动器被设计为用于持续的7*24小时使用。串行ATA(SATA)和串行SCSISAS是现今最常见的新驱动器。串行代表磁盘驱动器的接口，根本上与并行产品一样，不同在于磁盘加了新的高速接口。直连式存储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快对齐和DISKPARTKB对齐，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. 安装os2. 整理磁盘碎片3. 安装所有应用程序4. 整理磁盘碎片5. 以最大大小创立数据文件和日志文件6. 停顿sql server,检查碎片，有需要整理磁盘碎片7. 禁用自增长，或者将增量设置大一些。8. 周期性进展磁盘碎片整理。1.3. CPU1.4. 32bit *86处理器以后会被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. 64bit64bit有一个潜在的缺点，就是缺少64bit驱动程序。有些应用还未开发出支持64bit的应用。1.4.4. *64或IA64*64处理器运行速度超过3Ghz，IA64在1.6Ghz左右。IA64处理器的高速缓存大于*64处理器的高速缓存。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.e*e大小为8.9m，SQL SERVER2005该文件大得多，为28m，增加的代码为高速缓存重用局部，包含查询方案重用，增加的存则是查询方案存储空间等。1.5. 系统架构主要是指机器架构，如单插槽，双插槽，四插槽，甚至对称多处理器SMP.1.5.1. SMP对称多处理器通过一个系统总线连接到一个共享存上。SMP加剧了处理器和存之间的速度问题。系统处理器有数十个的时候，系统总线会成为瓶颈。1.5.2. NUMA非一致性存访问NUMA是一种超越SMP的可伸缩性的架构，NUMA系统中有多个系统总线，每一个系统总线能连接的处理器最大为4，最多4个cpu共享一块存。以下语句确认系统是否使用NUMASelect 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语句获得：selecttr_value fromsys.dm_os_performance_counterswhere object_name=MSSQL$Z*Y:Buffer Managerand counter_name=total pages以第2点的Sql server:Buffer managertotal pages为例，其中蓝色字体代表实例名称，将红色字体替换为Buffer manager，粉色字体替换为total pages即可。需要注意的是复制时注意空格要和原来一样。2.1. 存2.1.1. 外部压力1、 Processworking set 显示每一个进程虚拟地址空间中最近引用的页面规模。消耗存最多的进程就是消耗大量存的应用。指一个进程的4GB虚拟地址空间中被映射到RAM中的局部的大小，通常是该进程的虚拟存中的活泼局部。2、 Sql serverSql server:Buffer managertotal pages 显示sql server已获得的页面数3、 Sql serverSql server:buffer managertarget pages 显示sql server的buffer pool必需的理想页面数。2.1.2. 部存压力部压力通常是收缩缓冲池导致的。缓冲池规模变小，通常是sp_configure的ma* server memory的值减小导致。或者是其他数据库对象消耗了存空间导致，如：作业，扩展存储过程，com对象，sqlclr，效劳器。通过dbcc memorystatus查看buffer counts局部，确定存是否存在压力。其中committed memory和target memory最重要。Committed memory表示sql server已经得到的存数量，target memory表示有效运行所需的存数量。当两个存在差异过大，说明可能存在存压力。2.1.3. 确认存瓶颈1、 SQLServer:Buffer ManagerPage Life E*pectancy 显示数据页在缓冲池中驻留的时间长度单位是秒。值越大，系统越安康。如果存在存瓶颈，这个值会低于300s或者更少。2、 SQLServer:Buffer ManagerBuffer cache hit ratio 显示数据库存命中率，所请求的数据或者说页面在缓冲池物理存被找到的次数。如果这个值很低，说明存缺乏。这个值至少大于98%。3、 缓冲池的大小由两个参数决定：sp_configure的min server memory和ma* server memory。当其他应用使用了这些存后，操作系统就不会再将这些存分给sql server.4、 SQLServer:Buffer ManagerStolen pages显示了存被其他进程挪用的页面。当这个指标与目标存页面数比例较大时可能存在问题。5、 SQLServer:Memory ManagerMemory Grants Pending显示等待存授权的进程队列。这个指标值为0时理想状态。6、 SQLServer:Buffer ManagerCheckpoint pages/sec显示检查点操作每秒写入磁盘的脏页数目。如果这个值很高，说明缺少存。7、 SQLServer:Buffer ManagerLazy writes/sec 显示每秒将脏页从小写到磁盘的次数。这个值应该尽可能接近0，当大于20或者更多，确信缓冲池不够。8、 Memtoleave大小基于工作者线程的数目动态变化，是一个独立的存地址围，供效劳器，扩展存储过程，com对象或第三方ddl使用。2.2. 磁盘1、 PhysicalDiskAVG.Disk Aueue Length显示每一个磁盘的队列长度。不要选择_TOTAL对象，而是查看*驱动盘符的队列长度，如果参数值持续大于2，则可能影响性能。2、 PhysicalDiskAVG.Disk sec/Read显示每次读取的平均磁盘时间。3、 PhysicalDiskAVG.Disk sec/Write显示每次写入的平均磁盘时间。4、 如果存在磁盘性能问题，控制器使用率等问题，上面两个计数器的平均值高于正常值。理想情况下值小于10毫秒。5、 SQL ServerAccess MethodsFull scans/sec显示每秒请求完全索引扫描或者全表扫描的数目，如果扫描频率每秒大于1，则说明索引缺少或者索引比拟差。6、 SQL ServerAccess MethodsPage Splits/sec显示每秒页面拆分的次数。可以通过适当的索引维护或者好的填充因子来防止发生。2.2.1. 基于配置的磁盘瓶颈1、 数据文件和日志文件布局将这两类文件隔离，并且将每一类文件也隔离2、 数据文件和日志文件自动增长管理手动管理3、 Tempdb配置指定初始化大小4、 Tempdb部争用5、 磁盘/控制器争用用存储厂商的性能监控工具2.2.2. 基于模式的磁盘瓶颈1、 索引不当或者缺少索引，使用数据库优化参谋database tuning advisor来解决。或者查看sys.dm_db_inde*_usage_stats。2、 文件和文件组，将读取数据量大且频繁的表单独放到文件或者组合到文件组中，可以适当降低io并便于移植以提高性能。3、 分区，降低io4、 索引/表碎片，当索引和表的碎片较多时会产生页拆分类似于oracle的行和行迁移。产生的原意有两种：部和外部。部碎片是指8kb数据页包含数据很少。外部碎片是指数据不是顺序存放的。在dm_db_inde*_physical_stats提供当前碎片级别的所有信息；只有有聚簇索引的表才能重建索引；使用alter inde*命令重建索引。2.2.3. 指标1、 SQL ServerGeneral StatisticsTemp Tables Creation Rate显示每秒创立的临时表和临时变量的数目。2、 SQL ServerGeneral StatisticsTemp Tables For Destruction显示不再需要的正在等到销毁的临时表的数目。3、 Sys.dm_db_task_space_usage和sys.dm_e*ec_requests查看每一个效劳器进程使用的tempdb空间，具体见。2.2.4. 存储性能压力测试指标1. IOPS是每秒能够为输入输出请求提供效劳的度量。得到的子系统实际吞吐量和io请求的大小的函数。2. Block size提供sql执行的IO的大小的指示3. MB/sec每秒有多少MB的数据进入或者离开子系统。4. Latency为每一个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. Cpu1、 SystemProcessor Queue Length显示系统队列长度，如果平均值大于3，则说明cpu存在瓶颈。2、 Processor%Privilege Time显示操作系统部操作所花费的时间。3、 Processor%User Time显示用户模式操作花费的时间4、 Processor%User Timesqlservr显示sql server进程消耗的cpu时间确实切数量。这个最好从性能监视器来看。5、 SQL ServerSQL StatisticsSQL Compilations/sec显示查询方案的编译次数。6、 SQL ServerSQL StatisticsSQL Re-Compilations/sec显示查询方案的重编译次数。7、 当SQL Compilations/sec和SQL Re-Compilations/sec次数过高的话，说明可能未使用绑定变量导致方案重新编译，或者说重新编译次数比编译次数过高的话，则说明存在应用上的瓶颈。2.3.1. 基于配置的cpu瓶颈很难有建议或者计数器来协助sp_configure的cpu相关配置Affinity Mask,Hyper Threading,Priority Boost,Ma*imum Degree of Parallelism,Ma* 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 aFrom sys.dm_os_schedulers where parent_node_id324. 数据库等待事件SQL ServerWait StatisticsLock Waits显示进程等待获得锁的时间。SQL ServerWait StatisticsMemory Grant Queue Waits显示进程在队列中等待存锁花费的时间。SQL ServerWait StatisticsPage I/O Latches4.1. 常见等待类型WAITFOR:是人工挑起的等待，不用关注。LAZYWRITER_SLEEP:表示lazywiter进程处于睡眠状态并等待运行的时间，不用关注。SQLTRACE_BUFFER_FLUSH：当系统等待sql trace缓冲区被写到磁盘时出现的等待。一般可以看做永远存在的特性。SLEEP_BPOOL_FLUSH:检查点等待时，会计数。RESOURCE_SEMAPHORE:所有hash，sort，批量复制以及索引创立等操作均要使用工作区。一般来说工作区大小为25%到70%之间。说明了存压力。C*PACKET：显示并行正在发生，在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进程虚拟地址空间可用存页数,ma*_workers_count as最大工作线程数-,process_kernel_time_ms as 核态毫秒数,process_user_time_ms as 用户态毫秒数,sqlserver_start_time as 启动时间fromsys.dm_os_sys_info5.1.1.2. 指标说明指标名称数据类型指标说明系统的逻辑CPU数int我感觉是是否使用超线程的标志逻辑核数与物理核数的比int物理存Int可用物理存的数量虚拟存int对用户模式进程可用的虚拟存的数量,可以用来判断是否使用/3GB开关。缓存池已提交存页数int此数量表示缓冲池中的已提交物理存页数，单位为8KB。不包括缓冲池中的保存存缓存池目标存页数int目标数量是使用诸如系统当前状态包括它的负载、当前进程所请求的存、计算机上安装的存量以及配置参数等多种输入计算得到的。如果 bpool_commit_target 大于 bpool_committed 值，则缓冲池将尝试获得额外存。如果 bpool_commit_target 小于 bpool_committed 值，则缓冲池将收缩。进程虚拟地址空间可用存页数int不使用地址窗口化扩展插件 (AWE) 时，如果缓冲池已获得它的存目标 (bpool_committed = bpool_commit_target)，则 bpool_visible 的值等于 bpool_committed 的值。在 32 位版本的 SQL Server 上使用 AWE 时，bpool_visible 表示在访问缓冲池所分配的物理存时使用的 AWE 映射窗口的大小。此映射窗口的大小由进程地址空间绑定，因此，可见数量将小于提交数量，并且通过为数据库页之外的其他用途而消耗存的部组件会进一步减少可见数量。如果 bpool_visible 的值太低，则可能收到存缺乏错误。最大工作线程数int5.1.2. 重要统计信息5.1.2.1. 采集方式下面语句获得高速缓冲区命中率的值，采集可以每30s采集一次selectround(cast(sum(casewhen counter_name=Buffer cache hit ratiothentr_value else 0 end)asdecimal(38,16)/sum(casewhen counter_name=Buffer cache hit ratio basethentr_value else 0 end)*100 ,2)as bfromsys.dm_os_performance_counterswhere counter_name likeBuffer cache hit ratio%下面的语句返回方案缓冲区不同池的命中率，可以每30s采集一次：select instance_name,round(cast(sum(casewhen counter_name=Cache Hit Ratiothentr_value else 0 end)asdecimal(38,16)/sum(casewhen counter_name=Cache Hit Ratio Basethentr_value else 0 end)*100 ,2)as bfromsys.dm_os_performance_counterswhereobject_name=MSSQL$Z*Y:Plan Cachegroupby instance_namehavingsum(casewhen counter_name=Cache Hit Ratio Basethentr_value else 0 end)0下面语句获得每秒索引扫描和全部扫描次数，可以每30s采集一次：selectsum(casewhen counter_name=Full Scans/secthentr_value else 0 end)as full_table_scans,sum(casewhen counter_name=Range Scans/secthentr_value else 0 end)as range_scansfromsys.dm_os_performance_counterswhere 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,ma*_wait_time_ms,signal_wait_time_msfromsys.dm_os_wait_statswhere waiting_tasks_count=1orderby wait_time_ms/waiting_tasks_count desc5.1.3.2. 指标说明指标名称数据类型指标说明wait_typenvarchar(60) 等待事件名称waiting_tasks_countbigint等待次数wait_time_msbigint该等待类型的总等待时间毫秒。该时间包括 signal_wait_time_ms。ma*_wait_time_msbigint最长等待时间毫秒signal_wait_time_msbigint该等待事件从收到信号到运行等待时间毫秒5.1.3.3. 等待事件说明参见第四章。5.1.4. 数据文件和日志文件5.1.4.1. 采集方式下面分别说明数据库空间，数据库空间使用等信息，采集周期可以是每天一次，或者每周一次。数据库空间：selectnameas数据库空间,data_space_id as空间编号,typeas空间类型,is_default as默认数据空间标志fromsys.data_spaces数据库空间使用信息：sp_helpdb返回信息如下：指标名称数据类型指标说明namesysname数据库名称。db_sizenvarchar(13)数据库总计大小。dbidsmallint数据库 ID。statusnvarchar(600)指定数据库详细空间使用信息：sp_helpdb数据库名称取返回的第二个结果集，具体信息如下：指标名称数据类型指标说明namenchar(128)逻辑文件名。fileidsmallint文件 ID。filenamenchar(260)操作系统文件名物理文件名称。filegroupnvarchar(128)文件所属的文件组。NULL = 文件为日志文件。它决不是文件组的一局部。sizenvarchar(18)文件大小 (MB)。ma*sizenvarchar(18)文件大小可到达的最大值。此字段中的 UNLIMITED 值表示文件可以一直增长到磁盘已满。growthnvarchar(18)文件的增量。表示每次需要新的空间时给文件增加的空间大小。usagevarchar(9)文件用法。对于数据文件，该值为 data only，对于日志文件，该值为 log only。所有数据库对应的数据文件和日志文件io信息：select dbid,eqp.fileid,NumberReads,BytesRead /1024/1024 as bytesread,IoStallReadMS,Numberwrites,Byteswritten /1024/1024 as bytewitten,IoStallwriteMS,IoStallMSfrom(select*from sysfiles) aa crossapply fn_virtualfilestats (null,aa.fileid) eqp所有数据库临时表空间文件空间分配信息：select b.nameas数据库,c.nameas文件的逻辑名称,c.filenameas文件物理名称,version_store_reserved_page_count as统一区总页数,unallocated_e*tent_page_count 未分配取总页数,user_object_reserved_page_count as统一区用户对象用户对象总页数,internal_object_reserved_page_countas统一区文件部用户对象总页数, mi*ed_e*tent_page_count as混合区总页数from sys.dm_db_file_space_usage a innerjoin sysdatabases b on a.database_id=b.dbidinnerjoin sysfiles c on a.file_id=c.fileid5.1.4.2. 指标说明数据库空间：指标名称数据类型指标说明数据库空间名称nvarchar空间编号int空间类型char(2)数据空间类型：FG = 文件组PS = 分区方案默认数据空间标志bit1 = 这是默认数据空间。当未在 CREATE TABLE 或 CREATE INDE* 语句中指定文件组或分区方案时，将使用此数据空间。0 = 这不是默认数据空间。所有数据库对应的数据文件和日志文件io信息：指标名称数据类型指标说明dbidsmallint数据库编号Fileidsmallint文件编号NumberReadsbigint文件读次数BytesReadbigint文件读字节数IoStallReadMSbigint用户等待文件的读取 I/O 完成所费的总时间以毫秒为单位。Numberwritesbigint文件写次数Byteswrittenbigint文件写字节数IoStallwriteMSbigint用户等待文件的写 I/O 完成所费的总时间以毫秒为单位。IoStallMSbigint等待文件读写完成总时间以毫秒为单位所有数据库临时表空间文件空间分配信息：指标名称数据类型指标说明数据库名称Varchar(20)文件的逻辑名称Varchar(20)文件物理名称Varchar(260)未分配区总页数bigint文件的未分配区中的总页数,不包含已分配区的未使用页统一区总页数bigint统一区总页数统一区用户对象用户对象总页数bigint统一区用户对象总页数包含已分配未使用页。统一区文件部用户对象总页数bigint统一区文件中部对象总页数，包含已分配未使用页。混合区总页数bigint混合区已分配和未分配总页数之和。5.1.5. TEMPDB使用5.1.5.1. 采集方式select obj.objectid,sum(tmp.user_objects_alloc_page_count)as user_objects_alloc_page_count,sum(tmp.user_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_countfromsys.dm_db_task_space_usage tmpleftouterjoinsys.dm_e*ec_requests req on tmp.session_id=req.session_idouterapply sys.dm_e*ec_sql_te*t(req.sql_handle)as objwhere obj.objectid isnotnullgroupby obj.objectid5.1.5.2. 指标说明指标名称数据类型指标说明objectidVarchar(260)对象编号user_objects_alloc_page_countbigint为用户对象保存或分配的页数user_objects_dealloc_page_countbigint为用户对象释放并不再保存的页数internal_objects_alloc_page_countbigint为部对象保存或分配的页数internal_objects_dealloc_page_countbigint为部对象释放并不再保存的页数5.1.6. 系统当前资源高消耗sql5.1.6.1. 采集方式返回系统当前正运行的资源高消耗信息的sql：select objectid ,total_cpu_time,total_e*ec_count,num_of_statment,total_logical_reads/total_e*ec_count as avg_logical_reads,total_logical_writes/total_e*ec_count as avg_logical_writes,total_physical_reads/total_e*ec_count as avg_physical_reads,te*tfrom(select qs.sql_handle,sum(qs.total_worker_time)as total_cpu_time,cast(sum(qs.e*ecution_count)asdecimal(38,16)as total_e*ec_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_readsfromsys.dm_e*ec_query_stats qsgroupby qs.sql_handle)dtcrossapply sys.dm_e*ec_sql_te*t(dt.sql_handle) eqpwhere objectid isnotnull5.1.6.2. 指标说明指标名称数据类型指标说明objectidInt数据库对象Total_cpu_timebigintCpu自编译以来使用时间总毫秒数total_e*ec_countdecimal(38,16)上次编译后执行次数num_of_statmentInt语句个数avg_logical_readsdecimal(38,16)平均逻辑读次数avg_logical_writesdecimal(38,16)平均逻辑写次数avg_physical_readsdecimal(38,16)平均物理读次数Te*tNvarchar(4000)Sql脚本5.1.7. 当前阻塞和物理io多的会话请求信息5.1.7.1. 采集方式以下语句返回系统中有阻塞和物理io多的会话，可以每15s采集一次select session_id,request_id,start_time,status,command,sql_handle,plan_handle,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,total_elapsed_time,reads,writes,logical_reads,lock_timeout,granted_query_memoryfromsys.dm_e*ec_requestswhere( logical_reads0 and reads/logical_reads=0.2)or blocking_session_id 05.1.7.2. 指标说明指标名称数据类型指标说明Session_idsmallint会话编号Request_idInt请求号Start_timedatetime请求到达的时间Statusnvarchar(30) 请求状态：1、 后台2、 正在运行3、 可运行4、 睡眠5、 已挂起Commandnvarchar(16) 正在处理的命令的当前类型。常用命令类型包括：1、 SELECT2、 INSERT3、 UPDATE4、 DELETE5、 BACKUP LOG6、 BACKUP DB7、 DBCC8、 WAITFORSql_handlevarbinary(64)请求的 SQL 文本的哈希映射Plan_handlevarbinary(64)用于执行 SQL 的方案的哈希映射Database_idsmallint对其执行请求的数据库的 IDUser_idInt提交请求的用户的 IDConnection_idint请求到达时所采用的连接的 IDBlocking_session_idint正在阻塞请求的会话的 ID。如果此列为 NULL，则表示请求未被阻塞，或锁定会话的会话信息不可用或无法进展标识。-2 = 阻塞资源由孤立的分布式事务拥有。-3 = 阻塞资源由延迟的恢复事务拥有。-4 = 由于部闩锁状态转换而导致此时