SQL Server性能调优入门.doc

SQL SERVER 性性能能调调优优入入门门 图图文文版版 第一步 在业务高峰期抓取样本数据第一步 在业务高峰期抓取样本数据 2 个小时左右 采用的工具是 sqlserver 自带的 profiler 也叫 事件探查器 如下图 进入后 点击最左面的按钮 建立一个新的跟踪 登录需要用 DBO 权限 所以可以用 sa 登录 也可以用 windows 集成验证方式 如果当前登录的就是 sqlserver 的话 新建跟踪 一共有 4 个 tab 页进行配置 首先看第一个 跟踪名称不用更改 默认的即可 保存一共有 两种方式 一是文件 扩展名是 trc 这种方式方便你把客户那里的跟踪结果发给你 其二是数据库中 的表 为了分析方便 我们把它另存为表 此时 sql 提示你重新进行登录 这里我们把表保存到 master 中 假设表名字叫做 jq 如果有重复的 系统会提示是否覆盖 确定后回到了刚才的第一个 tab 页中 然后切换到第二个选项卡中 左面列出了各种事件类 Event Class 右面是当前已有的事件类 对于性能调优 我们不需要安全审 核 会话信息 点击删除按钮即可 继续切换到第三个 tab 页上 这里的数据列默认就够了 当然 如果你看着不顺眼 可以把 Appname NT username 等都删除 最后一个 tab 页上 我们需要把系统自己产生的事件 ID 屏蔽掉 把那个排除系统 ID 进行 check 即可 如下图 所有项目配置好后 点击 运行 按钮 持续运行两个小时左右即可 业务高峰期 能典型的反应客户最近 一段时间内的业务模式 好了 第一步的准备工作完成了 等待一段时间后 我们开始检查刚才自动保存到 master 中的表 jq 第二步 开始查找影响速度的地方 第二步 开始查找影响速度的地方 打开查询分析器 sql analyzer 登录到 master 中 从 表 jq 里面按照 I O 倒序 读取若干个 sql 根据我的习惯 一般是读取 1000 条记录 为什么根据 I O 来找呢 而不是根据时间来找呢 原因很简 单 一句 SQL 执行 稳定 的是 I O 而 duration 是一个不稳定的因素 我们进行 sql 调优的目的 就 是降低 I O 成本 从而提高效率 一般而言 I O 降低了 duration 自然就会降低 详细内容 参考 我以前的 post 执行完成后 我们仔细看下面的输出 1 XL TALLY Proc04 这个 sp 的 reads 最大 将近 100w duration 也达到了 25 秒多 2 Erp IM GMBill GetBill 这个 sp 的 I O 不算大 才 7w duration 平均都在 1 秒多点 但是这个 sp 执行的次数非常多 经过询问客户 XL TALLY Proc04 这个 sp 执行的频度很低 一天也就一两次 但是 Erp IM GMBill GetBill 大概 5 分钟就要一次 这样整体 I O 就占用的非常大 所以这里我们要重点分析 Erp IM GMBill GetBill 这个 sp 而不是第一个 总结一个原则就是 调整的重点是客户最关心的内容 是执行频度最高 看起来调整的重点是客户最关心的内容 是执行频度最高 看起来 I O 又比较大的那种 又比较大的那种 I O 最大的 不一定是我们要优先解决的内容 最大的 不一定是我们要优先解决的内容 第三步 开始分析刚才看到的那个语句 既然我们要分析 I O 那么就要把 I O 打开 这样每次调整 sql 我们都能随时看到 I O 的变化情况 这句很有用处地 set statistics io on 单纯看 I O 变化 我们会晕倒的 因为我们不知道自己做的任何改动 对 I O 是如何产生影响的 所以 还要看 sql 的执行计划是怎佯的 在查询分析器中 我们按 Ctrl K 或者如下图的菜单 check 上即可 好了 准备工作都做好了 下面开始干活了 我们首先看 sql 语句的调优 假设下面这条 sql 语句性能低下 上面的 sql 一共读取了 6636 条数据 逻辑读是 1126 那么这个 I O 是否合理呢 大了还是小了 还有 改进的余地吗 我们看执行计划 哦 一共 4 个咚咚在里面 Index seek 的成本占了 2 index scan 的占了 47 hash match 占 了 51 select 最终是 0 我们应该牢记第二个原则 所有的 index 尽可能的都走 index seek 我们看一下 billsoflading 的索引信息 当前索引为什么走 scan 这里就不说了 感兴趣的可以随便找一本介绍数据库索引的书籍来看看即可 根据我以前那篇 blog 的描述 我们知道应该建立一个复合索引 也叫 convered index boldate companyid bolcode 然后我们重新执行 sql 看看 I O 变化情况 Ooh 非常 cool logical reads 降低到了 50 为什么会这样呢 我们看一下执行计划 原来是 index scan 变成了 index seek 效率自然大大的提升 Sql 语句在 index 上调优的方法 基本就是这样 我们继续看 sp 的 对于 sp 的调优 有一点是和 sql 调优不同的 sp 内部的逻辑处理可能非常复杂 单纯从查询分析器中 我们无法得知哪一小块的 sql 执行的 I O 最大 我们只能看到一个总体的描述 所以 我们要知道 sp 内 部的信息 首先 了解自己当前的 spid 是多少 一种方法是 select spid 另一种方法是看查询分析器下面的 status bar 的信息 Ooh 我的 spid 是 101 上图的最下面那个 tips 然后我重新打开 profiler 事件探查器 重新建立一个跟踪 这里面要修改第二个 tab 页的信息 把左 面事件列 存储过程 中的 SmtpCompleted 加上 增加后的样子如下 然后修改第 4 个 tab 页 把刚才看到的 spid 101 的信息填上 点击运行后 这样 profiler 只能抓到在查询分析器中 spid 101 那个窗口发送的 sql 我们切换回查询 分析器 执行有问题的 sp 执行完成后 我们再回到 profiler 点停止按钮 一个 sp 内部所有执行的 sql 都被分开了 这次的结果假设保存在了 jq2 表中 我们把所有执行的小片 sql 都列出来 第一个是 sp 执行后的总体结果 I O 为 62328 就是这个 sp 自己的 第二个是向临时表中插入数据 I O 为 61514 我们很容易看到 这一句占用了整个 sp 的大概 95 以上的成本 如果我们把这句 insert into temptable 搞定 整个 sp 的成本自然就下来了 所以我们需要把这句 insert 搞出来 但是慢着 default 情况下 sqlserver 的 results 只显示很少的字符 第二行的 sql 我们根本抓不全的 所以我们需要修改一下设置 在查询分析器的工具 选项菜单中 切换到 结果 这个 tab 页 修改每列最 多字符个数为 8192 这是最大的允许值 然后点击 确定 按钮 重新从 jq2 中读取信息 也许你会问 如果某个 sql 特别长 怎么办 其实很简单 在你的代码中把这句 sql 单独写到 log 中 或者直接修改 sp 把这句 print 出来即可 Ok 我们把这句 insert sql 抓下来后 放到查询分析器中 因为 temptable 我们没有它的结构 所以我 们把 insert 部分注释掉 看后面的 select 语句 执行后 ooh 在 goodsmovement 表上的成本是 57834 老办法 我们继续看执行计划 其实 现在又回归到了 s