SQL Server 性能调优——如何定位和解决系统瓶颈
课程安排
•SQL Server性能优化概念•CPU性能瓶颈分析
•内存性能瓶颈分析
•磁盘性能瓶颈分析
性能优化介绍•什么是性能问题？
•性能的定义
–基线（Baseline）
–开销
–优化
影响性能的因素•应用程序
•查询语句
•事务管理
•数据库设计
•数据分布
•SQL Server本身
•网络
•操作系统
•硬件
CPU性能瓶颈分析
•CPU性能瓶颈的特征
•常见问题及解决方法
问题特征
•何时查看
–计数器Processor:%Processor 一直高于80%
•查看内容：
–SQL Server的计数器Process:%Processor Time值是否很高•解决方法
–找到谁在消耗CPU，并对其进行优化
–增加更多的CPU
常见的原因
•过度的编译/重编译
•低效的查询执行计划
•语句内的并行执行
•游标的不合理使用
过度编译/重编译•编译和重编译
–重编译：在SQL Server执行一个查询之前，它会查看当前执行计划的准确性和有效性，如果
检查失败，就需要编译或者重编译
–消耗较多的CPU
检测
•性能监视器
–SQL Recompilations/sec对Batch Requests/Sec的比率较高•Trace
–SP: Recompile
–RPC: Completed
–Auto Stats
原因和解决方案•SET语句
–在连接建立时运行SET语句
•统计信息变化
–指定KEEPFIXED PLAN查询提示
•不明确的对象名称
–使用明确的对象名称
•HINT提示
–去掉 WITH RECOMPILE
低效的执行计划
•背景知识
–SQL Server 优化器试图找到响应时间最快的执行计划
–由于某些原因，例如索引缺失、统计数据过期，这个执行计划实际上并不一定是最快的
–这个次优执行计划的某些因素(表扫描/索引扫描等)可能会使CPU使用率变高
•检测
–Trace, DMVs, T-SQL 语句
原因和解决方案•过时的统计信息
–UPDATE STATISTICS
•索引缺失
–使用数据库引擎优化顾问(DTA)
•代码质量
–修改查询语句
•错误索引
–查询提示(hint), 执行计划向导(plan guide )
并行查询
•背景知识
–查询优化器会选择最快的计划.
–如果查询的开销 > 并行的开销阈值 (默认为5 秒), 查询将会并行执行
–在大多数情况下，并行能够加强查询的性能
–然而，一个给定查询的响应时间必须从整个系统的吞吐量和系统上的其他查询出发来综合考虑
检测
•sys.sysprocesses (Blocker script) –一个SPID对应多条记录
–Wait type: CXPACKET
解决方案
•MAXDOP
–服务器级别及语句级别
•分析缺失的索引
•修改查询
游标的不合理使用•背景知识
–早期版本每个连接只允许一个命令
–当一条命令需要在刚刚读到的行的基础上运行，用户就需要服务器端的游标
–sp_cursorfetch 控制服务器端返回给客户端的行数，这使得ODBC或OLEDB驱动能高速缓存行.
检测
•PerfMon
–SQL Server: Cursor Manager by Type: Cursor Requests/Sec
•Trace
–RPC: Completed, 查找sp_cursorfetch
解决方案
•避免服务器端游标
•使用MARS
•使用更大的fetch缓冲区大小
内存性能瓶颈分析
•SQL Server如何使用内存
•如何识别内存性能瓶颈
SQL Server如何使用内存
•虚拟地址空间和物理内存
•动态内存管理
•AWE 和 Locked Pages
虚拟内存 vs. 物理内存•Windows 提供了虚拟内存服务
•提交的虚拟内存的是基于一些物理存储，通常是页文件或物理内存
•由Windows 来处理虚拟内存和物理存储之间的交互•32位的进程有4GB的虚拟内存地址空间，分为内核区和用户可访问区
动态内存管理
•当SQL Server动态地使用内存，它定期地查询系统，来确定空闲物理内存量•Target/total server memory
内存配置选项
•Min server memory (MB)
–指定最小值
–不会在启动时自动提交
–可能不会达到最小值
•Max server memory (MB)
–指定最大值
–SQL Server可能因为当前的使用而不能释放内存
•Set Working Set size
–不保证进程不被页出
•开启AWE / 锁定内存中的页
–避免发生Working Set Trimming (KBA 918483)
–企业版或装有特定补充包的标准版可以支持该功能(KBA 970070)
内存对其他方面的影响•内存影响了整个服务器的性能
•对磁盘I/O的影响
–过度的硬换页会干扰页文件设备上的数据I/O操作
–降低了系统文件缓存的有效性
•对CPU的影响
–增加了内核时间
–如果内存有压力(< 4或100 MB)，换页则会占用大部分的CPU时间
LazyWriter
•两个目的:
–试图保持最低数量的空缓冲区 (释放脏缓冲区会消耗 I/O)
–保留足够的空闲物理内存，从而避免频繁换页•LazyWriter VS. 检查点
检测
•PerfMon
–主要使用的计数器: Memory:Available
Mbytes, SQL Server:Memory Manager,
SQL Server:Buffer Mananger
故障排查步骤•外部压力
•性能监视器
•Sp_configure
•内部压力
•DBCC MEMORYSTATUS
内存误区•误区
–系统有大量的可用内存 = 没有内存瓶颈
–SQL Server在有压力时总是会释放内存
–SQL Server会把页锁在物理内存中
–增加MemToLeave 会提高性能
–“最小服务器内存” 在启动时就采用
–增加内存就一定会提高性能
磁盘性能瓶颈分析
•介绍
–SQL Server性能依赖于磁盘子系统的性能–数据库中的数据页并非总能够全部放到内存
当中，因此可能会发生磁盘I/O
监视I/O信息•使用单独的日志
•使用小的时间间隔: 2，4 秒
•不要把日志记到待评估的磁盘上
检测
演示：终端用户说SQL 宕机
磁盘IO的影响因素
•不合适的执行计划(缺少有效的索引) •太多OLAP类型的查询
•硬件能力不足够满足I/O需求
解决方案
•查询计划
•数据压缩
•I/O子系统升级
Q&A。
