SQLServer性能监控指标说明书.docx

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

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

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

4、然也会导致崩溃。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

5、联合使用。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物理存，均不适用。4

6、GB物理存，如果存是瓶颈，使用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 server memory存估计大小=每个所有者 64 字节 + 32 字节*锁的数量+(3 * network_packet_s

7、ize默认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驱动器是低端市场

8、的桌面计算的选择。SCSI本钱高，价值表现在数据完整性，可伸缩性，可靠性和高占空比预期磁盘驱动器用法。高占空比意味着驱动器被设计为用于持续的7*24小时使用。串行ATA(SATA)和串行SCSISAS是现今最常见的新驱动器。串行代表磁盘驱动器的接口，根本上与并行产品一样，不同在于磁盘加了新的高速接口。直连式存储DAS是讲一个服务器直接连接到磁盘阵列上，提供最优的性能，但是在企业级应用中的上百个服务器有不同的磁盘空间需求，但是受限于所能提供的磁盘规模，导致大量的空间浪费。存储区域网络SAN提供了一个公共存储池，给网络提供健壮动态的存储。使用主机总线适配器来进展数据传送，使用大量高速缓存。HBA队

9、列长度是在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是大势

10、所趋。1.2.5.扇区对齐扇区是磁盘最小存储空间，通常为512字节。磁道是位于磁盘一个圆周上的，由扇区组成的圆周轨迹。每磁道有63个扇区。块是扇区的逻辑称谓。由于不同存储供给商在定义磁道时的差异导致对齐错误，会出现写入跨越2个条带，从而影响性能。有两种方法进展扇区对齐。DISKPAR快对齐和DISKPARTKB对齐，windows 2003 sp1更适宜的是DISKPART。1.2.6.卷的类型卷是磁盘分区的逻辑抽象，分根本卷和动态卷。根本卷提供多年以来使用的简单高效的存储方式；动态卷在windows 2000引入，支持额外的特性，如跨区可扩展卷和软件驱动RAID。随着硬件的开展，动态磁盘被放

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

12、server文件使用专用磁盘，并设置适宜的大小，并禁用自动增长。如果没有专用的驱动器用于数据库，而且构建工作从头开始，那么最好的方法是：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的许可证发给每

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

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

15、高性能工作站，大型高可伸缩性系统。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非一致性存访