SQLServer性能监控指标说明

性能监控指标说明

--MSSQL SERVER 2005

赵小艳

2010年6月30日

从MSSQL SERVER2005开始，微软开始了漫长的向ORACLE学习之旅，本文以oracle优化为基础，整理了一部分MSSQL SERVER2005监控指标，这些指标不能全部反映MSSQL SERVER2005的性能信息，因此还需要从更多的开发的角度后继完成优化过程，其中最重要的一环是设计。

由于下一步的工作就是实际的优化，请大家指出文档中的不足和错误之处。

本文整理用到的资料：清华大学出版社的《SQL SERVER 2005性能调优》，微软技术资料库，MSDN。

1. 配置硬件 (5)

1.1. 内存 (5)

1.1.1. 物理地址空间 (5)

1.1.2. 虚拟地址空间 (5)

1.1.3. 虚拟内存管理器 (5)

1.1.4. 3GB内存 (5)

1.1.5. PAE (6)

1.1.6. AWE (6)

1.1.7. 选择/3GB 、/PAE、还是/AWE (6)

1.1.8. 64bit系统内存配置 (6)

1.1.9. 内存预计大小 (6)

1.2. I/O (7)

1.2.1. 网络IO (7)

1.2.2. 磁盘IO (7)

1.2.3. 存储设计 (7)

1.2.4. 配置分区 (7)

1.2.5. 扇区对齐 (8)

1.2.6. 卷的类型 (8)

1.2.7. NTFS分配空间的大小 (8)

1.2.8. 磁盘破碎 (8)

1.3. CPU (9)

1.4. 32bit x86处理器 (9)

1.4.1. 多核 (9)

1.4.2. 超线程 (9)

1.4.3. 64bit (9)

1.4.4. X64或IA64 (9)

1.4.5. 高速缓存 (10)

1.5. 系统架构 (10)

1.5.1. SMP (10)

1.5.2. NUMA (10)

1.5.3. SOFT NUMA (10)

2. 系统监控指标 (11)

2.1. 内存 (11)

2.1.1. 外部压力 (11)

2.1.2. 内部内存压力 (11)

2.1.3. 确认内存瓶颈 (11)

2.2. 磁盘 (12)

2.2.1. 基于配置的磁盘瓶颈 (12)

2.2.2. 基于模式的磁盘瓶颈 (12)

2.2.3. 指标 (13)

2.3. Cpu (13)

2.3.1. 基于配置的cpu瓶颈 (14)

2.3.2. 基于模式的cpu瓶颈 (14)

2.4. 数据库镜像性能 (14)

3. 服务器设置 (14)

3.1. Cpu (14)

4. 数据库等待事件 (15)

4.1. 常见等待类型 (15)

5. 数据库指标 (16)

5.1. 指标采集和排列 (16)

5.1.1. 操作系统资源 (16)

5.1.2. 重要统计信息 (17)

5.1.3. 等待事件 (18)

5.1.4. 数据文件和日志文件 (18)

5.1.5. TEMPDB使用 (21)

5.1.6. 系统当前资源高消耗sql (22)

5.1.7. 当前阻塞和物理io多的会话请求信息 (22)

5.1.8. 查询计划多的sql语句 (24)

5.1.9. 索引使用情况 (25)

6. 优化方法 (26)

6.1. 统计信息 (26)

6.2. 绑定变量 (26)

6.3. 提示 (27)

7. 错误 (27)

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是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