原 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类型的重要业务库，一定要提交变更才能创建索引，否则最后背锅的都是自己。

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

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

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