SQL Server 性能调优—如何定位并解决系统瓶颈

1、SQL Server 性能调优如何定位和解决系统瓶颈议程 SQL Server性能优化概念 CPU性能瓶颈分析 内存性能瓶颈分析 磁盘性能瓶颈分析性能优化介绍 什么是性能问题？ 性能的定义 基线（Baseline） 开销 优化影响性能的因素 应用程序 查询语句 事务管理 数据库设计 数据分布 SQL Server本身 网络 操作系统 硬件CPU性能瓶颈分析 CPU性能瓶颈的特征 常见问题及解决方法问题特征 何时查看 计数器Processor:%Processor Time一直高于80% 查看内容： SQL Server的计数器Process:%Processor Time值是否很高 解决方法

2、找到谁在消耗CPU，并对其进行优化 降低负载 升级硬件常见的原因 过度的编译/重编译 低效的查询执行计划 语句内的并行执行 游标的不合理使用过度编译/重编译 编译和重编译 重编译：在SQL Server执行一个查询之前，它会查看当前执行计划的准确性和有效性，如果检查失败，就需要编译或者重编译 消耗较多的CPU检测 性能监视器 SQL Recompilations/sec对Batch Requests/Sec的比率较高 Trace SP: Recompile SP: Compile RPC: Completed Auto Stats原因和解决方案 SET语句 在连接建立时运行SET语句 统计信息

3、变化 指定KEEPFIXED PLAN查询提示 不明确的对象名称 使用明确的对象名称 HINT提示 去掉 WITH RECOMPILE低效的执行计划 背景知识 SQL Server 优化器试图找到响应时间最快的执行计划 一旦查询被优化之后，存储引擎就使用选中的执行计划将结果返回，而被使用的这个执行计划就会被保存在内存中一个被称之为“计划缓存”的地方，从而使得这个执行计划可以被重用，从而节省CPU等资源。 由于某些原因，例如索引缺失、统计数据过期，这个执行计划实际上并不一定是最快的 这个次优执行计划的某些因素(表扫描/索引扫描等)可能会使CPU使用率变高原因和解决方案 过时的统计信息 UPDAT

4、E STATISTICS 索引缺失 使用数据库引擎优化顾问(DTA) 代码质量 修改查询语句 错误索引 查询提示(hint)并行查询 背景知识 查询优化器会选择最快的计划. 如果查询的开销 并行的开销阈值 (默认为5 秒), 查询将会并行执行 在大多数情况下，并行能够加强查询的性能 然而，一个给定查询的响应时间必须从整个系统的吞吐量和系统上的其他查询出发来综合考虑解决方案 通过在服务器级别设置最大并行度选项为1（关闭并行），然后根据确实需要在单个查询级别使用 OPTION (MAXDOP n) 覆盖它，即可达到此目的。游标的不合理使用 背景知识 在关系数据库中，我们对于查询的思考是面向集合的。

5、而游标打破了这一规则，游标使得我们思考方式变为逐行进行. SQL游标的优点是可以方便从一个结果集中进行循环遍历数据在进行操作 游标也有缺点复杂和低效，是游标的最大缺点解决方案对于游标一些优化建议l 如果能不用游标，尽量不要使用游标l 用完之后一定要关闭和释放l 尽量不要在大量数据上定义游标l 尽量不要使用游标上更新数据l 如果可以，尽量使用FAST_FORWARD关键字定义游标l 如果只对数据进行读取，当读取时只用到FETCH NEXT选项，则最好使用FORWARD_ONLY参数 内存性能瓶颈分析 SQL Server如何使用内存 如何识别内存性能瓶颈SQL Server如何使用内存 虚拟地址

6、空间和物理内存 动态内存管理 AWE 和 Locked Pages in memory虚拟地址空间和物理内存Virtual Address Space（虚拟地址空间） 它是一个应用程序能够申请访问的最大地址空间。在32位的服务器上，由于地址总线的长度是32位，所以寻址空间最大也就是4 GB。在64位的服务器上，目前的系统限制是8TB。虚拟地址空间里存放的数据信息不一定都在物理内存里。Physical Memory（物理内存） 顾名思义，就是内存芯片提供的物理存储空间。访问存放在物理内存里的数据通常比访问硬盘要快得多。对SQL Server来讲，要频繁访问的数据对象必须能长时间地缓存在物理内存里

7、，才能达到最优化的效率。AWE 和 Locked Pages in memory AWE的作用是使进程能超出32位地址空间的限制访问内存。 如果向SQL Server服务账户授予了Lock Pages in Memory权限，一旦SQL Server的内存页面被锁定了，那么这些页面就不能被页面交换出去。动态内存管理 当SQL Server动态地使用内存，它定期地查询系统，来确定空闲物理内存量 PerfMon 对象SQLServer：Memory Manger的计数器total server memory内存配置选项 Min server memory (MB) 指定最小值 不会在启动时自动提交

8、 可能不会达到最小值 Max server memory (MB) 指定最大值 SQL Server可能因为当前的使用而来不及释放内存，预留2到4GB内存给操作系统 开启AWE和内存锁定页内存对其他方面的影响 内存影响了整个服务器的性能 对磁盘I/O的影响 过度的硬换页会干扰页文件设备上的数据I/O操作 降低了系统文件缓存的有效性 对CPU的影响 增加了内核时间 如果内存有压力( 4或100 MB)， 换页则会占用大部分的CPU时间LazyWriter 两个目的: 试图保持最低数量的空缓冲区 保留足够的空闲物理内存，从而避免频繁换页 LazyWriter VS. 检查点checkpoint检测

9、 SQL性能计数器 主要使用的计数器: Memory:Available Mbytes, SQL Server:Memory Manager, SQL Server:Buffer Mananger内存误区 误区 系统有大量的可用内存 = 没有内存瓶颈 SQL Server在有压力时总是会释放内存 SQL Server会把页锁在物理内存中 “最小服务器内存” 在启动时就采用 增加内存就一定会提高性能磁盘性能瓶颈分析 介绍SQL Server性能依赖于磁盘子系统的性能数据库中的数据页并非总能够全部放到内存当中，因此可能会发生磁盘I/O监视I/O信息 使用单独的日志 使用小的时间间隔: 2，4 秒 不要把日志记到待评估的磁盘上检测DEMO案例1从一个硬盘读写非常慢的sql server服务器上收集的性能日志DEMO案例2有一个客户抱怨，每天晚上有一个客户抱怨，每天晚上SQL Server会突然很慢，几乎没响应。根据客户提供的会突然很慢，几乎没响应。根据客户提供的时间点，在时间点，在SQL Server Errorlog里可以找到这样的信息。里可以找到这样的信息。DEMO案例2 磁盘IO的影响因素 不合适的