原 SQL Server代理作业的巨大性能飞跃：从2天到2小时的调优

前言

在本文中，麦老师将给大家介绍如何调优SQL Server的代理作业JOB，并结合实际生产案例将一个运行时间从长达2天的作业调优缩短至令人欣喜的2小时。

本文所使用的调优方法论基本可以通用于其它SQL Server的数据库系统，该套方法论是麦老师经过好几个项目的实战案例总结所得。

闻道有先后，术业有专攻。善语结善缘，恶语伤人心。本文可能有不对之处，欢迎批评指正，欢迎转发评论。

调优前的作业情况及基本信息获取

SQL Server版本： 2012

首先通过日志文件查看器，简单获取JOB的运行情况，可以发现如下几个问题：

1、该JOB共131个step，其实就是131个存储过程

2、历史日志中，总运行时间从1天到2天不等

3、1月30日运行了12个小时还未跑完，我开始介入进行调优

可以通过如下的SQL语句，查询出JOB中哪个步骤最耗费时间：

结果：

从结果可以看出，第3、7、8、13、14、14、17、128这几个存储过程最耗费时间，其中以3和128最耗费时间，尤其是128存储过程需要1天9小时。后边重点只跟踪调优这2个存储过程即可。

在这里，存储过程名称如何获取呢？？？就是麦老师给的SQL语句中的 jstep.command列或StepName列就可以获取到。

调优过程

整个调优过程，可能涉及3个大的环节：

A、数据库层面整体调优，包括内存、CPU调整；索引碎片重建；创建missing的索引

B、具体的存储过程的SQL级别的跟踪和调优

C、继续创建missing的索引

D、观察性能是否稳定

步骤A、整体调优

这个步骤先不分析具体的SQL语句，因为SQL实在太多，我们先做数据库整体的调优。

1、内存和CPU调优

先进行数据库配置方面的调优，尤其是内存和并发，可以根据实际情况进行调整，如下：

可以看出，内存和CPU都不是瓶颈。

2、数据库总体层面的索引碎片重建

根据麦老师的经验，若SQL Server的SQL性能渐渐慢下来的话，很大程度上跟大表的索引碎片严重有关系，很多大表的索引碎片会达到90%以上，所以，必须重建。

但是，这类JOB慢，涉及的表很多，作为DBA只能从数据库整体层面来进行索引的重建，我们可以使用如下脚本查询当前数据库中碎片率大于30%的所有索引，若有多个数据库，则需要分别对每个库进行查询，这个脚本执行很慢，可能需要七八个小时甚至十几个小时，不用着急，慢慢跑：

跑出结果后，拷贝index_rebuild列，然后执行SQL重建索引即可，可能又需要好几个小时甚至十几个小时。

3、创建missing的索引

相关理论可以参考：https://mp.weixin.qq.com/s/_0AqqTvsZXwPJ2tNrYH5Yw

缺失索引功能是一种轻量工具，用于查找可显著提高查询性能的缺失索引。

我们可以直接使用如下的SQL获取数据库运行过程中需要创建的索引：

我们可以直接复制create_index_statement列，拷贝到文本编辑器中，将查询出来的索引进行手工的合并，因为有的索引有重叠，该步骤可能需要业务人员进行介入讨论。另外，对于OLTP类型的重要业务库，一定要提交变更才能创建索引，否则最后背锅的都是自己。

修改或合并完成后，把这些缺失的索引都创建上，这个过程也需要很久。

调优结果1

其实，在经过以上1、2和3步骤后，数据库性能应该已经有了显著的提升了，例如，麦老师这个环境：

可以看到，整个job的运行时间先缩短到21小时（碎片重建），再缩短到9小时（创建missing索引）。

step 3从之前的7小时缩短到3小时，step 128从之前的1天9小时缩短到现在的6小时。

但，时间仍然有点长，需要继续进行深入调优。

步骤B、具体存储过程调优

接下来的调优，因为涉及具体的额存储过程，需要找到存储过程中到底是哪个SQL很慢导致的，所以，需要借助SQL Server Profiler功能进行跟踪，比较费时，使用方法具体可以参考：https://www.dbaup.com/mssqlruhehuoqucunchuguochengzhongzhengzaizhixingdezhenshisqlyuju.html，示例：

另外，开启SQL Server Profiler还不够，还需要做3个事情：

1、拷贝原来的存储过程来创建新的存储过程，可以以debug结尾，例如：sp_aaa 重建为sp_aaa_debug

2、修改sp_aaa_debug存储过程内容，把里边的#修改为##号，好处是，可以在新开的窗口中分析其执行计划（有的场景仍然不能显示）

3、执行存储过程时，若存储过程内容少且无循环语句，则可以选择“包括实际的执行计划”功能，这样，我们可以分析出来存储过程中每一步的执行计划，方便通过执行计划进行调优。

在观察执行计划时，重点关注rid，Key Lookup、开销比较大的步骤等，这些都是我们需要调优的地方。

1、step 128调优

step 128执行时间历史：

该存储过程由于之前的碎片重建和缺失索引创建，性能已经有所提升，从1天多到10小时，再到5小时。

接下来，继续SQL级别的调优。

在进行setp 128跟踪调优时，发现有一类插入语句很慢，

虽说SQL语句都是插入不同的表中，但是这些SQL语句中都包含了一个共同的表，暂且叫FACT_AA表，查询该表的信息：

可以看到，该表大概 23亿，300GB大小。

找业务人员确认，只需要保留1个月的数据即可，做如下清理操作：

修改相关存储过程，并定期进行清理该表。

最终，step 128调优后的总运行时间为30分钟左右：

2、step 3调优

step 3执行时间历史：

该存储过程由于之前的碎片重建和缺失索引创建，性能也已经有所提升，从7小时到4小时，再到3小时。

接下来，继续SQL级别的调优。

在进行setp 3 跟踪调优时，发现有一个插入语句很慢，查询该进程的等待事件发现是SOS_SCHEDULER_YIELD，查询SQL如下：

如图所示：

SOS_SCHEDULER_YIELD该等待事件比较有意思，表示一个任务自愿放弃当前的资源占用，让给其他任务使用。 有关该等待事件可以参考：https://www.dbaup.com/sql-serverzhongdedengdaishijiansos_scheduler_yieldshuoming.html

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。