SQL Server体系结构——一个查询的生命周期

SQL Server体系结构——一个查询的生命周期（第1部分）

为了缩小读取操作所涉及范围，本文首先着眼于简单的SELECT查询，然后引入执行UPDATE操作有关的附加过程。最后你会读到，优化性能时SQLServer使用还原工具的相关术语和流程。

关系和存储引擎

如图所示，SQL Server被分为2个主要引擎：关系引擎和存储引擎。

关系引擎有时也被称为查询处理器，因为它的主要功能是查询优化和执行。它包含检查语法和准备查询树的命令分析器；查询优化器毫无疑问是任何数据库系统中皇冠上的宝石；查询执行器对执行（查询计划）负责。

存储引擎对所有数据输入、输出管理负责。它包含用来处理行、索引、页、分配和行版本等修改的输入、输出请求的存取方法；缓存区管理器，与SQL Server主要内存用户缓冲池打交道。还包含处理用来维持一致性（ACID）的数据锁和管理事务日志的事务管理器。

缓冲池

在进入查询生命周期之前你需要知道的其它主要组件是缓冲池，在SQL Server中是最大的内存用户。缓冲池里包含SQL Server中的所有不同缓存，包括计划缓存和数据缓存，计划缓存会接在透过它生命周期的查询后的章节谈到。

 

一个简单的SELECT查询

在这个例子中，使用的查询细节不重要——没有join的简单SELECT语句，因为你只是发起一起简单的读取请求。从客户端开始，那里你首先接触的组件是SQL Server网络接口（SNI）。

 

SQL Server网络接口（SNI）

SQL Server网络接口（SNI）是建立客户端和服务器端网络连接的协议层。它由数据库引擎和SQL Server本地客户端（SQL Server Native Client：SNAC）都用到的一系列API组成。SNI代表在SQL Server 2000里建立的网络库和包含在操作系统里的微软数据访问组件（Microsoft Data Access Components：MDAC）。

SNI不是被直接配置的；你要配置在客户端和服务器端的网络协议。SQL Server支持下列协议：

• 共享内存（Shared memory）——简单、快速，共享内存是用来连接本地（与SQL Server一样的计算机）运行客户端的默认协议。只能用在本地，没有配置属性，当从本地机器连接时始终是首次尝试的。
• TCP/IP——这是最常用于SQL Server的访问协议。它通过指定IP地址和端口号使你可以连接到SQL Server。通常，当你指定一个连接实例时它会自动发生。你的内部命名解析系统解析实例名的主机名为IP地址，不管你为默认实例连接到默认TCP 1433端口还是用SQL浏览服务为命名实例使用UDP 1434找到正确的端口。
• 命名管道（Named Pipes）——TCP/IP和命名管道（Named Pipes）是在架构里被使用的兼容协议。命名管道（Named Pipes）被开发来用于局域网（LANs），但也可以被不高效的用于低速网络，例如广域网（WANs）。

使用命名管道（Named Pipes）首先需要在为SQL Server配置管理器（SQL Server Configuration Manager）使它生效（如果你要远程连接的话），然后创建一个使用命名管道（Named Pipes）作为协议连接到服务器的SQL Server别名。

命名管道（Named Pipes）使用TCP 445端口，在2个电脑间的任何防火墙里请确保这个端口被打开，包括Windows防火墙。

• VIA——虚拟接口适配器（Virtual Interface Adapter：VIA）是两个系统间高性能通讯的协议，它需要在终端和专门连接口都要有特制硬件。

与命名管道（Named Pipes）一样，使用VIA协议首先需要在为SQL Server配置管理器使它生效，然后创建一个使用虚拟接口适配器作为协议连接到服务器的SQL Server别名.虽然SQL Server 2012还支持VIA协议，从以后的版本开始它会被移除，因为新安装时需要避免这个协议被安装。

 

不管使用哪种协议，一旦连接被建立，SQL Server网络接口（SNI）在服务器上与表格数据流（TDS）终结点创建一个完全连接，用它来发送请求和接收数据。

 

表格数据流终结点（Tabular Data Stream（TDS）Endpoints）

TDS是微软所有最先由Sybase设计用来与数据库服务器交互的协议。使用例如TCP/IP的网络协议一旦连接被接通，与相关TDS终结点的联系会被创建，它在客户端与服务器端之间担当着通信点。

每个网络协议都有一个TDS终结点，并且还有一个会被专用网络连接（dedicated administrator connection：DAC）使用。一旦连通性被创建，TDS消息会被用做客户端与服务器端间的沟通。

SELECT语句通过TCP/IP协议作为TDS消息发送给SQL Server。

 

协议层（Protocal Layer）

当SQL Server中的协议层收到你的TDS包，它会倒转SQL Server网络接口（SNI）的工作，把包拆开找出里面包含的请求是什么。协议层同样对把结果和状态消息打包并作为TDS消息发回给客户端负责。

我们的SELECT语句在TDS包里标记为SQL命令类型的消息，因为它传给下一个组件，命令分析器，开始走向执行的道路。

上图显示了我们的查询现在到哪里了。在客户端，语句被SQL Server网络接口（SNI）打包在TDS包里并发送给SQL Server中的协议层，在那里被拆包，识别为SQL命令，这个代码被SQL Server网络接口（SNI）发送给命令解析器

 

命令分析器（Command Parser）

命令分析器的角色是处理T-SQL语言事件（language events）。它首先检查语法并通过协议层返回给客户端任何语法错误。如果语法是有效的，然后下一步就是生成查询计划（query plan）或查找已存在的计划。查询计划（query plan）包含SQL Server将如何去执行这段代码的细节。它通常被称为执行计划（execution plan）。

为了检查查询计划（query plan），命令分析器（Comamnd Parser）会生成T-SQL的散列（hash）并核对计划缓存（plan cache）来决定是否有合适的计划已经存在。计划缓存（plan cache）是在缓冲池（buffer pool）里用来缓存查询计划（query plan）的区域。如果找到匹配的，从缓存里这个计划会被读取并传给查询执行器（Query Executor）去执行。

https://www.cnblogs.com/woodytu/p/4471386.html

 

SQL Server体系结构——一个查询的生命周期（第2部分）

计划缓存（Plan Cache）

如果SQL Server已经找到一个好的方式去执行一段代码时，应该把它作为随后的请求重用，因为生成执行计划是耗费时间且资源密集的。

如果没找到被缓存的计划，命令分析器（Command Parser）会在T-SQL基础上生成一个查询树（query tree）。查询树的内部结构是通过树上的每个结点代表查询中需要的执行操作。这个树然后被传给查询优化器（Query Optimizer）去处理。我们的简单查询没有一个存在的计划，因此一个查询树会被创建，然后传给查询优化器

上图展示了命令分析器是用来检查现存执行计划的计划缓存，因为在缓存里没找到我们查询的任何信息，还要从命令分析器输出传给优化器查询树

查询优化器是被SQL Server团队视为最有价值的财产，也是产品中最复杂、机密的部分之一。这个所谓的基于成本（cost-based）的优化器，意味要去评估执行查询的各种方式，然后选择被认为拥有最小成本的方式去执行。执行方式以查询计划实现并从查询优化器输出。它的实际工作是在一段时间内找到好的计划，而不是最佳计划。优化器的目标通常被描述为找最有效率的计划。

优化器同样在成本的基础执行多级优化，在每一阶段增加更多可用选择项来找更好的计划。当一个好计划被找到时，优化器就停在那一阶段了。

第1阶段被称之为预优化，当语句是足够简单而只有一个最佳计划时，在第一阶段就退出剩下的步骤，移除额外成本需要。没有join的基本查询被认为简单，计划成本产出为0，然后被称为普通计划（trivial plans）。

优化实际上开始的下一阶段包含三个查找时期：

• 第0时期——这个时期优化器会找嵌套循环连接（nested loop joins）且不考虑并行运算符（parallel operators）。如果已经找到的计划成本小于0.2，优化器会停在这里。在这个阶段生成的计划称为事务处理（transaction processing）或简称TP计划。

• 第1时期——第1时期使用可用优化规则的子集来找常用格式（common patterns）的已有计划。如果已经知道的计划成本小于1.0，优化器会停在这里。这个阶段生成的计划被称为快速计划（quick plans）。

• 第2时期——在这个最后时期优化器全力以赴（pulls out all the stops）使用它所有的优化规则。它同样也会找下并行（parallelism）和索引视图（indexed views）。第2时期的完成是找到计划的成本对优化需要的时间之间的平衡。在这个时期生成的计划有完全级别（level of "Full"）的优化。

 

它的cost为多少？

这里提及的cost不能用多少秒或其他有意义的表达来衡量；它只是标记代表计划资源消耗值的一个任意数。然而，在早期的微软SQL Server世界里，它的起源是在桌面电脑上的基准检查程序（benchmark）。

在计划里，每个运算符都有一个底线成本，然后用它来乘以行的大小和预计行数来获得那个运算符的成本，计划成本就是这些所有运算符的成本。

因为成本来自于底线值且与你的硬件速度无关，在每个SQL Server装置（同比版本 like-for-like version。博主注：与版本无关。）里生成每个计划的成本是一样的。

因为我们的SELECT查询非常简单，它退出在预优化时期的操作，因为这个计划对优化器非常明显（一个普通计划）。现在已经有查询计划了，它向查询执行器（Query Executor）去执行。

 

查询执行器（Query Executor）

查询执行器的工作是不释自明的，它执行查询。更准确的说，它通过干完包含与存储引擎相互作用的检索或修改数据的每一步来执行查询。

这个SELECT查询需要检索数据，因此请求传给存储引擎（Storage Engine）通过OLE DB接口传给存取方法（Access Methods）。

上图展示了作为优化器的输出的执行计划正传给查询执行器，同时引入了存储引擎，它被查询执行器通过OLE作为接口给存取方法（Access Methods）。

 

存取方法（Access Methods）

存取方法是为数据和索引提供存储结构，还有通过数据检索或数据修改接口的一批代码。它包含检索数据的所有代码但本身不执行操作，它向缓存区管理器（Buffer Manager）传递请求。

假设我们的SELECT语句需要读取一些记录行的数据刚好在一页。存取方法（Access Methods）的代码会让缓存区管理器（Buffer Manager）检索页，因此它可以准备一个OLE DB的记录集传回给关系引擎（Relational Engine）。

 

缓存区管理器（Buffer Manager）

缓存区管理器，顾名思义，负责管理缓冲池（buffer pool）。如果你需要从页读一些记录行，缓存区管理器在缓冲池检查数据缓存看看在内存里是否有被缓存的这页。如果这页已被缓存了，结果就会传回给存取方法。如果这页没被缓存，然后缓存区管理器从磁盘里拿这页，把它放入数据缓存（Data Cache），然后把结果传回给存取方法。

你这里要记住的要点是你永远只和内存中的数据打交道。在作为记录集返回前，你请求的每个新的数据读取，首先从磁盘读取，然后写回内存（数据缓存）。

这就是为什么SQL Server需要在内存里保持最小级别的可用页面；如果在缓存里没有空间来放数据，你就不能读取任何新数据。

 

数据缓存（Data Cache）

数据缓存一直是缓冲池最大一部分；因此也是在SQL Server最大内存用户。这里每个从磁盘读取的数据页在被用之前都会被写回。

sys.dm_os_buffer_descriptors动态管理视图（DMV）每一行代表当前内存持有的每个数据页，你可以用这个脚本看看在数据缓存区（Data Cache）每个数据库占用多少空间：

1 SELECT count(*)*8/1024 AS 'Cached Size (MB)'

2    ,CASE database_id

3         WHEN 32767 THEN 'ResourceDb'

4         ELSE db_name(database_id)

5         END AS 'Database'

6 FROM sys.dm_os_buffer_descriptors

7 GROUP BY db_name(database_id),database_id

8 ORDER BY 'Cached Size (MB)' DESC 

输出结果类似：

Cached Size (MB)  Database

3287              People

34                tempdb

12                ResourceDb

4                 msdb

这个例子里，People数据库在数据缓存（Data Cache）里有3287 MB数据页。页在缓存里停留时间量由最近最少使用（least recently used：LRU）策略决定。

 

 

一个简单SELECT语句生命周期总结

 

SELECT查询的整个生命周期：

1. 在客户端与服务端使用如TCP/IP的网络协议通过网络接口（SNI）建立连接。然后在TCP/IP连接上建立与TDS终结点的联系并发送SELECT语句作为TDS消息发送给SQL Server。
2. 在SQL Server上的SNI把TDS消息拆包，读取SELECT语句，传送一个“SQL命令”给命令分析器。
3. 命令分析器在缓冲池检查计划缓存是否存在，与语句匹配的可用查询计划被命令分析器接收。如果没有找到它，基于SELECT语句创建查询树传给优化器来生成查询计划。
4. 优化器在预编译生成零成本计划或普通计划，因为这个语句太简单了。生成的查询计划然后传给查询执行器去执行。
5. 在执行时，查询执行器决定读取需要的数据来完整这个查询计划，因此通过OLE DB接口把请求传给在存储引擎里的存取方法。
6. 存取方法需要从数据库里读一个页来完成来自查询执行器的请求，它让缓存区管理器来提供这个页。
7. 缓存区管理器检查数据缓存看看它在缓存里是否已有。它不在缓存，因此从磁盘里拿这个页，放入缓存，传回给存取方法。
8. 最后，存取方法把结果集送回给关系引擎发回给客户端。

https://www.cnblogs.com/woodytu/p/4472315.html

 

SQL Server体系结构——一个查询的生命周期（第3部分）

 

一个简单的更新操作

现在应该知道只读取数据的查询生命周期，下一步来看当你需要更新数据时会发生什么。这个部分通过看一个简单的UPDATE，修改刚才例子里读取的数据。

庆幸的是，直到存取方法前，更新操作和刚才SELECT语句流程是一模一样的。

这次存取方法需要修改数据，因此在I/O请求传递前，修改的细节要存放于硬盘。这就是事务管理器（Transaction Manager）的工作。

 

事务管理器（Transaction Manager）

事务管理器有2个有趣的组件：锁管理器（Lock Manager）和日志管理器（Log Manager）。锁管理器为数据提供并发性负责，它通过使用锁传递配置的隔离级别。

备注：

在刚才提到的SELECT查询生命周期里，锁管理器也有用到，这里继续谈的话会岔开话题，这里它被提到因为它是事务管理器（Transaction Manager）的一部分。

这里真正有趣的东西是日志管理器（Log Manager），存取方法代码里想要做出改变的请求被记录，日志管理器把这些改变写到事务日志（transaction log）,这就是预写式日志(Write-Ahead Logging：WAL)。

写入事务日志是数据修改事务的一部分，它总是需要物理写入硬盘，即使在系统崩溃的时候，SQL Server也可以靠它来重读那些改变（在接下来的还原章节你会学到这个更多）。

在事务日志里实际存放的并不是修改语句，而是修改语句造成页面变更的细节。这是SQL Server为了可以撤销修改，这也让事务日志内容很难读懂，当然你可以借助第三方工具来帮忙。

回到UPDATE查询生命周期，更新操作已经被写到日志。当事务日志已经确认物理写入后，实际的数据才会修改。这也是为什么事务日志操作重要。

一旦存取方法收到确认，它把修改请求发给缓冲区管理器来完成。

事务管理器（Transaction Manager），存取方法，记录我们更新的事务日志，完成数据修改请求的缓冲区管理器。

缓存区管理器（Buffer Manager）

需要修改的页已经在缓存里了，缓存区管理器要做的只是修改需要的页， 这个更新请求由存取方法发起。在缓存中的页被修改后，确认会发回给存取方法，最后发回给客户端。

这里的关键点是UPDATE语句只改变数据缓存里的数据，并不是在磁盘上的实际数据库文件。这是基于性能的原因，现在这个也被称为所谓的脏页（Dirty Page），因为它和硬盘上对应页是不一样的。

这与ACID属性里定义的修改持久性（durability of the modification）并不违背，因为你可以使用事务日志重建改变。举例来说，如果你服务器突然断电，物理内存（例如数据缓存区）里就啥都没有了。脏页是如何并在什么时候写回数据库文件在下一章节会介绍。

更新操作的生命周期如上图所示。缓冲区管理器改变缓存中页的内容，并发送确认给存取方法。可以看到，在此期间，数据文件一直没被访问。

恢复（Recovery）

在上一章节，你读到了UPDATE的生命周期，里面谈到了SQL Server使用预写式日志(Write-Ahead Logging：WAL)方法来保持更改的持久性。变更首先写入事务日志，然后只停留在内存里。这样做是基于性能原因并使你需要撤销的话可以从事务日志里还原。本章节会介绍更多的相关新概念和流程。

 

脏页（Dirty Pages）

从磁盘读回内存的页别标识为干净页（clean page）因为它和它的副本是一样的。同样，一旦在内存里的页被修改会被标识为脏页（Dirty Page）。

使用清空缓存（DBCC DROPCLEANBUFFERS）可以从缓存里清掉干净页（Clean pages）（注：从缓冲池中删除所有缓冲区。），当你对开发和测试环境进行故障排除时非常方便，因为它强制从磁盘后续读取来实现，不是缓存，不接触任何脏页。

脏页（dirty page）就是自硬盘加载到内存有改变且现在和磁盘上不一样。用下面的动态视图可以看每个数据库有多少脏页。

1 SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages'
2 FROM sys.dm_os_buffer_descriptors
3 WHERE is_modified =1
4 GROUP BY db_name(database_id)
5 ORDER BY count(page_id) DESC

Database Dirty PagesPeople   2524Tempdb   61Master   1

如上表示在People数据库有20M的脏页（2524 * 8 / 1024).

    每当缓存空闲不足或检查点（checkpoint）发生时，这些脏页（dirty page）会被定期写回到数据库。为了更快的分配页，SQL Server总是在缓存里保持一定数量的可用空页，这些可用空页在可用缓存列表里被跟踪。

    当一个工作线程（worker thread）发起一个读请求时，它在缓存拿到64页的列表并检查这个缓存列表是否低于特定的阈值（threshold）。如果是的话，会把列表里的页标记为过期（age-out），这会引起把脏页（dirty page）写回硬盘。另外一个称为惰性写入器（Lazy Writer）的线程也是基于空闲缓存列表（free buffer list）不足。

 

惰性写入器（Lazy Writer）

惰性写入器（Lazy Writer）会定期检查空闲缓存列表（free buffer list）的大小。当值低的时候，它会扫描整个数据缓存把有段时间没用过的页标记为过期（age-out），在内存里标记它们为空闲前会写回硬盘。

惰性写入器（Lazy Writer）也会在服务器上监控可用物理内存，在内存非常不足的情况下会把空闲缓存列表（free buffer list）的内存释放回给系统。当SQL Server 很忙的时候，在还有可用物理内存和没到服务器最大内存配置阈值时，它会增大空闲缓存列表（free buffer list）的大小来满足（缓冲池的）要求。

检查点过程（Checkpoint Process）

检查点（Check Point）是个SQL Server创建的时间点，用来保证提交的事务已经将它们的变更写回硬盘。检查点成为数据库可以开始的还原点。

检查点过程（Check Point Process）用来保证已提交的事务相关的所有脏页（dirty page）已经写回硬盘。为了有效使用写入器，它也会把未提交的脏页也写回硬盘，不像惰性写入器，检查点不会从缓存中移除页；它只把脏页写回硬盘并在缓存页的页头将缓存里页标记为干净。

默认情况下，在一个忙碌的服务器里，SQL Server会在每分钟发起一次检查点，这会标记在事务日志里。如果SQL Server实例或数据库重启了，还原过程会读取日志来获知，自上一个检查点后的日志里不需要进行任何操作。

日志序列号（Log Sequence Number：LSN）在事务日志里标识记录，它是顺序的，因此SQL Server可以知道事件发生的顺序。进行前滚或后滚的操作前，最小的LSN号会被拿到。

SQL Server尝试保证还原时间自一个数据库开始少于1分钟，在此期间它不会自动执行检查点，除非有10MB的日志写入。

检查点可以通过CHECK POINT的T-SQL命令人为执行，也可以由SQL Server里的其它事件来触发。例如，你发起一个备份命令时，检查点会首先执行。

跟踪号（trace flag）3502是检查点开始和结束的错误号。例如，在自启动后添加刚才的跟踪号，在执行一系列的大量写入后，我们在错误日志里可以看到如下的条目，可以看到检查点在30-40秒之间执行一次。

 

跟踪号（trace flag）提供改变SQL Server行为的一种途径，通常帮助我们进行故障排除或者出于测试目的启用或停用特定的功能。有几百个跟踪号（trace flag）存在但官方只公开部分；点击查看它的公开列表还有如何使用它：

 

恢复间隔（Recovery Interval）

恢复间隔是个服务器配置选项，可以用来调整检查点（间的时间差，因此可以设置自开始多少时间内的数据库可以还原。

默认情况下，恢复间隔设置为0；这会启用SQL Server选择一个合适的间隔（Interval），通常是接近于1分钟自动执行一次检查点。

改变这个值为大于0时，代表你希望在检查点的之间的间隔时间大小。大多数情况下不没必要修改，你不应该在真实生产环境里修改这个值。

为了停止对磁盘子系统的太多影响，SQL Server甚至会抑制检查点的I/O，因此它很会进行自我管理。如果你在服务器上曾看到SLEEP_BPOOL_FLUSH的等待类型，这是因为SQL Server为了保持全局系统的性能进行了检查点的I/O抑制。

https://www.cnblogs.com/woodytu/p/4474652.html