SQL Server 内存中OLTP内部机制概述（四）

----------------------------我是分割线-------------------------------

本文翻译自微软白皮书《SQL Server In-Memory OLTP Internals Overview》：http://technet.microsoft.com/en-us/library/dn720242.aspx

译者水平有限，如有翻译不当之处，欢迎指正。

----------------------------我是分割线-------------------------------

 

表和存储过程的本机编译

内存中OLTP将本机编译的概念引入到了SQL Server 2014中。SQL Server可以在本机编译访问内存优化表的存储过程，而且实际上，也本地编译了内存优化表本身。本机编译比起传统的解释型Transact-SQL可以提供更快的数据访问和更高效的查询执行。

什么是本机编译？

本地编译是指将变编程结构转换为本地代码，包括可以由CPU直接执行的处理器指令，而不需要进一步的编译或解释的过程。

Transact-SQL语言由高层次的结构所组成，例如CREATE TABLE和SELECT... FROM。内存中OLTP编译器分析这些结构，并将其编译成本地代码，以进行数据访问和查询执行的快速运行时间。SQL Server 2014中的内存中OLTP编译器将表和存储过程的定义做为输入。它生成C语言代码，并利用Visual C编译器来生成本地代码。

表和存储过程编译的结果为DLL文件，这些文件被加载到内存中并链接到SQL Server进程上。

SQL Server在创建内存优化表和本地编译的存储过程时，将它们编译成本地DLL文件。另外，数据库或服务器在重新启动后，会重新编译表和存储过程的DLL文件。重建DLL文件所需的信息存储在数据库的元数据中；DLL文件本身并不是数据库的一部分。因此DLL文件并不是数据库备份的一部分。

DLL文件的维护

内存优化表和本地编译存储过程的DLL文件，以及其他为了故障诊断和可支持性而保存的生成的文件，都存储在文件系统中。

以下查询显示了当前服务器上加载到内存中的所有表和存储过程的DLL文件：

SELECT name, description FROM sys.dm_os_loaded_modules
WHERE description = 'XTP Native DLL'

数据库管理员不需要维护由本机编译生成的文件。例如在表和存储过程的删除过程中，以及在删除的数据库中， SQL Server会自动删除不再需要的生成文件，而且服务器或数据库重新启动时也会自动删除。

表的本地编译

使用CREATE TABLE语句创建内存优化表，表信息写入到数据库中的元数据中，表和索引的结构在内存中创建，并且表还被编译成一个DLL文件。

请参考以下的示例脚本，这个脚本创建了一个数据库和一个内存优化表：

USE master 
GO 
create database db1 
GO 
ALTER DATABASE db1 ADD FILEGROUP db1_mod CONTAINS memory_optimized_data 
GO 
-- adapt filename as needed 
ALTER DATABASE db1 ADD FILE (name='db1_mod', filename='c:\data\db1_mod') 
    TO FILEGROUP db1_mod 
GO 
USE db1 
GO 
CREATE TABLE dbo.t1 
(c1 int not null primary key nonclustered, 
c2 int) 
WITH (MEMORY_OPTIMIZED=ON) 
GO 
-- retrieve the path of the DLL for table t1 
SELECT name, description FROM sys.dm_os_loaded_modules 
WHERE name LIKE '%xtp_t_' + cast(db_id() AS varchar(10))
                + '_' + cast(object_id('dbo.t1') AS varchar(10)) + '.dll' 
GO

表的创建会编译表的DLL文件，并将这个DLL文件加载到内存中。紧接着CREATE TABLE语句之后的查询检索出表的DLL文件的路径。

表t1的DLL文件能够解析表的索引结构和行格式。 SQL Server使用这个DLL文件遍历索引和检索行，以及行中的内容。

存储过程的本机编译

标有NATIVE_COMPILATION选项的存储过程是本机编译的。这意味着，为了性能要求较高的业务逻辑的执行效率，在存储过程中的Transact-SQL语句都被编译为本地代码。

请参考以下的示例存储过程，这个存储过程将行插入到之前示例的表t1中：

CREATE PROCEDURE dbo.p1
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=snapshot, LANGUAGE=N'us_english')
    DECLARE @i int = 1000000
    WHILE @i > 0
    BEGIN
    INSERT dbo.t1 VALUES (@i, @i+1)
    SET @i -= 1
    END
END
GO
EXEC dbo.p1
GO
-- reset
DELETE FROM dbo.t1
GO

存储过程p1的DLL文件可以直接与表t1的DLL文件以及内存中OLTP的存储引擎进行交互，以便尽可能快的插入行。

内存中OLTP编译器充分利用了查询优化器为存储过程中的每个查询创建高效的执行计划。需要注意的是，对于本机编译的存储过程，查询执行计划被编译成DLL文件。由于SQL Server 2014不支持本机编译的存储过程的自动重新编译，对表数据的修改可能需要删除并重新创建一些存储过程，将新的查询计划合并到存储过程的DLL文件中。需要注意的是，在服务器重新启动以及故障转移到一个AlwaysOn副本后，本机编译的存储过程在第一次执行时进行重新编译，这意味着查询优化器将创建新的查询计划，新的查询计划随后会被编译到存储过程的DLL文件中。

编译和查询处理

图15显示了本机编译的存储过程的编译过程：

图15 存储过程的本地编译

 

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

2. 分析器和 algebrizer 为该存储过程创建处理流程，并为存储过程中的 Transact-SQL 查询创建查询树

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

4. 内存中 OLTP 编译器通过嵌入的优化查询计划接管处理流程，并生成一个 DLL，其中包含执行存储过程的机器代码

5. 生成的 DLL 加载到内存中，并连接到SQL Server进程

本机编译的存储过程的调用转换为对 DLL 中函数的调用，如图16所示

图16 本地编译的存储过程的执行

 

1. 用户发出一条’ EXEC myproc’语句

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

3. 内存中 OLTP 运行时查找存储过程DLL文件的 入口点

4. DLL文件执行存储过程逻辑，结果会返回到客户端

参数嗅探

解释型 Transact-SQL 存储过程在首次执行（调用）时编译成直接的物理执行计划，而本机编译的存储过程在创建时编译。当在调用时对解释型存储过程进行编译，优化器使用为此调用提供的参数值生成执行计划。这种编译期间的参数用法称为“参数嗅探”。

参数嗅探不适用于编译本机编译的存储过程。此类存储过程的所有参数都视为具有 UNKNOWN 值。

SQL Server的功能支持

许多SQL Server功能都支持内存中OLTP和含内存优化表的数据库，但不是所有SQL Server功能都支持。比如，AlwaysOn组件，日志传送和数据库的备份和恢复都完全支持内存中OLTP。事务复制部分支持，允许内存优化表作为一个订阅使用，但不能作为发布项目。但是，不支持数据库镜像。可以使用SQL Server Management Studio来管理内存优化表，也支持SSIS。

有关支持和不支持功能的完整列表，请参阅SQL Server内存中OLTP的文档。

管理经验

内存中OLTP已完全集成到SQL Server的管理经验中。正如上面提到的，SQL Server Management Studio中能够管理内存优化表，文件组和本地编译的存储过程。还可以使用SQL Server管理对象（SMO）和PowerShell来管理您的内存优化对象。

元数据

一些已有的元数据对象已经得到增强，可提供关于内存优化表和存储过程的信息，新的对象也已经增加。

有一个函数得到了增强：

  • OBJECTPROPERTY - 现在包括一个属性TableIsMemoryOptimized

目录视图

以下的系统视图得到增强：

  • sys.tables 有三个新列：
    • durability（0或1）
    • durability_desc（SCHEMA_AND_DATA和SCHEMA_ONLY）
    • is_memory_optimized（0或1）
  • sys.table_types  现在有一个is_memory_optimized列
  • sys.indexes  现在有一个可能为7的type值和一个对应为NONCLUSTERED HASH的type_desc值。 （像一个非聚集的B-tree索引一样，非聚集索引有一个为2的 type_value和为NONCLUSTERED的type_desc）。
  • sys.index_columns列is_descending_key现在有不同的语义，对于哈希索引，这个值是无意义的，可以忽略。
  • sys.data_spaces  现在有一个可能为FX的type值和一个对应为MEMORY_OPTIMIZED_DATA_FILEGROUP的type_desc值
  • sys.sql_modules和sys.all_sql_modules - 现在包含一个uses_native_compilation列

此外，还有一些专门提供内存优化表信息的几个新的元数据对象。

增加了一个新的目录视图来支持哈希索引：sys.hash_indexes。这个视图是基于sys.indexes，因此具有和sys.indexes相同的列，并有一个额外添加的列。这个bucket_count列显示了为索引所指定的哈希桶的数量，而且如果没有删除和重建索引的话，这个值不会被改变。

动态管理对象

以下SQL Server动态管理视图是为内存中OLTP所新增的。 （xtp标识符代表“极限事务处理（eXtreme transaction processing）”。）sys.dm_db_xtp_*开头的那些动态管理视图提供了关于启用内存中OLTP的各个数据库的信息， sys.dm_xtp_*开头的那些动态管理视图提供了实例级别的信息。您可以在文档中读到关于这些对象的详细信息。这些动态管理视图中的一些在本文较早的相关章节已经提到过。

关于支持内存优化表的动态管理视图的详细信息，请参阅内存优化表的动态管理视图。

  • sys.dm_db_xtp_checkpoint
  • sys.dm_db_xtp_checkpoint_files
  • sys.dm_db_xtp_gc_cycles_stats
  • sys.dm_xtp_gc_stats
  • sys.dm_xtp_system_memory_consumers
  • sys.dm_xtp_threads
  • sys.dm_xtp_transaction_stats
  • sys.dm_db_xtp_index_stats
  • sys.dm_db_xtp_memory_consumers
  • sys.dm_db_xtp_object_stats
  • sys.dm_db_xtp_transactions
  • sys.dm_db_xtp_table_memory_stats

XEvents

内存中OLTP引擎提供了xEvents来帮助监控和故障排除。您可以运行以下查询来查看当前可用的xEvents：

SELECT p.name, o.name, o.description
FROM sys.dm_xe_objects o JOIN sys.dm_xe_packages p
ON o.package_guid=p.guid
WHERE p.name = 'XtpEngine';
GO

 

性能计数器

内存中OLTP引擎提供了性能计数器来帮助监控和故障排除。您可以运行以下查询来查看当前可用的性能计数器：

SELECT OBJECT_NAME，counter_name
from sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE'XTP%';
GO

还提供了名为XTP使用内存的数据库计数器对象，用于跟踪在数据库级别的内存优化表的内存使用情况。

内存使用情况报表

要获得内存优化表和索引当前使用内存的实时报表，您可以运行SQL Server Management Studio中提供的报表。在对象资源管理器中，右键单击包含内存优化表的数据库的名称，选择报表|标准报表|内存优化对象的内存使用情况。您会看到类似图17的报表。

图17内存优化对象的内存使用情况报表

 

这个报表显示表的行和索引所使用的空间，以及少量由系统使用的空间。请记住，一旦创建了哈希索引，这些索引就会拥有分配给已声明的哈希桶数量的内存，因此这个报表将显示在插入任何行之前，这些索引的内存使用情况。对于非聚集索引，在添加行之前不会分配内存，并且内存需求将依赖于索引键的大小和行的数量。

内存需求

当运行内存中OLTP时，SQL Server需要配置足够的内存来保存所有的内存优化表。未能分配足够的内存会导致在需要额外内存的操作运行时事务失败。通常这会在INSERT或UPDATE操作时发生，但在一个内存优化的非聚集索引上的删除操作也有可能发生。正如上一节所介绍的，删除可能导致产生页合并，而且由于索引页永远不会被更新，合并操作将会分配新页。内存中OLTP的内存管理器与SQL Server的内存管理器完全集成，并在可能的情况下，通过更积极地清理旧行版本来对内存压力进行反应。

在预测内存优化表所需的内存数量时，一个经验法是，应该拥有数据占用量两倍的内存。除此之外，总内存需求还取决于工作负荷；如果由于OLTP操作有大量的数据修改，则需要更多的内存供给行版本使用。如果大量读取现有的数据，则可能需要更少的内存。

对于规划索引所需的空间，哈希索引则非常简单。每个桶需要8个字节，所以您可以计算桶的数量乘以8字节数。内存优化非聚集索引的大小取决于索引键的大小和表中行的数量。可以假设每个索引行是8个字节加上索引键的大小（假设为k字节），因此，适合于一个页面的最大行数将是8176/（K +8）。预计的行数除以这个结果将得到一个初步的估计。请记住，并不是所有的索引页都是8K，也不是所有的页都是全满的。由于页需要拆分和合并，创建出新的页，需要为这些页留出空间，直到垃圾收集进程将它们删除。

用资源调控器管理内存

SQL Server的资源调控器是一种让您可以主动管理内存的工具。与CTP2版本开始，数据库可以绑定到一个资源池上，您可以分配一定量的内存到这个池中。这个数据库中的内存优化表使用的内存不能超过这个量。有一个可分配内存的80%的固定限制，以确保系统在内存压力下仍然稳定。事实上，内存优化表及其索引占用的任何内存都是由资源调控器管理，除此之外，并没有其他类型的内存是由资源调控器管理。如果数据库没有显式映射到一个资源池，它会隐式地映射到默认池。

更多有关SQL Server资源调控器的详细信息，请参阅SQL Server 2008引入资源调控器时编写的扩展白皮书：http://view.officeapps.live.com/op/view.aspx?src=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2FD%2FB%2FD%2FDBDE7972-1EB9-470A-BA18-58849DB3EB3B%2FResourceGov.docx

SQL Server2012中资源调控器的更改，可以在这里读到：http://msdn.microsoft.com/en-us/library/jj573256.aspx

为内存中OLTP数据库创建一个内存池的第一步是指定MAX_MEMORY_PERCENT值。这个值指定了可以被分配给与这个池关联数据库中内存优化表的SQL Server内存的百分比。

例如：

CREATE RESOURCE POOL HkPool WITH (MAX_MEMORY_PERCENT=50);
ALTER RESOURCE GOVERNOR RECONFIGURE;

创建了资源池之后，需要使用存储过程sp_xtp_bind_db_resource_pool将希望管理的数据库绑定到各自的池中。注意，一个池可以包含多个数据库，但数据库在任何时间点只能与一个池关联。

以下是一个示例：

EXEC sp_xtp_bind_db_resource_pool 'HkDB', 'HkPool';

因为是在分配内存时，才将内存指派到一个资源池中，只是将资源池与一个数据库关联并不能转移已分配内存的指派。要做到这一点，您需要将数据库脱机，再把它重新联机。当数据被读入到内存优化表，内存将关联到新的资源池中。

例如：

ALTER DATABASE [HkDb] SET OFFLINE;
ALTER DATABASE [HkDb] SET ONLINE;

如果您想要移除一个数据库和一个资源池之间的绑定，则可以使用存储过程sp_xtp_unbind_db_resource_pool。例如，您可能希望将数据库移动到另一个资源池中，或者彻底删除资源池，来将其替换成其他的资源池。

EXEC sp_xtp_unbind_db_resource_pool 'HkPool';

 

通过分析，迁移和报表工具集（AMR）来帮助迁移

安装SQL Server2014（只要您已经选择安装了完整的管理工具集）之后，分析，迁移和报表工具集（Analyze, Migrate and Report , AMR）可用于提供关于可能要考虑将哪些表和存储过程迁移到内存中OLTP的建议。

该工具集的分析和报表方面使用一组新的数据收集器与管理数据仓库相结合，以获取工作负荷瓶颈和性能指标。这些数据可以用来生成报表，可通过右击管理数据仓库的数据库并选择报表|管理数据仓库来使用。然后有一个选项可以选择“事务性能分析概述”。

其中一个报表包含了如果转换成内存优化表，哪些表可能提供最大性能增益的建议。该报表也将基于表中同时使用了多少不支持的功能，说明进行转换将需要多少代价。另一份报表包含为了内存优化表的使用，哪些存储过程可能会从转换为本机编译的存储过程中受益的建议。

一旦关键表确定，该工具集的迁移方面包含的内存优化顾问可以帮助您迁移它们。在右击基于磁盘的表弹出的上下文菜单中，点击“内存优化顾问”。这个顾问将确定对象中已有的不兼容性，并生成概述了这些不兼容问题的报表，并且还能对有限的一组表进行有指导的迁移。

内存优化表可以由解释型的Transact-SQL和本地编译存储过程进行访问，并且内存优化表可以与基于磁盘的表在同一个查询中使用，这就意味着迁移到一个内存中OLTP的环境可以逐步迭代地来完成。根据管理数据仓库报表中提供的建议，您可以开始将表转换成内存优化表，一次一个，可以从内存中优化结构中受益最多的表开始。当您开始看到转换成内存优化表的好处，您可以继续转换越来越多的表，但访问它们使用常规的Transact-SQL接口，如果有的话，应用程序只需要极少的更改。

一旦您的表已经完成转换，您就可以开始计划将代码重写成本地编译的存储过程，可以再次从数据仓库报表表明将提供最大收益的那些存储过程开始。利用本机编译顾问（另一种工具，可以通过右击Management Studio中的存储过程找到）来确定Transact-SQL中的不兼容性，可以帮助迁移这些对象。

 

总结

SQL Server的内存中OLTP特性提供了创建和使用内存中优化表的能力，并可以极其有效的对其进行管理，为 OLTP工作负荷提供了性能优化。内存优化表可采用真正的多版本乐观并发控制进行访问，在处理过程中不需要锁或闩锁。所有内存中OLTP的内存优化表必须至少有一个索引，并且所有的访问都需要通过索引。内存中OLTP的内存优化表可以与基于磁盘的表在同一个事务中被引用，只是稍有些限制。本机编译的存储过程是访问内存优化表和高效业务逻辑算法最快的方式。

 

---------------------------全文完-------------------------------

SQL Server 内存中OLTP内部机制概述（一）

SQL Server 内存中OLTP内部机制概述（二）

SQL Server 内存中OLTP内部机制概述（三）

SQL Server 内存中OLTP内部机制概述（四）

 

posted @ 2014-11-17 13:28  hukn  阅读(1526)  评论(0)  编辑  收藏  举报