SQLServer性能监控指标说明.docx

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

2、.1. 内存1.1.1. 物理地址空间物理地址是处理器用来访问位于其总线上的所有部件的地址集合。PAE地址总线是36bit。64bit服务器生产商将地址总线限制在44bit。1.1.2. 虚拟地址空间在32bit系统中，进程有4gb虚拟地址空间，虚拟内存由内核和用户进程共享。内核和用户进程各使用2g。1.1.3. 虚拟内存管理器虚拟内存管理器（vmm）管理所有内存，并在系统中所有使用内存的进程间共享。虚拟内存管理器使每一个进程在需要内存时提供4gb的虚拟内存的错觉，事实上所有进程争夺有限的物理内存。操作系统的内存有限，为了让进程有使用4gb虚拟内存的错误，用交换空间来保证所有进程的虚拟内存空间

3、的使用。如果系统有足够的物理内存，那么vmm只要分发内存并在进程结束时清理内存即可。如果没有足够的物理内存，vmm必需为每一个进程在需要时提供所需的内存。Vmm通过页面文件暂存进程有一段时间没有访问的数据来实现，这个过程叫分页。进程的数据经常被分页或者交换到磁盘。1.1.4. 3GB内存增加32bit系统中进程内存数量的一种方法是将分配给内核的一部分空间拿来用作用户模式地址空间。/3GB选项将边界移动到3GB，每个进程额外获得1GB的vas。此时内核只有1gb虚拟内存，有可能会产生问题导致崩溃。/USERVA是windows server2003新增的功能，提供从内核中获取指定的不同地址空间数

4、量的方式，使每个进程额外获得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是内

5、存到内存的传递，因此速度更快。允许进程使用多于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不减少非分页池

6、的大小，却将每次分配的大小从4字节倍增到8字节。联合使用对系统进行了两次扼杀。少于4GB物理内存，均不适用。4GB物理内存，如果内存是瓶颈，使用3GB4GB物理内存以上，使用/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 s

7、erver 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.

8、1. 网络IO设计时尽量减少返回给客户端的数据量。1.2.2. 磁盘IO1.2.3. 存储设计磁盘驱动器有两种：SCSI或者ATA。ATA驱动器是低端市场的桌面计算的选择。SCSI成本高，价值体现在数据完整性，可伸缩性，可靠性和高占空比（预期磁盘驱动器用法）。高占空比意味着驱动器被设计为用于持续的7*24小时使用。串行ATA(SATA)和串行SCSI（SAS）是现今最常见的新驱动器。串行代表磁盘驱动器的接口，基本上与并行产品相同，不同在于磁盘内加了新的高速接口。直连式存储（DAS）是讲一个服务器直接连接到磁盘阵列上，提供最佳的性能，但是在企业级应用中的上百个服务器有不同的磁盘空间需求，但是受限

9、于所能提供的磁盘规模，导致大量的空间浪费。存储区域网络（SAN）提供了一个公共存储池，给网络提供健壮动态的存储。使用主机总线适配器来进行数据传送，使用大量高速缓存。HBA队列长度是在SAN中能够并行请求的数目限制，默认在8到32之间，由于SQL SERVER的io是密集型，因此该值应当设置为64或者更大。如果没有使用SAN，可以将设备管理器队列长度设置为64到128。HBA驱动器有两种：SCSIPort和StorPort。StorPort比SCSIPort新。数据文件布置一般建议将数据文件、日志文件和tempdb存放在不同的物理轴上。1.2.4. 配置分区磁盘安装后需要配置分区，目前有两种：M

10、BR和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. 卷的类型卷是磁盘分区的逻

11、辑抽象，分基本卷和动态卷。基本卷提供多年以来使用的简单高效的存储方式；动态卷在windows 2000引入，支持额外的特性，如跨区可扩展卷和软件驱动RAID。随着硬件的发展，动态磁盘被放弃了。1.2.7. NTFS分配空间的大小在windows中格式化一个分区时，会指定NTFS分配单元的大小，微软实验室测试表明，对于数据文件和日志文件的分区，最佳单元大小是64KB。1.2.8. 磁盘破碎在SQL SERVER中破碎可能一两种形式发生：发生在数据陈旧并经历了大量的插入、更新、删除时；发生在创建文件时，文件系统没有足够的连续空间在单独的片段中创建文件，结果是导致一个文件分布在磁盘表明的多个文件片段

12、中。当sql server文件一旦被创建后就不会变得更加破碎，如果创建时没有连续的空间，文件在多个片段中创建，如果此后进行了碎片整理，那么文件就不会呈碎片状，将来也不会。理想的场景是对sql server文件使用专用磁盘，并设置合适的大小，并禁用自动增长。如果没有专用的驱动器用于数据库，而且构建工作从头开始，那么最好的方法是：1. 安装os2. 整理磁盘碎片3. 安装所有应用程序4. 整理磁盘碎片5. 以最大大小创建数据文件和日志文件6. 停止sql server,检查碎片，有需要整理磁盘碎片7. 禁用自增长，或者将增量设置大一些。8. 周期性进行磁盘碎片整理。1.3. CPU1.4. 32b

13、it x86处理器以后会被64bit取代。1.4.1. 多核摩尔定律是处理器的能力每18个月就会翻番，现在有变为每10个月就翻番的趋势。多核是指同一个芯片上有个cpu。Ms的per-processor的许可证发给每个插槽，而不是每个内核。对用户来说4个双核cpu同8个单核服务器性能相当，但是许可成本减半。1.4.2. 超线程线程是运行在cpu上的执行单元，cpu每次只能执行一个线程，它通过在线程间切换产生并发的错觉。超线程是intel处理器的技术，试图通过复制架构状态来提供两个逻辑cpu，完成同时使用处理器不同部件执行不同任务的工作。使用超线程提供理论上30%性能的好处，实践中，仅仅是1.1到

14、1.15倍cpu的能力。如果工作负荷是从高速缓存中行紧致循环，那么超线程不会有好处。如果选择并行计划时，并行计划所作的事情是假定每个处理器在给定的时间内能够完成相同数量的工作这个前提下，将要做的工作分割并指派给可用的处理器上，在使用超线程时，任何不是当前执行的线程将被停止，所以并行计划在超线程下得不到性能提高。在windows server2000上意识不到超线程，但是2003 sp1可以使用，随着处理器的更新换代，超线程越来越好。有机会通过超线程获得好处，但是由于测试成本以及应用程序的成本，一般来说客户会关闭超线程。1.4.3. 64bit64bit有一个潜在的缺点，就是缺少64bit驱动程

15、序。有些应用还未开发出支持64bit的应用。1.4.4. X64或IA64X64处理器运行速度超过3Ghz，IA64在1.6Ghz左右。IA64处理器的高速缓存大于X64处理器的高速缓存。IA64越来越在专用机器上出现，包括高性能工作站，大型高可伸缩性系统。1.4.5. 高速缓存高速缓存产生的原因在于处理器速度远远高于主存储器，存储器无法跟上处理器对内存的渴望。处理器设计者为了解决这一问题，增加了多层高速缓存。处理器分L1,L2,L3，离处理器越远，规模越大速度更慢。L1只保存地址查找、数据和指令，L2,L3保存内存的任意备份。Processor Cache被实现为透明的look-thru c

16、ache，芯片上有着控制功能用来管理填充高速缓存的过程，管理高速缓存条目。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对称多处理器通过一

17、个系统总线连接到一个共享内存上。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聚合成

18、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 managertotal pages为例，其中蓝色字体代表实例名称，将红色字体替换

19、为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 managerta

20、rget 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已经得到的内存数量，targe

21、t memory表示有效运行所需的内存数量。当两个存在差别过大，说明可能存在内存压力。2.1.3. 确认内存瓶颈1、 SQLServer:Buffer Manager Page Life Expectancy 显示数据页在缓冲池中驻留的时间长度（单位是秒）。值越大，系统越健康。如果存在内存瓶颈，这个值会低于300s或者更少。2、 SQLServer:Buffer Manager Buffer cache hit ratio 显示数据库内存命中率，所请求的数据或者说页面在缓冲池（物理内存）被找到的次数。如果这个值很低，说明内存不足。这个值至少大于98%。3、 缓冲池的大小由两个参数决定：sp_c

22、onfigure的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显示检查点操作每秒写

23、入磁盘的脏页数目。如果这个值很高，说明缺少内存。7、 SQLServer:Buffer Manager Lazy writes/sec 显示每秒将脏页从小写到磁盘的次数。这个值应该尽可能接近0，当大于20或者更多，确信缓冲池不够。8、 Memtoleave大小基于工作者线程的数目动态变化，是一个独立的内存地址范围，供链接服务器，扩展存储过程，com对象或第三方ddl使用。2.2. 磁盘1、 PhysicalDiskAVG.Disk Aueue Length显示每一个磁盘的队列长度。不要选择_TOTAL对象，而是查看某驱动盘符的队列长度，如果参数值持续大于2，则可能影响性能。2、 Physica

24、lDiskAVG.Disk sec/Read显示每次读取的平均磁盘时间。3、 PhysicalDiskAVG.Disk sec/Write显示每次写入的平均磁盘时间。4、 如果存在磁盘性能问题，控制器使用率等问题，上面两个计数器的平均值高于正常值。理想情况下值小于10毫秒。5、 SQL ServerAccess MethodsFull scans/sec显示每秒请求完全索引扫描或者全表扫描的数目，如果扫描频率每秒大于1，那么说明索引缺少或者索引比较差。6、 SQL ServerAccess MethodsPage Splits/sec显示每秒页面拆分的次数。可以通过适当的索引维护或者好的填充因

25、子来避免发生。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、

26、 分区，降低io4、 索引/表碎片，当索引和表的碎片较多时会产生页拆分（类似于oracle的行链接和行迁移）。产生的原意有两种：内部和外部。内部碎片是指8kb数据页包含数据很少。外部碎片是指数据不是顺序存放的。在dm_db_index_physical_stats提供当前碎片级别的所有信息；只有有聚簇索引的表才能重建索引；使用alter index命令重建索引。2.2.3. 指标1、 SQL ServerGeneral StatisticsTemp Tables Creation Rate显示每秒创建的临时表和临时变量的数目。2、 SQL ServerGeneral StatisticsTem

27、p 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的数据进入或者离开子系统。4. Latency为每一个io完成所必须等待的时间长度。5. DISK Queue length

28、磁盘队列的长度，说明当前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 l

29、ength说明了平均磁盘队列长度，磁盘队列长度是等待被送到磁盘的请求的数目。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 Leng

30、th显示系统队列长度，如果平均值大于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显示查询计划的重编

31、译次数。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 enabl