性能监控指标说明
--MSSQL SERVER 2005
赵小艳
2010年6月30日
前言
从MSSQL SERVER2005开始，微软开始了漫长的向ORACLE学习之旅，本文以oracle优化为基础，整理了一部分MSSQL SERVER2005监控指标，这些指标不能全部反映MSSQL SERVER2005的性能信息，因此还需要从更多的开发的角度后继完成优化过程，其中最重要的一环是设计。
由于下一步的工作就是实际的优化，请大家指出文档中的不足和错误之处。
本文整理用到的资料：清华大学出版社的《SQL SERVER 2005性能调优》，微软技术资料
库，MSDN。
1. 配置硬件 2
1.1. 内存 2
1.1.1. 物理地址空间 2
1.1.2. 虚拟地址空间 2
1.1.3. 虚拟内存管理器 3
1.1.4. 3GB内存 3
1.1.5. PAE 3
1.1.6. AWE 3
1.1.7. 选择/3GB 、/PAE、还是/AWE 3
1.1.8. 64bit系统内存配置 4
1.1.9. 内存预计大小 4
1.2. I/O 4
1.2.1. 网络IO 4
1.2.2. 磁盘IO 4
1.2.3. 存储设计 4
1.2.4. 配置分区 5
1.2.5. 扇区对齐 5
1.2.6. 卷的类型 5
1.2.7. NTFS分配空间的大小 5
1.2.8. 磁盘破碎 6
1.3. CPU 6
1.4. 32bit x86处理器 6
1.4.1. 多核 6
1.4.2. 超线程 6
1.4.3. 64bit 7
1.4.4. X64或IA64 7
1.4.5. 高速缓存 7
1.5. 系统架构 7
1.5.1. SMP 7
1.5.2. NUMA 8
1.5.3. SOFT NUMA 8
2. 系统监控指标 8
2.1. 内存 8
2.1.1. 外部压力 8
2.1.2. 内部内存压力 8
2.1.3. 确认内存瓶颈 9
2.2. 磁盘 9
2.2.1. 基于配置的磁盘瓶颈 10
2.2.2. 基于模式的磁盘瓶颈 10
2.2.3. 指标 10
2.3. Cpu 10
2.3.1. 基于配置的cpu瓶颈 11
2.3.2. 基于模式的cpu瓶颈 11
2.4. 数据库镜像性能 11
3. 服务器设置 11
3.1. Cpu 11
4. 数据库等待事件 11
4.1. 常见等待类型 12
5. 数据库指标 12
5.1. 指标采集和排列 12
5.1.1. 操作系统资源 12
5.1.2. 重要统计信息 14
5.1.3. 等待事件 14
5.1.4. 数据文件和日志文件 15
5.1.5. TEMPDB使用 18
5.1.6. 系统当前资源高消耗sql 18
5.1.7. 当前阻塞和物理io多的会话请求信息 19
5.1.8. 查询计划多的sql语句 21
5.1.9. 索引使用情况 22
6. 优化方法 23
6.1. 统计信息 23
6.2. 绑定变量 23
6.3. 提示 23
7. 错误 23
1. 配置硬件
1.1. 内存
1.1.1. 物理地址空间
物理地址是处理器用来访问位于其总线上的所有部件的地址集合。PAE地址总线是36bit。64bit服务器生产商将地址总线限制在44bit。
1.1.2. 虚拟地址空间
在32bit系统中，进程有4gb虚拟地址空间，虚拟内存由内核和用户进程共享。内核和用户进程各使用2g。
1.1.3. 虚拟内存管理器
虚拟内存管理器（vmm）管理所有内存，并在系统中所有使用内存的进程间共享。虚拟内存管理器使每一个进程在需要内存时提供4gb的虚拟内存的错觉，事实上所有进程争夺有限的物理内存。sqlserver备份表语句
操作系统的内存有限，为了让进程有使用4gb虚拟内存的错误，用交换空间来保证所有进程
的虚拟内存空间的使用。
如果系统有足够的物理内存，那么vmm只要分发内存并在进程结束时清理内存即可。如果没有足够的物理内存，vmm必需为每一个进程在需要时提供所需的内存。Vmm通过页面文件暂存进程有一段时间没有访问的数据来实现，这个过程叫分页。进程的数据经常被分页或者交换到磁盘。
1.1.4. 3GB内存
增加32bit系统中进程内存数量的一种方法是将分配给内核的一部分空间拿来用作用户模式地址空间。
/3GB选项将边界移动到3GB，每个进程额外获得1GB的vas。此时内核只有1gb虚拟内存，有可能会产生问题导致崩溃。
/USERVA是windows server2003新增的功能，提供从内核中获取指定的不同地址空间数量的方式，使每个进程额外获得0gb到1gb的地址空间，与3GB方式相同，当然也会导致崩溃。
1.1.5. /PAE
Intel引入用来突破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物理内存，如果内存是瓶颈，使用3GB
4GB物理内存以上，使用/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_size（默认4KB） + 94 KB)*会话数
如果会话有多个活动结果集，那么内存使用量为(3 + 3 * num_logical_connections) * network_packet_size + 94 KB
Sql server启动时根据配置（系统最大物理内存数，系统最大线程数和其他参数）设置缓冲区大小。Sys.dm_os_sys_info中记录当前占用操作系统资源的杂项信息。
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之间，由于SQL SERVER的io是密集型，因此该值应当设置为64或者更大。如果没有使用SAN，可以将设备管理器队列长度设置为64到128。
HBA驱动器有两种：SCSIPort和StorPort。StorPort比SCSIPort新。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论