性能监控指标说明

1、性能监控指标说明 -MSSQL SERVER 2005赵小艳2010年6月30日前言从MSSQL SERVER2005开始，微软开始了漫长的向ORACLE学习之旅，本文以oracle优化为基础，整理了一部分MSSQL SERVER2005监控指标，这些指标不能全部反映MSSQL SERVER2005的性能信息，因此还需要从更多的开发的角度后继完成优化过程，其中最重要的一环是设计。由于下一步的工作就是实际的优化，请大家指出文档中的不足和错误之处。本文整理用到的资料：清华大学出版社的SQL SERVER 2005性能调优，微软技术资料库，MSDN。目录1.配置硬件21.1.内存21.1.1.物理地

2、址空间21.1.2.虚拟地址空间21.1.3.虚拟内存管理器GB内存31.1.5.PAE31.1.6.AWE31.1.7.选择/3GB 、/PAE、还是/AWE4bit系统内存配置41.1.9.内存预计大小41.2.I/O41.2.1.网络IO41.2.2.磁盘IO41.2.3.存储设计41.2.4.配置分区51.2.5.扇区对齐51.2.6.卷的类型51.2.7.NTFS分配空间的大小51.2.8.磁盘破碎61.3.CPU61.4.32bit x86处理器61.4.1.多核61.4.2.超线程4bit71.4.4.X64或IA6471.4.5.

3、高速缓存71.5.系统架构71.5.1.SMP71.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.指标102.3.Cpu102.3.1.基于配置的cpu瓶颈112.3.2.基于模式的cpu瓶颈112.4.数据库镜像性能113.服务器设置113.1.Cpu114.数据库等待事件114.1.常见等待类型125.数据库指标125.1.指标采集和排列125.1.1.操作系统资源125.1.2.重要统计

4、信息145.1.3.等待事件145.1.4.数据文件和日志文件155.1.5.TEMPDB使用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虚拟地址空间，

5、虚拟内存由内核和用户进程共享。内核和用户进程各使用2g。1.1.3. 虚拟内存管理器虚拟内存管理器（vmm）管理所有内存，并在系统中所有使用内存的进程间共享。虚拟内存管理器使每一个进程在需要内存时提供4gb的虚拟内存的错觉，事实上所有进程争夺有限的物理内存。操作系统的内存有限，为了让进程有使用4gb虚拟内存的错误，用交换空间来保证所有进程的虚拟内存空间的使用。如果系统有足够的物理内存，那么vmm只要分发内存并在进程结束时清理内存即可。如果没有足够的物理内存，vmm必需为每一个进程在需要时提供所需的内存。Vmm通过页面文件暂存进程有一段时间没有访问的数据来实现，这个过程叫分页。进程的数据经常被分

6、页或者交换到磁盘。1.1.4. 3GB内存增加32bit系统中进程内存数量的一种方法是将分配给内核的一部分空间拿来用作用户模式地址空间。/3GB选项将边界移动到3GB，每个进程额外获得1GB的vas。此时内核只有1gb虚拟内存，有可能会产生问题导致崩溃。/USERVA是windows server2003新增的功能，提供从内核中获取指定的不同地址空间数量的方式，使每个进程额外获得0gb到1gb的地址空间，与3GB方式相同，当然也会导致崩溃。1.1.5. /PAEIntel引入用来突破32bit地址总线的限制，将地址总线扩展为36bit。允许32bit最多访问64GB内存，要求是：企业版或者更高

7、版本的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 MEMO

8、RY的高级用户权限，将自动使用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系统内存

9、配置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

10、_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成本高，价值体现在数据完整性，可伸缩性，可靠性和高占空比（预期磁盘

11、驱动器用法）。高占空比意味着驱动器被设计为用于持续的7*24小时使用。串行ATA(SATA)和串行SCSI（SAS）是现今最常见的新驱动器。串行代表磁盘驱动器的接口，基本上与并行产品相同，不同在于磁盘内加了新的高速接口。直连式存储（DAS）是讲一个服务器直接连接到磁盘阵列上，提供最佳的性能，但是在企业级应用中的上百个服务器有不同的磁盘空间需求，但是受限于所能提供的磁盘规模，导致大量的空间浪费。存储区域网络（SAN）提供了一个公共存储池，给网络提供健壮动态的存储。使用主机总线适配器来进行数据传送，使用大量高速缓存。HBA队列长度是在SAN中能够并行请求的数目限制，默认在8到32之间，由于SQL

12、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字节。磁

13、道是位于磁盘一个圆周上的，由扇区组成的圆周轨迹。每磁道有63个扇区。块是扇区的逻辑称谓。由于不同存储供应商在定义磁道时的差异导致对齐错误，会出现写入跨越2个条带，从而影响性能。有两种方法进行扇区对齐。DISKPAR（快对齐）和DISKPART（KB对齐），windows 2003 sp1更合适的是DISKPART。1.2.6. 卷的类型卷是磁盘分区的逻辑抽象，分基本卷和动态卷。基本卷提供多年以来使用的简单高效的存储方式；动态卷在windows 2000引入，支持额外的特性，如跨区可扩展卷和软件驱动RAID。随着硬件的发展，动态磁盘被放弃了。1.2.7. NTFS分配空间的大小在windows中

14、格式化一个分区时，会指定NTFS分配单元的大小，微软实验室测试表明，对于数据文件和日志文件的分区，最佳单元大小是64KB。1.2.8. 磁盘破碎在SQL SERVER中破碎可能一两种形式发生：发生在数据陈旧并经历了大量的插入、更新、删除时；发生在创建文件时，文件系统没有足够的连续空间在单独的片段中创建文件，结果是导致一个文件分布在磁盘表明的多个文件片段中。当sql server文件一旦被创建后就不会变得更加破碎，如果创建时没有连续的空间，文件在多个片段中创建，如果此后进行了碎片整理，那么文件就不会呈碎片状，将来也不会。理想的场景是对sql server文件使用专用磁盘，并设置合适的大小，并禁用

15、自动增长。如果没有专用的驱动器用于数据库，而且构建工作从头开始，那么最好的方法是：1. 安装os2. 整理磁盘碎片3. 安装所有应用程序4. 整理磁盘碎片5. 以最大大小创建数据文件和日志文件6. 停止sql server,检查碎片，有需要整理磁盘碎片7. 禁用自增长，或者将增量设置大一些。8. 周期性进行磁盘碎片整理。1.3. CPU1.4. 32bit x86处理器以后会被64bit取代。1.4.1. 多核摩尔定律是处理器的能力每18个月就会翻番，现在有变为每10个月就翻番的趋势。多核是指同一个芯片上有个cpu。Ms的per-processor的许可证发给每个插槽，而不是每个内核。对用户来

16、说4个双核cpu同8个单核服务器性能相当，但是许可成本减半。1.4.2. 超线程线程是运行在cpu上的执行单元，cpu每次只能执行一个线程，它通过在线程间切换产生并发的错觉。超线程是intel处理器的技术，试图通过复制架构状态来提供两个逻辑cpu，完成同时使用处理器不同部件执行不同任务的工作。使用超线程提供理论上30%性能的好处，实践中，仅仅是1.1到1.15倍cpu的能力。如果工作负荷是从高速缓存中行紧致循环，那么超线程不会有好处。如果选择并行计划时，并行计划所作的事情是假定每个处理器在给定的时间内能够完成相同数量的工作这个前提下，将要做的工作分割并指派给可用的处理器上，在使用超线程时，任何

17、不是当前执行的线程将被停止，所以并行计划在超线程下得不到性能提高。在windows server2000上意识不到超线程，但是2003 sp1可以使用，随着处理器的更新换代，超线程越来越好。有机会通过超线程获得好处，但是由于测试成本以及应用程序的成本，一般来说客户会关闭超线程。1.4.3. 64bit64bit有一个潜在的缺点，就是缺少64bit驱动程序。有些应用还未开发出支持64bit的应用。1.4.4. X64或IA64X64处理器运行速度超过3Ghz，IA64在1.6Ghz左右。IA64处理器的高速缓存大于X64处理器的高速缓存。IA64越来越在专用机器上出现，包括高性能工作站，大型高可

18、伸缩性系统。1.4.5. 高速缓存高速缓存产生的原因在于处理器速度远远高于主存储器，存储器无法跟上处理器对内存的渴望。处理器设计者为了解决这一问题，增加了多层高速缓存。处理器分L1,L2,L3，离处理器越远，规模越大速度更慢。L1只保存地址查找、数据和指令，L2,L3保存内存的任意备份。Processor Cache被实现为透明的look-thru cache，芯片上有着控制功能用来管理填充高速缓存的过程，管理高速缓存条目。Sql server2000启动时占用29M大小内存，使用sqlcmd连接到数据库并执行一个简单的查询，内存增0.5m。SQL SERVER2005启动就有50m，同样的命

19、令，内存增加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系统中有多个系统总线，每一个系统总线

20、能连接的处理器最大为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语句获得：select cntr_value

21、 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 managertotal pages为例，其中蓝色字体代表实例名称，将红色字体替换为Buffer manager，粉色字体替换为total pages即可。需要注意的是复制时注意空格要和原来一样。2.1. 内存2.1.1. 外部压力1、 Processworking set 显示每一个进程虚拟地址空间中最近引用的页面规模。消耗

22、内存最多的进程就是消耗大量内存的应用。指一个进程的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的max server memory的值减小导致。或者

23、是其他数据库对象消耗了内存空间导致，如：作业，扩展存储过程，com对象，sqlclr，链接服务器。通过dbcc memorystatus查看buffer counts部分，确定内存是否存在压力。其中committed memory和target memory最重要。Committed memory表示sql server已经得到的内存数量，target memory表示有效运行所需的内存数量。当两个存在差别过大，说明可能存在内存压力。2.1.3. 确认内存瓶颈1、 SQLServer:Buffer ManagerPage Life Expectancy 显示数据页在缓冲池中驻留的时间长度（单位

24、是秒）。值越大，系统越健康。如果存在内存瓶颈，这个值会低于300s或者更少。2、 SQLServer:Buffer ManagerBuffer cache hit ratio 显示数据库内存命中率，所请求的数据或者说页面在缓冲池（物理内存）被找到的次数。如果这个值很低，说明内存不足。这个值至少大于98%。3、 缓冲池的大小由两个参数决定：sp_configure的min server memory和max server memory。当其他应用使用了这些内存后，操作系统就不会再将这些内存分给sql server.4、 SQLServer:Buffer ManagerStolen pages显示

25、了内存被其他进程挪用的页面。当这个指标与目标内存页面数比例较大时可能存在问题。5、 SQLServer:Buffer ManagerMemory Grants Pending显示等待内存授权的进程队列。这个指标值为0时理想状态。6、 SQLServer:Buffer ManagerCheckpoint pages/sec显示检查点操作每秒写入磁盘的脏页数目。如果这个值很高，说明缺少内存。7、 SQLServer:Buffer ManagerLazy writes/sec 显示每秒将脏页从小写到磁盘的次数。这个值应该尽可能接近0，当大于20或者更多，确信缓冲池不够。8、 Memtoleave大小

26、基于工作者线程的数目动态变化，是一个独立的内存地址范围，供链接服务器，扩展存储过程，com对象或第三方ddl使用。2.2. 磁盘1、 PhysicalDiskAVG.Disk Aueue Length显示每一个磁盘的队列长度。不要选择_TOTAL对象，而是查看某驱动盘符的队列长度，如果参数值持续大于2，则可能影响性能。2、 PhysicalDiskAVG.Disk sec/Read显示每次读取的平均磁盘时间。3、 PhysicalDiskAVG.Disk sec/Write显示每次写入的平均磁盘时间。4、 如果存在磁盘性能问题，控制器使用率等问题，上面两个计数器的平均值高于正常值。理想情况下值

27、小于10毫秒。5、 SQL ServerAccess MethodsFull scans/sec显示每秒请求完全索引扫描或者全表扫描的数目，如果扫描频率每秒大于1，那么说明索引缺少或者索引比较差。6、 SQL ServerAccess MethodsPage Splits/sec显示每秒页面拆分的次数。可以通过适当的索引维护或者好的填充因子来避免发生。2.2.1. 基于配置的磁盘瓶颈1、 数据文件和日志文件布局（将这两类文件隔离，并且将每一类文件也隔离）2、 数据文件和日志文件自动增长管理（手动管理）3、 Tempdb配置（指定初始化大小）4、 Tempdb内部争用5、 磁盘/控制器争用（用存

28、储厂商的性能监控工具）2.2.2. 基于模式的磁盘瓶颈1、 索引不当或者缺少索引，使用数据库优化顾问（database tuning advisor）来解决。或者查看sys.dm_db_index_usage_stats。2、 文件和文件组，将读取数据量大且频繁的表单独放到文件或者组合到文件组中，可以适当降低io并便于移植以提高性能。3、 分区，降低io4、 索引/表碎片，当索引和表的碎片较多时会产生页拆分（类似于oracle的行链接和行迁移）。产生的原意有两种：内部和外部。内部碎片是指8kb数据页包含数据很少。外部碎片是指数据不是顺序存放的。在dm_db_index_physical_sta

29、ts提供当前碎片级别的所有信息；只有有聚簇索引的表才能重建索引；使用alter index命令重建索引。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_exec_requests查看每一个服务器进程使用的tempdb空间，具体见5.1.5。2.2.

30、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说明采用周期内磁盘读取，踩入，使用和空闲各占的百分比。A

31、vg.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 By

32、tes/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 T

33、imesqlservr显示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瓶颈很难

34、有建议或者计数器来协助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建议不使用超

35、线程如果系统有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 W

36、aits显示进程在队列中等待内存锁花费的时间。SQL ServerWait StatisticsPage I/O Latches4.1. 常见等待类型WAITFOR:是人工挑起的等待，不用关注。LAZYWRITER_SLEEP:表示lazywiter进程处于睡眠状态并等待运行的时间，不用关注。SQLTRACE_BUFFER_FLUSH：当系统等待sql trace缓冲区被写到磁盘时出现的等待。一般可以看做永远存在的特性。SLEEP_BPOOL_FLUSH:检查点等待时，会计数。RESOURCE_SEMAPHORE:所有hash，sort，批量复制以及索引创建等操作均要使用工作区。一般来说工作区

37、大小为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绑定操作，说明了磁盘瓶颈。PAG

38、EIOLATCH_*显示将数据页从磁盘检索到内存的时间，说明了IO压力LCK_M_*等待获得某个资源的锁。LOGBUFFER：说明正在等待日志缓存写入事务日志文件，大的值说明日志文件磁盘io瓶颈。LOGMGR_RESERVE_APPEND：日志文件太小，添加日志文件。PAGELATCH_*说明在任务等待不处于 I/O请求中的缓冲区闩锁时发生，说明有连接同时想访问缓存中同一个数据页。 LATCH_*5. 数据库指标5.1. 指标采集和排列5.1.1. 操作系统资源. 采集方式下面的语句获得SQL Server 可用资源及其已占用资源的有用杂项信息的指标，可以每15分钟采集一回。se

39、lect 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_k

40、ernel_time_ms as 内核态毫秒数,process_user_time_ms as 用户态毫秒数,sqlserver_start_time as 启动时间 from sys.dm_os_sys_info. 指标说明指标名称数据类型指标说明系统的逻辑CPU数int我感觉是是否使用超线程的标志逻辑内核数与物理内核数的比int物理内存Int可用物理内存的数量虚拟内存int对用户模式进程可用的虚拟内存的数量,可以用来判断是否使用/3GB开关。缓存池已提交内存页数int此数量表示缓冲池中的已提交物理内存页数，单位为8KB。不包括缓冲池中的保留内存缓存池目标内存页数int目标数量

41、是使用诸如系统当前状态（包括它的负载、当前进程所请求的内存、计算机上安装的内存量以及配置参数）等多种输入计算得到的。如果 bpool_commit_target 大于 bpool_committed 值，则缓冲池将尝试获得额外内存。如果 bpool_commit_target 小于 bpool_committed 值，则缓冲池将收缩。进程虚拟地址空间可用内存页数int不使用地址窗口化扩展插件 (AWE) 时，如果缓冲池已获得它的内存目标 (bpool_committed = bpool_commit_target)，则 bpool_visible 的值等于 bpool_committed 的值

42、。在 32 位版本的 SQL Server 上使用 AWE 时，bpool_visible 表示在访问缓冲池所分配的物理内存时使用的 AWE 映射窗口的大小。此映射窗口的大小由进程地址空间绑定，因此，可见数量将小于提交数量，并且通过为数据库页之外的其他用途而消耗内存的内部组件会进一步减少可见数量。如果 bpool_visible 的值太低，则可能收到内存不足错误。最大工作线程数int5.1.2. 重要统计信息. 采集方式下面语句获得高速缓冲区命中率的值，采集可以每30s采集一次select round(cast(sum(case when counter_name=Buffer

43、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

44、(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 Cachegroup by instance_namehaving sum(case wh

45、en 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 else 0 end ) as range_scans from sys.dm_os_perf

46、ormance_counters where counter_name in (Full Scans/sec,Range Scans/sec). 指标说明5.1.3. 等待事件按照平均等待事件来说明数据库等待事件. 采集方式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=1order by wait_time_ms/waiting_task

47、s_count desc. 指标说明指标名称数据类型指标说明wait_typenvarchar(60) 等待事件名称waiting_tasks_countbigint等待次数wait_time_msbigint该等待类型的总等待时间（毫秒）。该时间包括 signal_wait_time_ms。max_wait_time_msbigint最长等待时间（毫秒）signal_wait_time_msbigint该等待事件从收到信号到运行等待时间（毫秒）. 等待事件说明参见第四章。5.1.4. 数据文件和日志文件. 采集方式下面分别说明数据库空间，数据库空间使

48、用等信息，采集周期可以是每天一次，或者每周一次。数据库空间：select name as 数据库空间,data_space_id as 空间编号,type as 空间类型,is_default as 默认数据空间标志from sys.data_spaces数据库空间使用信息：sp_helpdb返回信息如下：指标名称数据类型指标说明namesysname数据库名称。db_sizenvarchar(13)数据库总计大小。dbidsmallint数据库 ID。statusnvarchar(600)指定数据库详细空间使用信息：sp_helpdb 数据库名称取返回的第二个结果集，具体信息如下：指标名称数

49、据类型指标说明namenchar(128)逻辑文件名。fileidsmallint文件 ID。filenamenchar(260)操作系统文件名（物理文件名称）。filegroupnvarchar(128)文件所属的文件组。NULL = 文件为日志文件。它决不是文件组的一部分。sizenvarchar(18)文件大小 (MB)。maxsizenvarchar(18)文件大小可达到的最大值。此字段中的 UNLIMITED 值表示文件可以一直增长到磁盘已满。growthnvarchar(18)文件的增量。表示每次需要新的空间时给文件增加的空间大小。usagevarchar(9)文件用法。对于数据文

50、件，该值为 data only，对于日志文件，该值为 log only。所有数据库对应的数据文件和日志文件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) e

51、qp所有数据库临时表空间文件空间分配信息：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_countas 统一区文件内部用户对象总页数, mixed_extent_page_count as 混合区总页数

52、from sys.dm_db_file_space_usage a inner join sysdatabases b on a.database_id=b.dbidinner join sysfiles c on a.file_id=c.fileid. 指标说明数据库空间：指标名称数据类型指标说明数据库空间名称nvarchar空间编号int空间类型char(2)数据空间类型：FG = 文件组PS = 分区方案默认数据空间标志bit1 = 这是默认数据空间。当未在 CREATE TABLE 或 CREATE INDEX 语句中指定文件组或分区方案时，将使用此数据空间。0 = 这不

53、是默认数据空间。所有数据库对应的数据文件和日志文件io信息：指标名称数据类型指标说明dbidsmallint数据库编号Fileidsmallint文件编号NumberReadsbigint文件读次数BytesReadbigint文件读字节数IoStallReadMSbigint用户等待文件的读取 I/O 完成所费的总时间（以毫秒为单位）。Numberwritesbigint文件写次数Byteswrittenbigint文件写字节数IoStallwriteMSbigint用户等待文件的写 I/O 完成所费的总时间（以毫秒为单位）。IoStallMSbigint等待文件读写完成总时间（以毫秒为单位

54、）所有数据库临时表空间文件空间分配信息：指标名称数据类型指标说明数据库名称Varchar(20)文件的逻辑名称Varchar(20)文件物理名称Varchar(260)未分配区总页数bigint文件的未分配区中的总页数,不包含已分配区的未使用页统一区总页数bigint统一区总页数统一区用户对象用户对象总页数bigint统一区用户对象总页数包含已分配未使用页。统一区文件内部用户对象总页数bigint统一区文件中内部对象总页数，包含已分配未使用页。混合区总页数bigint混合区已分配和未分配总页数之和。5.1.5. TEMPDB使用. 采集方式select obj.objectid,

55、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(ernal_objects_alloc_page_count) as internal_objects_alloc_page_count,sum(ernal_objects_dealloc_page_count) as internal_objects_dealloc_page_countfrom sys.dm_db_task_space_usage tmpleft outer join sys.dm_exec_requests req on tmp.sessio