客户的困扰

前几天某程序员小王向阿里云咨询他的SQL Server数据库整体负载较高，是否有优化的方法？前几天另外一个工单则是需要阿里云工程师帮忙定位某一个时刻的数据库性能尖刺的问题。

这些都是常见的性能诊断工单，其实数据库性能诊断不仅对其数据库技能要求较高，而且需要大量的前期准备工作，如收集各种性能基线、性能指标和慢SQL日志等，尤其是面对多数据库性能调优时，往往事倍功半。

如何评估数据库负载情况？如何评估数据库

当问到，如何评估数据库负载时，不同角色可能想到不同的方法，例如以下几种：

• QPS/TPS
• 资源使用: IOPS CPU 内存
• SQL执行时间
• 并发量
• Application业务反馈

上述每一种评价方法都较为片面且作为对实际调优的参考也较为困难。

通常情况下，我们评价数据库资源负载是一个较为复杂的事情，需要我们对关系数据库有一个较为全面的理解才行，但作为数据库的使用者，大多数人不需要对数据库进行深入学习，因此，我们倾向于简化指标。

比如说，我们会只看CPU、IO、内存等指标看数据库是否存在问题，这些指标适用于监控大多数应用，但对于数据库来说可能并不能够较为正确的反映数据库内发生了什么，以及我们该如何处理。我们还要结合很多数据库特有的指标综合判断，比如各种SQL Server专用的性能计数器、DMV、等待类型、长事务、网络、活动连接等等。但这些信息需要我们对数据库自身有一个高级的了解，这使得评估数据库的负载成为一个较高门槛的工作。

下面我们不妨换一个思路，关系数据库本身是一个同步调用的过程，也就是说，从应用程序发起SQL，到数据库返回结果，是同步的，数据库不完成该请求，那么应用程序无法收到结果，在此期间应用程序与数据库之间的Session就是所谓的“Active”状态，因此我们可以尝试不再从资源使用的角度出发评估数据库负载，而简化为一个简单的指标-AAS（Average Active Session），也就是活跃会话数量。

什么是AAS概念？

设想一下，当你开车去一个目的地时，你更关注的是什么？目的地的距离？路上是否堵车？到目的地是否有停车位置？等等

你会关心汽车状态吗？或许会，但你需要了解发动机原理、汽车的相关原理才能正确判断车的状态是否正常吗？我们只需通过仪表盘几个简单的指标和报警灯做一个简单的判断即可。

数据库也是一样，绝大多数用户的场景并不需要理解数据库引擎底层原理，而是更多关注如何使用数据库，当然发烧友另说。

我们通过使用AAS的概念，提供了一种简单、抽象的评估方法，也就是数据库的活动连接数来衡量数据库的总体负载，以及每种SQL对负载的贡献，把数据库各种metric汇总为一个简单的指标----AAS。

从而使得用户使用该抽象的概念评估数据库负载，用户仅需要对比AAS与CPU核数来评估当前负载是否超出当前实例的能力，这极大的降低了用户需要对数据库技能的要求，用户可以花更多精力在业务逻辑而不是数据库技术细节上。优化器、执行计划、执行引擎，Buffer Pool，这些数据库的技术细节我们都可以减少了解。

一个AAS概念简单的图形示例如图1所示：

横轴Time为时间，假设有3个长连接（也就是上图中的User），每个连接根据应用负载向数据库发送SQL请求，当时间为1时，User1连接正在执行SQL，并使用CPU资源，User2正在等待锁资源，User3没有负载，因此时间1的AAS值为2，时间2的AAS值为3，以此类推。

那么AAS的值是2还是3究竟是高还是低？这取决于当前数据库所拥有的CPU Core数量，每一个Core维护一个完整的SQL执行周期，如图2所示：

当AAS值<=CPU核数时，通常来讲数据库的负载没有额外等待，当前负载不需要额外等待其他CPU的调度，是AAS比较理想的状态。

设想一个场景，你作为数据库的运维人员，开发或业务方找到你说，嗨，数据库出问题了。通过AAS，你可以简单的根据AAS一个指标，初步缩小排查范围，确定问题是否真正的出在数据库。

一个简单的AAS与实例核数的对比关系如下：

• AAS ≈0 数据库无明显负载，异常在应用侧
• AAS < 1 数据库无阻塞
• AAS< Max CPUs 有空余CPU核，但可能存在单个Session打满或资源（OLAP）
• AAS> Max CPUs 可能存在性能问题，但存在特殊情况
• AAS>> Max CPUs 存在严重性能问题，但存在特殊情况

案例解决

通过图3我们可以看到性能洞察功能的UI，该功能的入口如图

上下两部分，上部分是按时间序列展示每个时间段的AAS负载情况，下部分是按照不同维度由高到底展示不同维度资源所占的负载，默认以SQL维度为主。

上部分可以看到各时间段负载，每种资源所占比例，比如图中蓝色展示的是CPU，其中重要的是当前实例规格的核数（max Vcores: 32），如果AAS值超过实例所拥有的CPU核数，我们就知道当前实例负载处于超标状态，图3所示负载一直处于10左右，低于Max Vcores 32，可以知道数据库整体负载处于健康水位。

那从哪知道这些负载的来源？可以通过图3下面的部分看到对应的SQL，以及每个SQL所贡献的AAS比例，例如图中可以看到第一条SQL全部为橙色，值为1.7056，该值说明在给定时间段内，该语句存在的平均会话是1.7次。而主要是等待Lock资源，这说明该语句的瓶颈在于锁。

因此我们注意到第一个语句AAS贡献最高，且等待瓶颈在于锁，根据图4数据库调优的抽象方法论，就解决了两个问题“缩小范围”和“定位瓶颈”两个问题：

通俗点说，也就是解决了下面两个问题：

• 哪些SQL在特定时间对实例的负载影响最大
• 这些SQL为什么慢

而具体如何实施优化，以及如何验证优化效果，会在后续文章中进行讲述。

USE CASE1：快速优化整体负载情况

80 20法则同样适用于数据库，80%的负载都是由20%的 SQL产生，也就是说只要优化这20%的SQL就已经完成了80%的优化工作，进一步想，如果20%中的20%，也就是4%，优化这部分岂不是就可以完成80%*80%=64%的工作。因此很多场景下，优化头部的几个SQL就能完成绝大多数优化工作。

图4我们可以看到，示例CPU使用率一直100%，在发生阻塞时会瞬间跌到个位数。我们观察一个小时的AAS数据，看到下面单个Select的SQL的平均AAS为78，远远超过实例8C的规格，因此只要优化这一个SQL，该实例的问题基本就能够得到解决。

通过图4的SQL“分析”功能，我们能够快速根据执行计划发现常见SQL慢的原因，包括索引缺失、参数类型转换、统计信息不准确等问题。

USE CASE2：找到特定时间段内数据库响应时间变慢的原因

这类场景也是一个经典场景，数据库整体可能较长时间处于健康水平，但在业务高峰或特定时间段，存在数据库负载压力较大，业务侧SQL较慢的场景。通常情况下，大多数数据库仅存在一些指标维度的监控，比如通用的CPU、网络、IO。或者引擎侧的指标，通常通过这些指标我们能猜测出大概范围，但难以定位到具体语句，通过AAS，我们可以通过查看特定时间段的高负载定位到导致特定时间数据库问题的语句，如图6所示：

通过图6，我们可以看到在特定2分钟内有性能突发的毛刺，我们通过鼠标拖拽放大该时间范围，得到如图7所示结果

通过图7，我们可以快速定位到两个产生性能毛刺的语句，并且注意到等待类型分别为Lock与Tran Log IO，由此根据图4的调优理论，我们可以初步判断是大量的更新操作产生的日志IO负载，并由于这些语句之间的锁阻塞导致锁等待。这可以极大的降低调优成本。

回顾

通过上面的案例分析，我们最终成功帮助客户解决了问题。

今天数据库早已迈入云时代，借助阿里云RDS for SQL Server Clouddba这一免费工具，可以快速准确地降低阿里云RDS for SQL Server数据库负载优化成本与操作人员技能水平要求，从而达到将更多精力用于实现业务本身的，而不是数据库上实现细节。使用性能洞察，在云上我们可以做到不用任何额外成本，快速查看整体负载，查看负载细节，以及定位不同负载对应的SQL，从而可以帮我们在云上快速解决数据库性能问题、并定期调优整体负载。

作者信息

宋沄剑(沄迹) RDS产品部SQL Server产品线技术专家，负责SQL Server产品相关开发工作，善于分析各类疑难杂症。

原文链接：
http://click.aliyun.com/m/1000351332/

SQL Server的几个基本概念

QL Server 是一个关系数据库管理系统。

它最初是由Microsoft、Sybase 和Ashton-Tate三家公司共同开发的，于1988 年推出了第一个OS/2版本。在Windows NT 推出后，Microsoft与Sybase 在SQL Server 的开发上就分道扬镳了，Microsoft 将SQL Server移植到Windows NT系统上，专注于开发推广SQL Server 的Windows NT 版本。Sybase 则较专注于SQL Server在UNⅨ操作系统上的应用。

SQL Server 2000 是Microsoft 公司推出的SQL Server数据库管理系统，该版本继承了SQL Server 7.0 版本的优点，同时又比它增加了许多更先进的功能。具有使用方便可伸缩性好与相关软件集成

程度高等优点，可跨越从运行Microsoft Windows 98 的膝上型电脑到运行Microsoft Windows 2000 的大型多处理器的服务器等多种平台使用。

SQL Server 是如何执行查询脚本的呢？

首先，应用程序连接到SQL Server引擎， 向SQL Server发送请求。一旦应用程序连接到数据库引擎，SQL Server 创建会话（Session），用于表示客户端和服务器端之间数据交换的状态。其次，SQL Server引擎分配Task来接受查询请求，然后，SQL Server把Workder 绑定到Task，开始分配CPU资源和内存资源来处理Task。最后，通过解析、编译和优化，进入查询引擎，真正开始执行查询请求。下图从高层次上概括了SQL Server执行TSQL脚本的流程：

对于图中的相关组件，先来了解一下其基本的概念和功能，下面的术语都是在SQL Server OS中实现的。

一，Connections

连接，这是在底层协议上实现的实际的物理连接，在客户端向数据库引擎发送请求之前，必须建立应用程序和数据库引擎之间的物理链接，是应用程序和数据库引擎之间联系的物理通道，有三种连接的类型：TCP socket，命名管道（named pipe）和共享内存（shared memory）。相关的DMV：sys.dm_exec_connections。

二，Sessions

会话，当客户端应用程序连到SQL Server时，两端就会建立起一个“会话”用于交换信息。严格来说，会话不是底层的物理连接，是SQL Server对连接的逻辑表示，用于存储在数据库引擎和应用程序之间连接时所需要的设置，比如，登陆信息，事务的隔离级别，会话的SET选项等。但是，在实际应用中，通常可以把会话视为连接。

当发现一个session有多个值时，意味着出现了并行查询。一个并行查询使用相同的会话来连接客户端，但是在SQL Server端使用多个Worker（Thread）用于服务这个请求。相关DMV：sys.dm_exec_sessions，当你看到有多行数据有相同的Session ID时，这是因为SQL Server 使用多个线程来处理一个查询请求。

通常情况下，一个Connection对应一个Session，有时，可能存在多个Session对应一个Connection的情况，这是MARS（Multiple Active Result Sets）现象。

三，Request

请求，在SQL Server 的语境下，是查询或批处理的逻辑表示。SQL Server是一个 Client-Server平台，客户端与服务器（后端数据库）交互的唯一方式是通过发送包含命令的请求到数据库，而客户端与服务器端交互的协议简称为TDS（Tabular Data Stream）。应用程序使用SqlClient、OleDB、ODBC、JDBC等驱动来实现这种协议。当应用程序需要对数据库执行任何操作时，它都通过TDS协议向数据库引擎发送一个请求（request）。

简单来说，每次对数据库的操作都会以“请求”的形式发送给数据库服务器，发送请求有多种类型，主要是：Batch Request、RPC Request、Bulk Load Request。

• Batch Request：批请求，此请求类型仅包含要执行的批处理的T-SQL文本。
• RPC Request：远程过程调用请求（Remote Procedure Call Request），用于执行存储过程。
• Bulk Load Request：大容量加载请求，用于执行大容量插入（Bulk Insert）操作

相关的DMV是：sys.dm_exec_requests。

四，Tasks

任务，表示SQL Server 需要处理的一个请求。一个或多个Task会被分配用于完成一个请求。在一个完整的TDS请求达到数据库引擎时，SQL Server将会创建一个任务（Task）来处理请求。当Request到达SQL Server之后，后续操作都发生在SQL Server内部。用户可以从 sys.dm_exec_requests 查看数据库引擎接收到的所有请求。一旦一个Task接收一个请求，Task的状态由PENDING 转变为可用。

当任务被创建用于处理请求时，该Task将代表请求从开始到完成的整过程。例如，如果请求是SQL Batch类型的请求，则任务将代表整个批次，而不是单个语句，SQL Batch中的单个语句不会创建新任务。批处理中的某些个别语句可以并行执行（通常称为DOP，并行度），在这种情况下，任务将产生新的子任务以并行执行。如果请求返回结果，则当客户端完全使用结果时（例如，当您处置SqlDataReader时），批处理就完成了。您可以通过查询sys.dm_os_tasks 来查看服务器中的任务列表。

当一个新请求到达服务器并且创建一个对应的任务时，首先会处于PENDING（挂起状态），任务的状态可以有：

• PENDING：正在等待工作线程（Worker Thread）。
• RUNNABLE：可运行，但正在等待接收一个时间片（quantum）。
• RUNNING：当前正在Scheduler中运行。
• SUSPENDED：拥有worker，但是正在等待某些事件（向RUNNABLE转变）
• DONE：已经完成。
• SPINLOOP：陷入自旋锁。

当新的请求到达服务器并创建任务以处理该请求时，处于PENDING状态。在此阶段，服务器尚不知道请求实际上是什么。该任务必须首先开始执行，为此，引擎必须为其分配一个工作程序（Worker）。

五，Workers

工作进程（Workder Thread），简称为Workder，或Thread，逻辑上对应于操作系统的线程，线程是操作系统可以执行的最小处理单元，并允许将应用程序逻辑上分为多个并发执行路径。SQL Server服务器在启动时会创建一定数量的Worker（工作程序），并且可以按需创建更多工作程序，直到配置的最大工作程序线程（max worker threads）。只有Worker才能执行代码，Worker等待PENDING任务变为可用（当Task被分配用于处理请求），然后每个Workder被分配到一个Task，并执行该Task。Workder会一直执行（running），直到任务完全完成。

当没有更多可用的Worker（工作进程）时，正在等待处理（PENDING）的任务将不得不等待，直到正在执行的（running）任务完成，或者执行该任务的Workder变得可用，能够执行下一个PENDING的任务为止。

对于一个SQL批处理请求，承担该任务的工作进程将执行整个SQL批处理（每个语句）。对于SQL批处理中的语句（=> request => task => worker）是否可以并行执行，答案是否定的，因为它们是在单个线程（=> worker）上执行的，所以每个语句必须按照顺序来执行。

对于使用并行选项（DOP> 1）的语句，SQL Server会创建子任务，每个子任务都会经历完全相同的周期：创建子任务（PENDING），工作程序必须拾取子任务并执行（与SQL批处理工作者不同的工作程序），通过查询sys.dm_os_workers可以查看SQL Server中工作程序的列表和状态。

六，Scheduler

调度程序（Scheduler）是指SOS scheduler，用于管理Worker对CPU时间的需求，协调各个Worker对CPU资源的利用。每一个Scheduler都映射到一个单独的CPU，Workder在一个调度程序中保持活跃/运行（Running）的时间称作一个时间片（Quantum），最长时长为4毫秒。在其时间片到期之后，一个Worker主动退出，把时间片让给其他需要访问CPU资源的Workder，并修改自身的状态为RUNNABLE，这种调度方式称为非抢占式调度。

SOS Scheduler是非抢占式的，数据库对各种请求分配的时间都是相同的，而操作系统的调度模式抢占式的，当出现紧急情况时，按照优先级，高优先级进程抢占把低优先级低的资源。