SQLServer性能监控指标说明.docx
《SQLServer性能监控指标说明.docx》由会员分享，可在线阅读，更多相关《SQLServer性能监控指标说明.docx（29页珍藏版）》请在冰豆网上搜索。
SQLServer性能监控指标说明
性能监控指标说明
--MSSQLSERVER2005
赵小艳
2010年6月30日
前言
从MSSQLSERVER2005开始，微软开始了漫长的向ORACLE学习之旅，本文以oracle优化为基础，整理了一部分MSSQLSERVER2005监控指标，这些指标不能全部反映MSSQLSERVER2005的性能信息，因此还需要从更多的开发的角度后继完成优化过程，其中最重要的一环是设计。
由于下一步的工作就是实际的优化，请大家指出文档中的不足和错误之处。
本文整理用到的资料：
清华大学出版社的《SQLSERVER2005性能调优》，微软技术资料库，MSDN。
1.配置硬件2
.内存2
物理地址空间2
虚拟地址空间2
虚拟内存管理器3
3GB内存3
PAE3
AWE3
选择/3GB、/PAE、还是/AWE3
64bit系统内存配置4
内存预计大小4
.I/O4
网络IO4
磁盘IO4
存储设计4
配置分区5
扇区对齐5
卷的类型5
NTFS分配空间的大小5
磁盘破碎6
.CPU6
.32bitx86处理器6
多核6
超线程6
64bit7
X64或IA647
高速缓存7
.系统架构7
SMP7
NUMA8
SOFTNUMA8
2.系统监控指标8
.内存8
外部压力8
内部内存压力8
确认内存瓶颈9
.磁盘9
基于配置的磁盘瓶颈10
基于模式的磁盘瓶颈10
指标10
.Cpu10
基于配置的cpu瓶颈11
基于模式的cpu瓶颈11
.数据库镜像性能11
3.服务器设置11
.Cpu11
4.数据库等待事件11
.常见等待类型12
5.数据库指标12
.指标采集和排列12
操作系统资源12
重要统计信息14
等待事件14
数据文件和日志文件15
TEMPDB使用18
系统当前资源高消耗sql18
当前阻塞和物理io多的会话请求信息19
查询计划多的sql语句21
索引使用情况22
6.优化方法23
.统计信息23
.绑定变量23
.提示23
7.错误23
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是windowsserver2003新增的功能，提供从内核中获取指定的不同地址空间数量的方式，使每个进程额外获得0gb到1gb的地址空间，与3GB方式相同，当然也会导致崩溃。
1.1.5./PAE
Intel引入用来突破32bit地址总线的限制，将地址总线扩展为36bit。
允许32bit最多访问64GB内存，要求是：
企业版或者更高版本的windows服务器，必需运行在windows2003sp1之上的版本。
这个配置也是在中。
也可以在datacenter版本的os下，也会使用PAE。
1.1.6.AWE
地址窗口扩展（AWE）是windows的api，允许32bit的进程映射虚拟地址空间以外的内存。
进程可以使用物理内存存储数据，但是必须映射到vas中。
AWE是内存到内存的传递，因此速度更快。
允许进程使用多于2gb的内存，但是仍然局限于4GB。
因此建议和PAE联合使用。
MS建议在64bit上也使用AWE。
当然在64bit的sqlserver上无法激活AWE，如果账号有LOCKPAGESINMEMORY的高级用户权限，将自动使用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。
Windowsserver2003sp1目前支持的最大物理内存为2TB，对于sqlserver而言，不用担心使用/PAE或者/3GB。
他有足够的虚拟地址空间使用。
1.1.9.内存预计大小
minservermemory和maxservermemory
内存估计大小=（每个所有者64字节+32字节）*锁的数量+（3*network_packet_size（默认4KB）+94KB）*会话数
如果会话有多个活动结果集，那么内存使用量为（3+3*num_logical_connections）*network_packet_size+94KB
Sqlserver启动时根据配置（系统最大物理内存数，系统最大线程数和其他参数）设置缓冲区大小。
中记录当前占用操作系统资源的杂项信息。
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之间，由于SQLSERVER的io是密集型，因此该值应当设置为64或者更大。
如果没有使用SAN，可以将设备管理器队列长度设置为64到128。
HBA驱动器有两种：
SCSIPort和StorPort。
StorPort比SCSIPort新。
数据文件布置一般建议将数据文件、日志文件和tempdb存放在不同的物理轴上。
1.2.4.配置分区
磁盘安装后需要配置分区，目前有两种：
MBR和GPT。
现在只有windowsserver2003sp1以上才支持GPT，并且在failoverclustering中不支持，目前仍使用MBR，但是GPT是大势所趋。
1.2.5.扇区对齐
扇区是磁盘最小存储空间，通常为512字节。
磁道是位于磁盘一个圆周上的，由扇区组成的圆周轨迹。
每磁道有63个扇区。
块是扇区的逻辑称谓。
由于不同存储供应商在定义磁道时的差异导致对齐错误，会出现写入跨越2个条带，从而影响性能。
有两种方法进行扇区对齐。
DISKPAR（快对齐）和DISKPART（KB对齐），windows2003sp1更合适的是DISKPART。
1.2.6.卷的类型
卷是磁盘分区的逻辑抽象，分基本卷和动态卷。
基本卷提供多年以来使用的简单高效的存储方式；动态卷在windows2000引入，支持额外的特性，如跨区可扩展卷和软件驱动RAID。
随着硬件的发展，动态磁盘被放弃了。
1.2.7.NTFS分配空间的大小
在windows中格式化一个分区时，会指定NTFS分配单元的大小，微软实验室测试表明，对于数据文件和日志文件的分区，最佳单元大小是64KB。
1.2.8.磁盘破碎
在SQLSERVER中破碎可能一两种形式发生：
发生在数据陈旧并经历了大量的插入、更新、删除时；发生在创建文件时，文件系统没有足够的连续空间在单独的片段中创建文件，结果是导致一个文件分布在磁盘表明的多个文件片段中。
当sqlserver文件一旦被创建后就不会变得更加破碎，如果创建时没有连续的空间，文件在多个片段中创建，如果此后进行了碎片整理，那么文件就不会呈碎片状，将来也不会。
理想的场景是对sqlserver文件使用专用磁盘，并设置合适的大小，并禁用自动增长。
如果没有专用的驱动器用于数据库，而且构建工作从头开始，那么最好的方法是：
1.安装os
2.整理磁盘碎片
3.安装所有应用程序
4.整理磁盘碎片
5.以最大大小创建数据文件和日志文件
6.停止sqlserver,检查碎片，有需要整理磁盘碎片
7.禁用自增长，或者将增量设置大一些。
8.周期性进行磁盘碎片整理。
1.3.CPU
1.4.32bitx86处理器
以后会被64bit取代。
1.4.1.多核
摩尔定律是处理器的能力每18个月就会翻番，现在有变为每10个月就翻番的趋势。
多核是指同一个芯片上有个cpu。
Ms的per-processor的许可证发给每个插槽，而不是每个内核。
对用户来说4个双核cpu同8个单核服务器性能相当，但是许可成本减半。
1.4.2.超线程
线程是运行在cpu上的执行单元，cpu每次只能执行一个线程，它通过在线程间切换产生并发的错觉。
超线程是intel处理器的技术，试图通过复制架构状态来提供两个逻辑cpu，完成同时使用处理器不同部件执行不同任务的工作。
使用超线程提供理论上30%性能的好处，实践中，仅仅是到倍cpu的能力。
如果工作负荷是从高速缓存中行紧致循环，那么超线程不会有好处。
如果选择并行计划时，并行计划所作的事情是假定每个处理器在给定的时间内能够完成相同数量的工作这个前提下，将要做的工作分割并指派给可用的处理器上，在使用超线程时，任何不是当前执行的线程将被停止，所以并行计划在超线程下得不到性能提高。
在windowsserver2000上意识不到超线程，但是2003sp1可以使用，随着处理器的更新换代，超线程越来越好。
有机会通过超线程获得好处，但是由于测试成本以及应用程序的成本，一般来说客户会关闭超线程。
1.4.3.64bit
64bit有一个潜在的缺点，就是缺少64bit驱动程序。
有些应用还未开发出支持64bit的应用。
1.4.4.X64或IA64
X64处理器运行速度超过3Ghz，IA64在左右。
IA64处理器的高速缓存大于X64处理器的高速缓存。
IA64越来越在专用机器上出现，包括高性能工作站，大型高可伸缩性系统。
1.4.5.高速缓存
高速缓存产生的原因在于处理器速度远远高于主存储器，存储器无法跟上处理器对内存的渴望。
处理器设计者为了解决这一问题，增加了多层高速缓存。
处理器分L1,L2,L3，离处理器越远，规模越大速度更慢。
L1只保存地址查找、数据和指令，L2,L3保存内存的任意备份。
ProcessorCache被实现为透明的look-thrucache，芯片上有着控制功能用来管理填充高速缓存的过程，管理高速缓存条目。
Sqlserver2000启动时占用29M大小内存，使用sqlcmd连接到数据库并执行一个简单的查询，内存增。
SQLSERVER2005启动就有50m，同样的命令，内存增加3m左右。
Sqlserver2000的大小为，SQLSERVER2005该文件大得多，为28m，增加的代码为高速缓存重用部分，包含查询计划重用，增加的内存则是查询计划存储空间等。
1.5.系统架构
主要是指机器架构，如单插槽，双插槽，四插槽，甚至对称多处理器（SMP）.
1.5.1.SMP
对称多处理器通过一个系统总线连接到一个共享内存上。
SMP加剧了处理器和内存之间的速度问题。
系统处理器有数十个的时候，系统总线会成为瓶颈。
1.5.2.NUMA
非一致性内存访问（NUMA）是一种超越SMP的可伸缩性的架构，NUMA系统中有多个系统总线，每一个系统总线能连接的处理器最大为4，最多4个cpu共享一块内存。
以下语句确认系统是否使用NUMA
Selectdistinctmemory_node_idfrom
返回0说明未使用NUMA。
AMD处理器都作为NUMA实现的。
1.5.3.SOFTNUMA
这是SQLSERVER2005的新特性，允许将cpu聚合成soft-NUMA节点，如果没有硬件NUMA,但是cpu个数很多，可以提高性能。
建议使用SOFTNUMA来对cpu进行分组。
2.系统监控指标
注意：
凡是sqlserver的指标值，都可以使用以下sql语句获得：
selectcntr_valuefrom
whereobject_name='MSSQL$ZXY:
BufferManager'andcounter_name='totalpages'
以第2点的Sqlserver:
Buffermanagertotalpages为例，其中蓝色字体代表实例名称，将红色字体替换为Buffermanager，粉色字体替换为totalpages即可。
需要注意的是复制时注意空格要和原来一样。
2.1.内存
2.1.1.外部压力
1、Processworkingset显示每一个进程虚拟地址空间中最近引用的页面规模。
消耗内存最多的进程就是消耗大量内存的应用。
指一个进程的4GB虚拟地址空间中被映射到RAM中的部分的大小，通常是该进程的虚拟内存中的活跃部分。
2、SqlserverSqlserver:
Buffermanagertotalpages显示sqlserver已获得的页面数
3、SqlserverSqlserver:
buffermanagertargetpages显示sqlserver的bufferpool必需的理想页面数。
2.1.2.内部内存压力
内部压力通常是收缩缓冲池导致的。
缓冲池规模变小，通常是sp_configure的maxservermemory的值减小导致。
或者是其他数据库对象消耗了内存空间导致，如：
作业，扩展存储过程，com对象，sqlclr，链接服务器。
通过dbccmemorystatus查看buffercounts部分，确定内存是否存在压力。
其中committedmemory和targetmemory最重要。
Committedmemory表示sqlserver已经得到的内存数量，targetmemory表示有效运行所需的内存数量。
当两个存在差别过大，说明可能存在内存压力。
2.1.3.确认内存瓶颈
1、SQLServer:
BufferManagerPageLifeExpectancy显示数据页在缓冲池中驻留的时间长度（单位是秒）。
值越大，系统越健康。
如果存在内存瓶颈，这个值会低于300s或者更少。
2、SQLServer:
BufferManagerBuffercachehitratio显示数据库内存命中率，所请求的数据或者说页面在缓冲池（物理内存）被找到的次数。
如果这个值很低，说明内存不足。
这个值至少大于98%。
3、缓冲池的大小由两个参数决定：
sp_configure的minservermemory和maxservermemory。
当其他应用使用了这些内存后，操作系统就不会再将这些内存分给sqlserver.
4、SQLServer:
BufferManagerStolenpages显示了内存被其他进程挪用的页面。
当这个指标与目标内存页面数比例较大时可能存在问题。
5、SQLServer:
MemoryManagerMemoryGrantsPending显示等待内存授权的进程队列。
这个指标值为0时理想状态。
6、SQLServer:
BufferManagerCheckpointpages/sec显示检查点操作每秒写入磁盘的脏页数目。
如果这个值很高，说明缺少内存。
7、SQLServer:
BufferManagerLazywrites/sec显示每秒将脏页从小写到磁盘的次数。
这个值应该尽可能接近0，当大于20或者更多，确信缓冲池不够。
8、Memtoleave大小基于工作者线程的数目动态变化，是一个独立的内存地址范围，供链接服务器，扩展存储过程，com对象或第三方ddl使用。
2.2.磁盘
1、PhysicalDiskAueueLength显示每一个磁盘的队列长度。
不要选择_TOTAL对象，而是查看某驱动盘符的队列长度，如果参数值持续大于2，则可能影响性能。
2、PhysicalDisksec/Read显示每次读取的平均磁盘时间。
3、PhysicalDisksec/Write显示每次写入的平均磁盘时间。
4、如果存在磁盘性能问题，控制器使用率等问题，上面两个计数器的平均值高于正常值。
理想情况下值小于10毫秒。
5、SQLServerAccessMethodsFullscans/sec显示每秒请求完全索引扫描或者全表扫描的数目，如果扫描频率每秒大于1，那么说明索引缺少或者索引比较差。
6、SQLServerAccessMethodsPageSplits/sec显示每秒页面拆分的次数。
可以通过适当的索引维护或者好的填充因子来避免发生。
2.2.1.基于配置的磁盘瓶颈
1、数据文件和日志文件布局（将这两类文件隔离，并且将每一类文件也隔离）
2、数据文件和日志文件自动增长管理（手动管理）
3、Tempdb配置（指定初始化大小）
4、Tempdb内部争用
5、磁盘/控制器争用（用存储厂商的性能监控工具）
2.2.2.基于模式的磁盘瓶颈
1、索引不当或者缺少索引，使用数据库优化顾问（databasetuningadvisor）来解决。
或者查看。
2、文件和文件组，将读取数据量大且频繁的表单独放到文件或者组合到文件组中，可以适当降低io并便于移植以提高性能。
3、分区，降低io
4、索引/表碎片，当索引和表的碎片较多时会产生页拆分（类似于oracle的行链接和行迁移）。
产生的原意有两种：
内部和外部。
内部碎片是指8kb数据页包含数据很少。
外部碎片是指数据不是顺序存放的。
在dm_db_index_physical_stats提供当前碎片级别的所有信息；只有有聚簇索引的表才能重建索引；使用alterindex命令重建索引。
2.2.3.指标
1、SQLServerGeneralStatisticsTempTablesCreationRate显示每秒创建的临时表和临时变量的数目。
2、SQLServerGeneralStatisticsTempTablesForDestruction显示不再需要的正在等到销毁的临时表的数目。
3、和查看每一个服务器进程使用的tempdb空间，具体见。
2.2.4.存储性能压力测试指标
1.IOPS是每秒能够为输入输出请求提供服务的度量。
得到的子系统实际吞吐量和io请求的大小的函数。
2.Blocksize提供sql执行的IO的大小的指示
3.MB/sec每秒有多少MB的数据进入或者离开子系统。
4.Latency为每一个io完成所必须等待的时间长度。
5.DISKQueuelength磁盘队列的长度，说明当前io请求时排队等候的io请求的数目。
2.2.5.度量IO性能
%DISKReadTime,%DiskTime,%DiskWriteTime%DiskIdleTime说明采用周期内磁盘读取，踩入，使用和空闲各占的百分比。
Bytes/sec/Read,Bytes/sec/Transfer,Bytes/sec/Write说明每次读取、写入或者传送的平均字节数。
Queuelength,ReadQueuelength,WriteQueuelength说明了平均磁盘队列长度，磁盘队列长度是等待被送到磁盘的请求的数目。
sec/Read,sec/Transfer,sec/Write说明io的延迟，每次读取或者写入所占用时间的长短。
DiskBytes/sec,DiskReadBytes/sec,DiskWriteBytes/sec说明以Bytes/sec为单位显示吞吐量。
DiskRead/sec,DiskTransfer/sec,DiskWrite/sec显示得到的IOPS的数目。
2.3.Cpu
1、SystemProcessorQueueLength显示系统队列长度，如果平均值大于3，那么说明cpu存在瓶颈。
2、Processor%PrivilegeTime显示操作系统内部操作所花费的时间。
3、Processor%UserTime显示用户模式操作花费的时间
4、Processor%UserTimesqlservr显示sqlserver进程消耗的cpu时间的确切数量。
这个最好从性能监视器来看。
5、SQLServerSQLStatisticsSQLCompilations/sec显示查询计划的编译次数。
6、SQLServerSQLStatisticsSQLRe-Compilations/sec显示查询计划的重编译次数。
7、当SQLCompilations/sec和SQLRe-Compilations/sec次数过高的话，说明可能未使用绑定变量导致计划重新编译，或者说重新编译次数比编译次数过高的话，那么说明存在应用上的瓶颈。
2.3.1.基于配置的cpu瓶颈
很难有建议或者计数器来协助sp_configure的cpu相关配置（AffinityMask,HyperThreading,PriorityBoost,MaximumDegreeofPa
升级会员 