TechEd2012 SQL Server 性能调优 -如何定位和解决系统瓶颈

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)