SQL Server线上生产环境调优之Profiler

Lokie.Wang
2020-01-21 22:04:54
数据库
MSSQL

一直以来公司一套线上运行的招投标业务系统，基于.net framework 4.5，年代比较久远直接也快7年了，由于一些历史原因和当年团队的因素之前的开发者选择了存储过程作为主要业务逻辑的开发方式，相信这也是国内大多数此类业务系统的问题。随着业务逻辑日益复杂，数据量的日益增加，查询越来越缓慢同时有些操作开始出现超时的现象，这自然是非常影响用户体验的，因此优化迫在眉睫。

对性能的优化其实一直是一个可大可小的事情，大的来说可能是架构不合理带来大动干戈，小的可能就是找到性能瓶颈去优化几个点。分析架构后确实也发现了不少问题，但是这些问题都是需要大动干戈的，比如：

设计不合理的表结构
存错过程写的业务逻辑
不合理的文件存储方式，这点由其他文章描述
不合理的架构
低效的SQL语句

对于这些优化可能会是一个长期的过程并且风险巨大，这里先解决DBA层次能够解决的问题，也就是不修改程序，不修改业务逻辑，通过索引、统计信息、合理的设置来达到性能优化的目的。本文是我将要写的有关SQL Server数据库性能优化系列的第一篇文章，要谈的就是如何通过使用工具进行性能监控，添加合理的索引或者统计信息来达到目的。

前文项目背景中大家知道了存储过程非常多，业务表也非常多，开发时需求的变更或者急于完成客户需求因此表是缺少了大量的索引，虽然表结构设计本身也有不合理的地方，如冗余、主键等设计上，但是本文仅仅是通过介绍性能监控的使用方法。看下具体怎么做吧。

如何监控当前Sql Server服务器的执行情况？

SQL Server是自带相关工具的，这就是通过SQL Server Profiler，可以在SQL Server安装目录下找到。

打开后如图：

下面开始运行一个Profiler：
通过文件菜单新增一个跟踪：

选择连接是实例

连接收可以填写跟踪名字和保存方式，可以选择保存到文件和表，以及要跟踪的SQL 事件，
文件和表的保存用于下一步性能优化顾问，这里选择文件

看下事件选择：

列是一些性能指标，行可以是审计登录事件，存储过程、链接执行等，这里我们先默认，如果需要搞清这些，请参考SQL Server 联机丛书。
运行后就开始监控。

这时候可以打开系统进行用户真实操作，如果是线上环境应该可以捕捉到大量的SQL会话。当然你也可以通过管理工具去调用相关API执行数据库操作来保证相关要优化的存储过程被捕捉到。运行一段时间后停止跟踪就可以了。
当然你可以以观察性能指标比如CPU、读写等看下到底是那些操作费时，去自己进行优化。

如何使用数据库引擎优化顾问

我们通过数据库引擎优化顾问来进行优化，让SQL得出优化报告和建议。
首先在开始菜单找到数据库引擎优化顾问

点击打开并从文件菜单新建会话

点击链接后的界面如下：

左侧是之前优化后的日志记录可以删除，右侧就是需要填写的东西
先要选择工作负荷：工作负荷就是之前通过性能监控跑下来的结果，优化顾问通过分析当时工作的负荷给出建议。这里我们选择之前的跟踪文件。
用于工作分析数据库：一般就是你存储过程执行的那个库，下面选择要优化的库，由于有些存储过程会调用到其他数据库所以建议全选而不是单单选择你本身的数据库。

来看下优化选项：

在物理结构中我们一般选择增加索引，并保留现在的索引，对于分区，如果你认为数据库量足够大值得去分区的话可以选择。
然后选择运行，SQL Server进过一段时间的分析会得出报告。这里就来看下之前的结果。

以及给出相关SQL语句如：

先将脚本放入一个文件中，到测试环境先执行下确认无误后，可以选择适当的时间在生产环境执行。
注意这个过程是一个迭代和循环往复的过程，因为之前跑的监控可能不全面，直到通过几次跑不出结果或者提升程度很小后才结束。
当然这个方式是一个比较偷懒的方式，也更适合DBA去做，对于开发人员还是应该自己了解索引的原理，设计合理的表结构、设计合理的逻辑去解决问题，在日后的文章中我会继续有所涉及。
这里呢介绍一个概念是关于统计信息的概念。可以看到上图的SQL语句是Create Statistics这个大家或许不熟悉的SQL语句而不是索引，那这个是什么呢。其实这是SQL Server才有的Mysql没有所谓的统计信息。查询优化器使用统计信息来创建可提高查询性能的查询计划。对于大多数查询，查询优化器已为高质量查询计划生成必要的统计信息；但在一些情况下，需要创建附加的统计信息或修改查询设计以得到最佳结果。