Memory-Optimized 表查询处理指南

In-Memory OLTP 在 SQL Server 中引入了内存优化表和本机编译的存储过程。 本文概述了内存优化表和本机编译的存储过程的查询处理。

本文档介绍了如何编译和执行内存优化表的查询，包括：

• SQL Server 中基于磁盘的表的查询处理管道。

• 查询优化;对内存优化表的统计信息的角色，以及排查查询计划错误的指南。

• 使用解释的 Transact-SQL 来访问内存优化表。

• 有关内存优化表访问的查询优化需要考虑的因素。

• 本机编译的存储过程编译和处理。

• 用于优化器成本估算的统计信息。

• 修复错误的查询计划的方法。

示例查询

以下示例将用于说明本文中讨论的查询处理概念。

我们考虑两个表：“客户”和“订单”。 以下 Transact-SQL 脚本包含这两个表及其关联索引的定义，以传统的磁盘形式：

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY,  
  ContactName nvarchar (30) NOT NULL   
)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY,  
  CustomerID nchar (5) NOT NULL,  
  OrderDate date NOT NULL  
)  
GO  
CREATE INDEX IX_CustomerID ON dbo.[Order](CustomerID)  
GO  
CREATE INDEX IX_OrderDate ON dbo.[Order](OrderDate)  
GO  

为了生成本文中显示的查询计划，这两个表填充了来自 Northwind 和 pubs 示例数据库的示例数据，可以从 Northwind 和 pubs SQL Server 2000 示例数据库下载。

请考虑以下查询，该查询联接“客户”和“订单”表，并返回订单的 ID 以及关联的客户信息：

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

SQL Server Management Studio 显示的估计执行计划如下所示

用于联接基于磁盘的表的查询计划。

关于此查询计划：

• 从聚集索引中检索 Customer 表中的行，该索引是主数据结构，具有完整的表数据。

• 使用 CustomerID 列上的非聚集索引检索 Order 表中的数据。 此索引包含用于联接的 CustomerID 列和返回给用户的主键列 OrderID。 为了从 Order 表中返回额外的列，需要在 Order 表的聚集索引中进行查找。

• 逻辑运算符由物理运算符Inner JoinMerge Join实现。 其他物理联接类型为 Nested Loops 和 Hash Join。 运算符 Merge Join 利用这两个索引在联接列 CustomerID 上排序的事实。

请考虑此查询的一个变体，该变体返回 Order 表中的所有行，而不仅仅是 OrderID。

SELECT o.*, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

此查询的估计计划为：

基于磁盘的表的哈希联接的查询计划。

在此查询中，使用聚集索引检索 Order 表中的行。 物理Hash Match运算符现在用于Inner Join。 Order 上的聚集索引未在 CustomerID 上排序，因此 Merge Join 需要排序运算符，这将影响性能。 请注意Hash Match运算符的相对成本（75%），与上一示例中Merge Join运算符的成本（46%）的比较。 优化器在前面的示例中也考虑了 Hash Match 运算符，但得出结论， Merge Join 该运算符提供了更好的性能。

针对 Disk-Based 表的 SQL Server 查询处理

下图概述了 SQL Server 中用于即席查询的查询处理流：

SQL Server 查询处理管道。

在本方案中：

1. 用户发出查询。

2. 分析器和代数化器基于用户提交的 Transact-SQL 文本，构造一个包含逻辑运算符的查询树。

3. 优化器创建包含物理运算符（例如嵌套循环联接）的优化查询计划。 优化后，计划可以存储在计划缓存中。 如果计划缓存已包含此查询的计划，则跳过此步骤。

4. 查询执行引擎进行对查询计划的解释的处理。

5. 对于每个索引查找、索引扫描和表扫描运算符，执行引擎会从 Access 方法请求相应索引和表结构中的行。

6. Access 方法从缓冲池中的索引和数据页检索行，并根据需要将磁盘中的页加载到缓冲池中。

对于第一个示例查询，执行引擎请求 Access 方法中 Customer 表的聚集索引中的行以及 Order 表的非聚集索引。 Access 方法遍历 B 树索引结构以检索请求的行。 在这种情况下，所有行都检索为计划调用完整索引扫描。

解释 Transact-SQL 对 Memory-Optimized 表的访问

Transact-SQL 即席批处理和存储过程也称为解释的 Transact-SQL。 解释是指查询计划由查询计划中的每个运算符的查询执行引擎解释的事实。 执行引擎读取运算符及其参数并执行该作。

解释 Transact-SQL 可用于访问内存优化表和基于磁盘的表。 下图演示了对内存优化表进行解释 Transact-SQL 访问的查询处理：

用于解释Transact-SQL访问内存优化表的查询处理管道。

如图所示，查询处理管道基本保持不变：

• 分析器和“代数化器”构造查询树。

• 优化器创建执行计划。

• 查询执行引擎解释执行计划。

与传统查询处理管道（图 2）的主要区别在于，内存优化表的行并不是通过访问方法从缓冲池中检索出来的。 而是通过 In-Memory OLTP 引擎从内存中数据结构中检索行。 数据结构的差异会导致优化器在某些情况下选取不同的计划，如以下示例所示。

以下 Transact-SQL 脚本包含使用哈希索引的 Order 和 Customer 表的内存优化版本：

CREATE TABLE dbo.[Customer] (  
  CustomerID nchar (5) NOT NULL PRIMARY KEY NONCLUSTERED,  
  ContactName nvarchar (30) NOT NULL   
) WITH (MEMORY_OPTIMIZED=ON)  
GO  
  
CREATE TABLE dbo.[Order] (  
  OrderID int NOT NULL PRIMARY KEY NONCLUSTERED,  
  CustomerID nchar (5) NOT NULL INDEX IX_CustomerID HASH(CustomerID) WITH (BUCKET_COUNT=100000),  
  OrderDate date NOT NULL INDEX IX_OrderDate HASH(OrderDate) WITH (BUCKET_COUNT=100000)  
) WITH (MEMORY_OPTIMIZED=ON)  
GO  

请考虑对内存优化表执行的同一查询：

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

估计的计划如下所示：

用于联接内存优化表的查询计划。

观察与基于磁盘的表上相同查询的计划之间的以下差异（图 1）：

• 此计划包含表扫描，而不是针对“客户”表的聚集索引扫描：

  • 表的定义不包含聚集索引。

  • 内存优化表不支持聚集索引。 相反，每个内存优化表必须至少有一个非聚集索引，并且内存优化表上的所有索引都可以有效地访问表中的所有列，而无需将它们存储在索引中或引用聚集索引。

• 此计划包含一个 Hash Match 而不是一个 Merge Join。 Order 表和 Customer 表上的索引都是哈希索引，因此不进行排序。 A Merge Join 需要排序运算符，这将降低性能。

本地编译的存储过程

本机编译的存储过程 Transact-SQL 编译为计算机代码的存储过程，而不是由查询执行引擎解释。 以下脚本创建一个本机编译的存储过程，用于运行示例查询（从示例查询部分）。

CREATE PROCEDURE usp_SampleJoin  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT o.OrderID, c.CustomerID, c.ContactName   
FROM dbo.[Order] o INNER JOIN dbo.[Customer] c   
  ON c.CustomerID = o.CustomerID  
  
END  

本机编译的存储过程是在创建时编译的，而解释的存储过程是在首次执行时编译的。 （编译的一部分，特别是分析和生成，在创建时进行。但是，对于解释的存储过程，查询计划的优化在首次执行时进行。重新编译逻辑类似。 如果重新启动服务器，则首次执行过程时将重新编译本机编译的存储过程。 如果规划不在规划缓存中，存储过程将被重新编译。 下表汇总了本机编译和解释型存储过程的编译和重新编译情况：

编译和查询处理

下图说明了本机编译存储过程的编译流程：

存储过程的本机编译。

该过程描述为，

1. 用户向 SQL Server 发出 CREATE PROCEDURE 语句。

2. 解析器和代数转换器为过程创建处理流程，并为存储过程中的 Transact-SQL 查询创建查询树。

3. 优化器为存储过程中的所有查询创建优化的查询执行计划。

4. In-Memory OLTP 编译器使用嵌入式优化查询计划获取处理流，并生成一个 DLL，其中包含用于执行存储过程的计算机代码。

5. 生成的 DLL 将加载到内存中。

调用本机编译的存储过程将转换为调用 DLL 中的函数。

执行本机编译的存储过程。

本机编译存储过程的调用如下所示：

1. 用户发出 EXECusp_myproc 语句。

2. 解析器提取名称和存储过程参数。

   如果已准备好语句（例如使用 sp_prep_exec）分析器不需要在执行时提取过程名称和参数。

3. In-Memory OLTP 运行时查找存储过程的 DLL 入口点。

4. 执行 DLL 中的计算机代码，并将结果返回到客户端。

参数探查

解释型Transact-SQL存储过程是在首次执行时编译的，而与之相反，本地编译的存储过程是在创建时编译的。 在调用时编译解释的存储过程时，优化器在生成执行计划时使用为此调用提供的参数的值。 在编译过程中，这种参数的使用称为参数探查。

参数探查不用于编译本机编译的存储过程。 存储过程的所有参数都被视为具有 UNKNOWN 值。 与解释的存储过程一样，本机编译的存储过程也支持提示 OPTIMIZE FOR 。 有关详细信息，请参阅 查询提示 (Transact-SQL)。

检索本机编译存储过程的查询执行计划

可以使用 Management Studio 中的 估计执行计划 或使用 Transact-SQL 中的SHOWPLAN_XML选项检索本机编译存储过程的查询执行计划。 例如：

SET SHOWPLAN_XML ON  
GO  
EXEC dbo.usp_myproc  
GO  
SET SHOWPLAN_XML OFF  
GO  

查询优化器生成的执行计划由一个树组成，其中包含树的节点上和树叶上的查询运算符。 树的结构确定运算符之间的交互（从一个运算符到另一个运算符的行流）。 在 SQL Server Management Studio 的图形视图中，流从右到左。 例如，图 1 中的查询计划包含两个索引扫描运算符，该运算符向合并联接运算符提供行。 合并联接运算符向选择运算符提供行。 select 运算符最后将行返回到客户端。

本机编译存储过程中的查询运算符

下表汇总了本机编译存储过程中支持的查询运算符：

列统计信息和联接

SQL Server 维护索引键列中值的统计信息，以帮助估算某些操作的成本，例如索引扫描和索引查找。 （如果显式创建非索引键列，或者查询优化器创建统计信息以响应具有谓词的查询，则 SQL Server 还会针对非索引键列创建统计信息。成本估算中的主要指标是单个运算符处理的行数。 请注意，对于基于磁盘的表，特定运算符访问的页数在成本估算方面非常重要。 但是，由于页面计数对于内存优化表（始终为零）并不重要，因此此讨论侧重于行计数。 估算从计划中的索引查找和扫描运算符开始，然后扩展为包含其他运算符，例如联接运算符。 联接运算符要处理的估计行数基于基础索引、查找和扫描运算符的估计。 对于解释型 Transact-SQL 内存优化表访问，您可以通过观察实际执行计划来了解计划中运算符的估计行计数与实际行计数之间的差异。

对于图 1 中的示例，

• Customer表上的聚集索引扫描估算为 91；实际为 91。

• CustomerID 上的非聚集索引扫描估计为 830，实际为 830。

• 合并联接运算符估计为 815，实际为 830。

索引扫描的估计是准确的。 SQL Server 维护基于磁盘的表的行计数。 完整表和索引扫描的估计始终准确。 联接的估计也相当准确。

如果这些估算值发生更改，则不同计划替代项的成本注意事项也会发生变化。 例如，如果联接一侧的估计行数为 1 或只有几行，那么使用嵌套循环联接的开销较小。

查询的计划如下：

SELECT o.OrderID, c.* FROM dbo.[Customer] c INNER JOIN dbo.[Order] o ON c.CustomerID = o.CustomerID  

在表 Customer 中删除除一行以外的所有行后：

关于这份查询计划：

• 哈希匹配已替换为嵌套循环物理联接运算符。

• IX_CustomerID上的完整索引扫描已被替换为索引查找操作。 这导致扫描 5 行，而不是完整索引扫描所需的 830 行。

Memory-Optimized 表的统计信息和基数统计

SQL Server 维护内存优化表的列级统计信息。 此外，它还维持表中实际的行数计数。 但是，与基于磁盘的表相比，内存优化表的统计信息不会自动更新。 因此，在表中发生重大更改后，需要手动更新统计信息。 有关详细信息，请参阅 Memory-Optimized 表的统计信息。

另请参阅

Memory-Optimized 表