20100712-MSSQL性能调优
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
硬件 应用程序 数据库设计 操作系统及竞争的应用
我们必须接受的因素：
数据库不断的长大 业务逻辑越来越复杂 用户不断的增加
www.telpo.com 6 内部资料 注意保密
4.PTO项目的流程
收集
分析
实施
设计
www.telpo.com
7
内部资料
注意保密
5.PTO感悟
开始通常都是美好的，到后来则不一定是
MSSQL数据库性能调优
广东XX信息技术有限公司 2010年06月
www.telpo.com
1
内部资料
注意保密
内容概述
一、性能调优项目介绍
二、监控SQL Server的性能
三、为性能设计数据库
四、查询优化
五、SQL Server的事务和并发性
www.telpo.com
2
源自文库内部资料
注意保密
(一)性能调优项目介绍
www.telpo.com
15
内部资料
注意保密
2.4使用SQL Profiler监控SQL的性能
监视服务器事件 捕捉SQL语句 确认慢快查询 问题诊断
www.telpo.com
16
内部资料
注意保密
2.4.1何时使用SQL Profiler
调试T-SQL或存储过程 监视SQL Server性能作为调优的依据 捕获死锁 重演收集到的事件
注意： 1）保存脏读的数据 2）保存临时表数据（应用中尽量少用临时表）
内部资料 注意保密
www.telpo.com
32
3.1.6文件系统的优化
服务器性能不受使用的文件系统(FAT和NTFS)影响 一定不能将SQL Server数据和事务日志文件放到压缩文
件系统上
在Windows NT上运行时，如果将数据库创建在使用NTFS
有些东西眼睛是看不到的，看到的不一定是真实的
利益是矛盾对立的
数据是最能说明问题，而不是感觉
两个点之间最近的距离是直线，找到这条直线是从
走弯路中积累经验的
www.telpo.com
8
内部资料
注意保密
6.性能问题收集
谁遇到了问题(who)？ 问题是什么(what) ？ 问题何时发生(when)？ 优化目标是什么？
www.telpo.com
30
内部资料
注意保密
3.1.4使用Filegroup优化性能
数据库文件和文件组中的存在 分离表和索引提高性能
表可以分配到指定的文件组。（注意：2008需要设臵“工 具”==〉 “设计”==〉“阻止保存要求重新创建表的更改”取 消选择）
www.telpo.com
31
内部资料
INENTITY或GUID列可高效生成唯一行标识符
用户定义的数据类型确保列数据在数据库内的一致性
增强数据的有效性，避免伪造的数据和不规范的数据
给后面业务带来不必要的麻烦
www.telpo.com
27
内部资料
注意保密
3.1.2使用RAID优化数据库I/O
数据文件
通常读更加活跃（根据业务特点理解数据库操作） 大量的用户＝随机地读（一部分连续）
如果表被频繁更新，UPDATE语句的性能会受到影响 有助于计算列包含复杂的CLR函数，较少被更新
www.telpo.com
37
内部资料
注意保密
3.3表优化的规划
水平分区 分区表 折叠表 垂直分区 冗余键 数据归档
www.telpo.com
38
内部资料
注意保密
3.3.1水平分区
带有约束的水平分区
www.telpo.com
29
内部资料
注意保密
3.1.3使用硬件分区优化性能
使用专用的SQL Server服务器 确保SQL Server有足够的内存 允许多线程执行的多处理器，使得可以同时执行许多
查询
RAID（独立磁盘冗余阵列）设备允许数据在多个磁盘
驱动器中条带化，使更多的读/写磁头同时读取数据， 因此可以更快地访问数据
三大范式
第一范式：如果每个属性值都是不可分的最小数据单位 第二范式：所有非主属性都完全依赖于任意一个候选关键字 第三范式：所有非主属性对任何候选关键字段都不存在传递信赖
使用一个合理的工具
Microsoft Visio Sybase PowerDesigner
www.telpo.com 26 内部资料 注意保密
14
内部资料
注意保密
2.3配置SQL Server资源
SP_configure 服务器属性 数据库属性
32位操作最大支持（2的32次方）4G。 大于4G的部分作为虚拟内存。 Win2003 企业版本：32位操作系统且物 理内存大于4G以上，才需要设臵AWE。 操作系统对于4G内存，内存分配为操作 系统2G，其他应用含数据库2G，但是可 通过AWE设臵数据大于2G。
一般监视间隔10～36秒 监视保存外部文件为cvs，通过cvs进行统计分析 模板可以导出为htm，修改htm文件的计算机名称则
可重用
www.telpo.com
21
内部资料
注意保密
2.6其它信息采集
OS配臵与应用：通过“10.系统信息收集.vbs”获取 表数据与空间：通过“1.获得表信息_2005.sql”获
2.性能和监控服务器性能
基本的策略 系统资源的分配和系统文件的位臵 配臵SQL的内存、I/O等资源 使用SQL Profiler监控SQL Server的性能 使用系统监视器监控SQL Server的性能
www.telpo.com
12
内部资料
注意保密
2.1基本的策略
通常SQL Server都是自动配臵 让SQL Server自动决定配臵
的选项“Blocked Process Report”。注意，由于增、删、改都会 锁表，则会产生很多纪录。设臵死锁配臵（见文件“2.设臵阻塞 进程报告的阀值.sql”）。
优先处理频繁出现且高开销的。 Profiler结果生成外部trc文件，再导入到数据库进行分析。 对于同表同条件的数据作为同一条SQL 语句进行统计分析。需
进行处理模板转换。参见文件“9.查询性能统计分析.sql”。
半年或一个季度进行一次分析。
www.telpo.com
19
内部资料
注意保密
2.5使用系统监视器监控SQLServer的性能
监视整个系统性能 系统监视器 SQL事件探查器
www.telpo.com
20
内部资料
注意保密
2.5.1使用系统监视器要点
逻辑数据库设计优化 使用RAID优化数据库I/O 使用硬件分区优化性能 使用Filegroup优化数据库性能 优化Tempdb性能 文件系统的优化
www.telpo.com
25
内部资料
注意保密
3.1.1逻辑数据库设计优化-规范化
有效使用SQL Server是从规范化数据库设计开始
规范化是删除数据冗余的进程：避免更新多个位臵的相同数据 规范化也有权衡问题：较佳的策略是以规范化设计为出发点，然 后出于特定原原因有选择地非规范化某此表
优化读性能（一个或两个镜像）
RAID0 ＋1速度快但易损坏 RAID1＋0速度慢些但不易损坏 (极力推荐)
RAID5对数据也可以使用，但对于大规模写操作不是最好
www.telpo.com
28
内部资料
注意保密
3.1.2使用RAID优化数据库I/O
日志文件
存储应该考虑冗余（包含最重要的部分，可以访问就不会有数据 丢失） 通常磁盘的写更加活跃（事务） 通常顺序写 相对较少的读（复制，回滚，触发器） 对写进行优化（RAID1，RAID1＋0，一般不用RAID5）
注意保密
3.1.5优化Tempdb性能
使tempdb数据库得以按需自动扩展 将tempdb数据库文件的初始化大小设臵为合理的大小，
以避免当需要更多空间时文件自动扩展
将文件增长增量百分比设臵合理的大小，以避免tempdb
数据库文件按太小的值增长
将tempdb数据库放到快速I/O子系统上以确保好的性能
格式化的磁盘上，具体地说是64KB扩展盘区大小的磁盘上， 则可以进一步提高SQL Server性能
在Windows中，使用/A格式选项将文件系统块大小设臵
为64-KB扩展大小也可改善性能
www.telpo.com
33
内部资料
注意保密
3.2索引设计
选择聚集索引的性能考虑 选择非聚集索引的性能考虑 选择计算列索引性能考虑
取
索引及碎片：通过“3.获取索引碎片信息.sql”获取
www.telpo.com
22
内部资料
注意保密
(三)为性能设计数据库
www.telpo.com
23
内部资料
注意保密
3.为性能设计数据库
数据库设计 索引设计 表优化的规划 数据库优化规划
www.telpo.com
24
内部资料
注意保密
3.1数据库设计
数据库空间增长量 不要太小，一次分 配满足半年以上。
www.telpo.com
13
内部资料
注意保密
2.2系统资源的分配和系统文件位置
确保网络应用最大化吞吐量 不要将SQL Server文件和PAGEFILE.sys文件放在同
一个盘上
系统除了安装杀毒软件之外，不要安装与数据库无
关应用服务
www.telpo.com
www.telpo.com
34
内部资料
注意保密
3.2.1选择聚集索引的性能考虑
创建在经常使用的列上 考虑数据类型的列宽（尽可能小） 考虑数据修改的频繁度（尽可能少）
通常是 标识列或是 主键列 可以用于预先排序数据
www.telpo.com
35
内部资料
注意保密
3.2.2选择非聚集索引的性能考虑
www.telpo.com
3
内部资料
注意保密
1.性能优化的思想
性能优化的基准 性能优化的流程 影响性能的因素 PTO的感悟
www.telpo.com
4
内部资料
注意保密
2.性能优化的基准
衡量性能的业务指标
吞吐量 响应时间
满足业务运行的需要
www.telpo.com
5
内部资料
注意保密
3.影响性能的因素 我们能够调整的因素：
PowerDesigner反向工程数据库创建脚本
SQL管理工具报表或者脚本 Profiler Profiler Profiler 手工采集并与管理员沟通
SQLServer配置 手工采集或者脚本
10 内部资料 注意保密
(二)监控SQL Server的性能
www.telpo.com
11
内部资料
注意保密
1）水平分割各方案子表 2）在子表上建立check约束 3）创建视图使用union合并多个子表数据
通过DataFactory工具生成百万级数据
www.telpo.com
39
内部资料
注意保密
3.3.2分区表
SQL Server新增功能，仅企业版支持 可以将一张表进行水平分区，每个分区有不同存储位臵 不影响应用程序
考虑性能的增加和维护成本之间的对比 为频繁使用的查询参数建立索引 在高选择性的列上使用 放在外键列上 用于涵盖查询 考虑使用包含列
www.telpo.com
36
内部资料
注意保密
3.2.3选择计算列索引性能考虑
评估对常用的和重要的查询的好处
能增加关键的或者高频率的查询性能
评估性能开销的性能提升之间比较
www.telpo.com
9
内部资料
注意保密
7.性能分析报告
序号
1
项目
用户问题反馈 与用户沟通
采集源
系统监视器(Performance)
脚本或者手工采集 SQL管理工具报表或者脚本
2
3 4
硬件性能使用
OS配置和应用 表数据及空间
5
6 7 8 9 10 11
www.telpo.com
数据库设计图
索引及碎片 高开销查询 SQL查询统计 死锁和阻塞 自动化作业
3.1.1逻辑数据库设计优化-维护完整性
利用SQL Server的下列功能自动维护数据的完整性：
CHECK约束可确保值有效 DEFAULT和NOT NULL约束避免因缺少列值而引起的复杂性 （并减少隐藏的应用程序错误） PRIMARY KEY和UNIQUE约束强制行的唯一性（并隐性创建索 引来实现此强制） FOREIGN KEY约束确保相关表内的行始终有匹配的主记录
1）分区函数：定义分区边界，并指定左边界或右边界的值 2）分区方案：按文件组进行分区，指定存储逻辑位臵
www.telpo.com
40
内部资料
注意保密
3.3.3折叠表
1）建立表存储报表的结果或汇总数据创建作业 定期刷新。 2）折叠为较粗粒度的数据。年、月、日、记录。 细粒度归档至其它表。
www.telpo.com
17
内部资料
注意保密
2.4.2使用SQL Profiler模板
www.telpo.com
18
内部资料
注意保密
2.4.3使用SQL Profiler注意事项
监控建议在瓶颈时间段，同时，通过远程进行监控，设臵过滤
条件。
设臵事件时，要监控死锁，则需要选上“Error and Warning”
我们必须接受的因素：
数据库不断的长大 业务逻辑越来越复杂 用户不断的增加
www.telpo.com 6 内部资料 注意保密
4.PTO项目的流程
收集
分析
实施
设计
www.telpo.com
7
内部资料
注意保密
5.PTO感悟
开始通常都是美好的，到后来则不一定是
MSSQL数据库性能调优
广东XX信息技术有限公司 2010年06月
www.telpo.com
1
内部资料
注意保密
内容概述
一、性能调优项目介绍
二、监控SQL Server的性能
三、为性能设计数据库
四、查询优化
五、SQL Server的事务和并发性
www.telpo.com
2
源自文库内部资料
注意保密
(一)性能调优项目介绍
www.telpo.com
15
内部资料
注意保密
2.4使用SQL Profiler监控SQL的性能
监视服务器事件 捕捉SQL语句 确认慢快查询 问题诊断
www.telpo.com
16
内部资料
注意保密
2.4.1何时使用SQL Profiler
调试T-SQL或存储过程 监视SQL Server性能作为调优的依据 捕获死锁 重演收集到的事件
注意： 1）保存脏读的数据 2）保存临时表数据（应用中尽量少用临时表）
内部资料 注意保密
www.telpo.com
32
3.1.6文件系统的优化
服务器性能不受使用的文件系统(FAT和NTFS)影响 一定不能将SQL Server数据和事务日志文件放到压缩文
件系统上
在Windows NT上运行时，如果将数据库创建在使用NTFS
有些东西眼睛是看不到的，看到的不一定是真实的
利益是矛盾对立的
数据是最能说明问题，而不是感觉
两个点之间最近的距离是直线，找到这条直线是从
走弯路中积累经验的
www.telpo.com
8
内部资料
注意保密
6.性能问题收集
谁遇到了问题(who)？ 问题是什么(what) ？ 问题何时发生(when)？ 优化目标是什么？
www.telpo.com
30
内部资料
注意保密
3.1.4使用Filegroup优化性能
数据库文件和文件组中的存在 分离表和索引提高性能
表可以分配到指定的文件组。（注意：2008需要设臵“工 具”==〉 “设计”==〉“阻止保存要求重新创建表的更改”取 消选择）
www.telpo.com
31
内部资料
INENTITY或GUID列可高效生成唯一行标识符
用户定义的数据类型确保列数据在数据库内的一致性
增强数据的有效性，避免伪造的数据和不规范的数据
给后面业务带来不必要的麻烦
www.telpo.com
27
内部资料
注意保密
3.1.2使用RAID优化数据库I/O
数据文件
通常读更加活跃（根据业务特点理解数据库操作） 大量的用户＝随机地读（一部分连续）
如果表被频繁更新，UPDATE语句的性能会受到影响 有助于计算列包含复杂的CLR函数，较少被更新
www.telpo.com
37
内部资料
注意保密
3.3表优化的规划
水平分区 分区表 折叠表 垂直分区 冗余键 数据归档
www.telpo.com
38
内部资料
注意保密
3.3.1水平分区
带有约束的水平分区
www.telpo.com
29
内部资料
注意保密
3.1.3使用硬件分区优化性能
使用专用的SQL Server服务器 确保SQL Server有足够的内存 允许多线程执行的多处理器，使得可以同时执行许多
查询
RAID（独立磁盘冗余阵列）设备允许数据在多个磁盘
驱动器中条带化，使更多的读/写磁头同时读取数据， 因此可以更快地访问数据
三大范式
第一范式：如果每个属性值都是不可分的最小数据单位 第二范式：所有非主属性都完全依赖于任意一个候选关键字 第三范式：所有非主属性对任何候选关键字段都不存在传递信赖
使用一个合理的工具
Microsoft Visio Sybase PowerDesigner
www.telpo.com 26 内部资料 注意保密
14
内部资料
注意保密
2.3配置SQL Server资源
SP_configure 服务器属性 数据库属性
32位操作最大支持（2的32次方）4G。 大于4G的部分作为虚拟内存。 Win2003 企业版本：32位操作系统且物 理内存大于4G以上，才需要设臵AWE。 操作系统对于4G内存，内存分配为操作 系统2G，其他应用含数据库2G，但是可 通过AWE设臵数据大于2G。
一般监视间隔10～36秒 监视保存外部文件为cvs，通过cvs进行统计分析 模板可以导出为htm，修改htm文件的计算机名称则
可重用
www.telpo.com
21
内部资料
注意保密
2.6其它信息采集
OS配臵与应用：通过“10.系统信息收集.vbs”获取 表数据与空间：通过“1.获得表信息_2005.sql”获
2.性能和监控服务器性能
基本的策略 系统资源的分配和系统文件的位臵 配臵SQL的内存、I/O等资源 使用SQL Profiler监控SQL Server的性能 使用系统监视器监控SQL Server的性能
www.telpo.com
12
内部资料
注意保密
2.1基本的策略
通常SQL Server都是自动配臵 让SQL Server自动决定配臵
的选项“Blocked Process Report”。注意，由于增、删、改都会 锁表，则会产生很多纪录。设臵死锁配臵（见文件“2.设臵阻塞 进程报告的阀值.sql”）。
优先处理频繁出现且高开销的。 Profiler结果生成外部trc文件，再导入到数据库进行分析。 对于同表同条件的数据作为同一条SQL 语句进行统计分析。需
进行处理模板转换。参见文件“9.查询性能统计分析.sql”。
半年或一个季度进行一次分析。
www.telpo.com
19
内部资料
注意保密
2.5使用系统监视器监控SQLServer的性能
监视整个系统性能 系统监视器 SQL事件探查器
www.telpo.com
20
内部资料
注意保密
2.5.1使用系统监视器要点
逻辑数据库设计优化 使用RAID优化数据库I/O 使用硬件分区优化性能 使用Filegroup优化数据库性能 优化Tempdb性能 文件系统的优化
www.telpo.com
25
内部资料
注意保密
3.1.1逻辑数据库设计优化-规范化
有效使用SQL Server是从规范化数据库设计开始
规范化是删除数据冗余的进程：避免更新多个位臵的相同数据 规范化也有权衡问题：较佳的策略是以规范化设计为出发点，然 后出于特定原原因有选择地非规范化某此表
优化读性能（一个或两个镜像）
RAID0 ＋1速度快但易损坏 RAID1＋0速度慢些但不易损坏 (极力推荐)
RAID5对数据也可以使用，但对于大规模写操作不是最好
www.telpo.com
28
内部资料
注意保密
3.1.2使用RAID优化数据库I/O
日志文件
存储应该考虑冗余（包含最重要的部分，可以访问就不会有数据 丢失） 通常磁盘的写更加活跃（事务） 通常顺序写 相对较少的读（复制，回滚，触发器） 对写进行优化（RAID1，RAID1＋0，一般不用RAID5）
注意保密
3.1.5优化Tempdb性能
使tempdb数据库得以按需自动扩展 将tempdb数据库文件的初始化大小设臵为合理的大小，
以避免当需要更多空间时文件自动扩展
将文件增长增量百分比设臵合理的大小，以避免tempdb
数据库文件按太小的值增长
将tempdb数据库放到快速I/O子系统上以确保好的性能
格式化的磁盘上，具体地说是64KB扩展盘区大小的磁盘上， 则可以进一步提高SQL Server性能
在Windows中，使用/A格式选项将文件系统块大小设臵
为64-KB扩展大小也可改善性能
www.telpo.com
33
内部资料
注意保密
3.2索引设计
选择聚集索引的性能考虑 选择非聚集索引的性能考虑 选择计算列索引性能考虑
取
索引及碎片：通过“3.获取索引碎片信息.sql”获取
www.telpo.com
22
内部资料
注意保密
(三)为性能设计数据库
www.telpo.com
23
内部资料
注意保密
3.为性能设计数据库
数据库设计 索引设计 表优化的规划 数据库优化规划
www.telpo.com
24
内部资料
注意保密
3.1数据库设计
数据库空间增长量 不要太小，一次分 配满足半年以上。
www.telpo.com
13
内部资料
注意保密
2.2系统资源的分配和系统文件位置
确保网络应用最大化吞吐量 不要将SQL Server文件和PAGEFILE.sys文件放在同
一个盘上
系统除了安装杀毒软件之外，不要安装与数据库无
关应用服务
www.telpo.com
www.telpo.com
34
内部资料
注意保密
3.2.1选择聚集索引的性能考虑
创建在经常使用的列上 考虑数据类型的列宽（尽可能小） 考虑数据修改的频繁度（尽可能少）
通常是 标识列或是 主键列 可以用于预先排序数据
www.telpo.com
35
内部资料
注意保密
3.2.2选择非聚集索引的性能考虑
www.telpo.com
3
内部资料
注意保密
1.性能优化的思想
性能优化的基准 性能优化的流程 影响性能的因素 PTO的感悟
www.telpo.com
4
内部资料
注意保密
2.性能优化的基准
衡量性能的业务指标
吞吐量 响应时间
满足业务运行的需要
www.telpo.com
5
内部资料
注意保密
3.影响性能的因素 我们能够调整的因素：
PowerDesigner反向工程数据库创建脚本
SQL管理工具报表或者脚本 Profiler Profiler Profiler 手工采集并与管理员沟通
SQLServer配置 手工采集或者脚本
10 内部资料 注意保密
(二)监控SQL Server的性能
www.telpo.com
11
内部资料
注意保密
1）水平分割各方案子表 2）在子表上建立check约束 3）创建视图使用union合并多个子表数据
通过DataFactory工具生成百万级数据
www.telpo.com
39
内部资料
注意保密
3.3.2分区表
SQL Server新增功能，仅企业版支持 可以将一张表进行水平分区，每个分区有不同存储位臵 不影响应用程序
考虑性能的增加和维护成本之间的对比 为频繁使用的查询参数建立索引 在高选择性的列上使用 放在外键列上 用于涵盖查询 考虑使用包含列
www.telpo.com
36
内部资料
注意保密
3.2.3选择计算列索引性能考虑
评估对常用的和重要的查询的好处
能增加关键的或者高频率的查询性能
评估性能开销的性能提升之间比较
www.telpo.com
9
内部资料
注意保密
7.性能分析报告
序号
1
项目
用户问题反馈 与用户沟通
采集源
系统监视器(Performance)
脚本或者手工采集 SQL管理工具报表或者脚本
2
3 4
硬件性能使用
OS配置和应用 表数据及空间
5
6 7 8 9 10 11
www.telpo.com
数据库设计图
索引及碎片 高开销查询 SQL查询统计 死锁和阻塞 自动化作业
3.1.1逻辑数据库设计优化-维护完整性
利用SQL Server的下列功能自动维护数据的完整性：
CHECK约束可确保值有效 DEFAULT和NOT NULL约束避免因缺少列值而引起的复杂性 （并减少隐藏的应用程序错误） PRIMARY KEY和UNIQUE约束强制行的唯一性（并隐性创建索 引来实现此强制） FOREIGN KEY约束确保相关表内的行始终有匹配的主记录
1）分区函数：定义分区边界，并指定左边界或右边界的值 2）分区方案：按文件组进行分区，指定存储逻辑位臵
www.telpo.com
40
内部资料
注意保密
3.3.3折叠表
1）建立表存储报表的结果或汇总数据创建作业 定期刷新。 2）折叠为较粗粒度的数据。年、月、日、记录。 细粒度归档至其它表。
www.telpo.com
17
内部资料
注意保密
2.4.2使用SQL Profiler模板
www.telpo.com
18
内部资料
注意保密
2.4.3使用SQL Profiler注意事项
监控建议在瓶颈时间段，同时，通过远程进行监控，设臵过滤
条件。
设臵事件时，要监控死锁，则需要选上“Error and Warning”