快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

SQL Server中的内存优化表初学者指南

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

Microsoft在SQL Server 2014中添加了内存中OLTP(又名内存优化表)功能。对此类型表的操作不需要锁,因此消除了阻塞和死锁。在本文中,Monica Rathbun演示了如何开始使用内存优化表。

有时,当我尝试了解某个概念时,我的大脑会遮挡所有与它有关的事物。谈论使用“ 内存中”概念的任何内容,有时都会对我造成影响。需要特别注意的是,内存是SQL Server中一系列功能的行销术语,这些功能具有共同的行为,但并非固有相关。在本文中,我将解释一些与SQL Server相关的内存概念,从深入研究内存优化表或内存OLTP开始。我已经写了关于Columnstore的文章,与In-Memory OLTP相比,它的用例有很大不同,您可以在这里找到。Columnstore是内存中概念的完美示例,这使我花了一些时间来思考。

什么是内存优化表?

从SQL Server 2014开始的内存优化表就是一个简单的表,该表具有两个副本,一个在活动内存中,一个在磁盘上持久(无论包含数据还是仅包含架构),我将在后面解释。由于内存是在重新启动SQL Services时刷新的,因此SQL Server保留了可恢复的表的物理副本。即使表有两个副本,内存副本也是完全透明的,并且对您隐藏。

使用这些内存表的附加好处是什么?

在查看SQL Server选项或功能时,这总是我要问的问题。对于内存表,这是SQL Server处理闩锁和锁的方式。根据Microsoft的说法,该引擎为此使用了一种乐观的方法,这意味着它不会在任何版本的更新数据行上放置锁或闩锁,这与普通表有很大不同。正是这种机制减少了争用并允许事务以指数级速度更快地处理。In-Memory使用行版本而不是锁,将原始行保留到提交事务之后。与“ 读取提交的快照隔离(RCSI)” 非常相似,这允许其他事务读取原始行,同时更新新的行版本。内存结构化版本是无页面的 并针对活动内存内部的速度进行了优化,从而根据工作负载对性能产生重大影响。

SQL Server还更改了这些表的日志记录。而不是完全记录日志,表的磁盘和内存版本(行版本)的这种双重性使得更少的日志记录。SQL Server可以使用之前和之后的版本来获取通常从日志文件中获取的信息。在SQL Server 2019中,相同的概念适用于新的加速数据恢复(ADR)方法进行日志记录和恢复。

最后,另一个额外的好处是DURABILITY创建表的部分示例中显示的选项。使用SCHEMA_ONLY可以是避免使用#TEMP表并添加更有效的方法来处理临时数据的好方法,尤其是对于较大的表。您可以在此处阅读更多内容。

要考虑的事情

现在听起来一切都很好,所以您会认为每个人都会将其添加到所有表中,但是,就像所有SQL Server选项一样,这并不意味着所有环境都适用。在实现“内存表”之前,您需要考虑一些事项。首先,在考虑之前,请考虑内存量和该内存的配置。您必须在SQL Server中正确设置该设置,并根据增加的内存使用情况进行调整,这可能意味着在启动之前向您的服务器添加更多的内存。其次,要知道,与Columnstore索引一样,这些表并不适用于所有内容。这些表针对高容量WRITE(而非主要用于读取的数据仓库)进行了优化。最后,要考虑不支持的功能和语法的完整列表,请务必查看以下文档只是其中一些。

请记住,内存表中不支持的功能。

  • 复写
  • 镜射
  • 链接服务器
  • 批量记录
  • DDL触发器
  • 最小记录
  • 更改数据捕获
  • 资料压缩

不支持T-SQL

  • 外键(只能引用其他内存优化表PK)
  • 更改表
  • 创建索引
  • 截断表
  • DBCC检查表
  • DBCC CHECKDB

创建内存优化表

具有“ In-Memory”表的关键是在首次创建表时在create语句上使用关键字“ MEMORY-OPTIMIZED”。注意,不能更改表来优化现有的一个内存;您将需要重新创建表并加载数据,以便在现有表上利用此选项。从下面可以看到,只需配置几个设置即可使此工作正常进行。

第一步是确保您的兼容性级别> = 130。运行此查询以找出当前的兼容性级别:

如果数据库处于较低级别,则需要对其进行更改。

接下来,您必须更改数据库以通过启用MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT设置来利用内存中OLTP 。

最后,您的数据库将需要添加一个内存优化文件组。

请注意,一个数据库可能只有一个内存优化文件组,而AdventureWorks2016CTP3数据库已经有一个,因此在运行该语句时可能会看到错误。

以下命令将文件创建到新文件组中。

现在创建一个表

创建表后,表属性显示Memory Optimized = TRUEDurability = SchemaAndData,这使验证表的工作变得非常简单。

SQL Server中的内存优化表初学者指南

在表上插入和选择在语法上与任何其他常规表相同,但是在内部,相差很大。除了创建表之外,在包括添加或删除列的操作中,其结构化行为基本相同。现在,对这些表的一个警告是您不能以相同的方式CREATEDROP索引。您必须使用ADD / DROP Index来完成此操作,相信我,我已经尝试过。稍后在本文中将对这些表建立索引。

还记得DURABILITY我之前简短提到的选项吗?这个很重要。上面的示例将其设置为SCHEMA_AND_DATA意味着数据库脱机后,架构和数据都将保留在磁盘上。如果选择SCHEMA_ONLY,这意味着将仅保留结构,并删除数据。注意这一点非常重要,因为使用不当会导致数据丢失。

如您所见,In-Memory表并不像我的大脑想要制作的那样复杂。这是一个相对简单的概念,仅包含行版本控制和表的两个副本。将概念分解成各个部分后,它实际上会使它更容易理解。

我将哪些表放入内存中?

使用称为内存优化顾问(MOA)的工具,可以轻松确定哪些表可以从内存中受益。这是SQL Server Management Studio(SSMS)中内置的工具,它将通知您哪些表可以使用内存中OLTP功能而受益,哪些表可能具有不受支持的功能。一旦确定,MOA将帮助您迁移要优化的表和数据。

要查看其工作原理,我将引导您在用于AdventureWorks2016CTP3演示的桌子上使用它。由于这是一个较小的表,并且不会产生大量写入操作,所以这不是一个好用例,但是,为简单起见,我在演示中使用了它。

首先,右键单击Sales.OrderTracking表,然后选择Memory Optimization Advisor

 

SQL Server中的内存优化表初学者指南

这将打开向导。单击下一步继续。

SQL Server中的内存优化表初学者指南

接下来,它将验证您的表是否能够迁移。它查找诸如不受支持的数据类型,稀疏列,种子列,外键,约束和复制之类的内容。如果任何项目失败,则必须进行更改和/或删除这些功能,才能将表移至内存优化表。

SQL Server中的内存优化表初学者指南

接下来,它将跳过一些警告。这些项目与“验证”屏幕中的项目不同,它们不会停止迁移过程,但是会导致该选项的行为失败或异常,因此请注意这一点。Microsoft在此更进一步,并提供了指向更多信息的链接,以便您可以就是否继续进行迁移做出明智的决定。

SQL Server中的内存优化表初学者指南

下面的下一个屏幕非常重要,因为它使您可以选择迁移到内存优化表的选项。我想在下一个屏幕截图中指出一些事情。

SQL Server中的内存优化表初学者指南

首先,在红色框中,您将找到文件组名称的要求。内存优化表在迁移时必须具有特殊的文件组。这是允许您重命名原始表并将其保留在原处的要求,从而避免了命名冲突。您还将在此屏幕中注意到,您可以选择重命名原始表的内容。

接下来,在“紫色”框中,您将看到用于将数据也移动到新表的选项。如果不选择此选项,则将创建没有行的表,并且必须手动移动数据。

在“黄色”框中的下一个是“创建表”选项,该选项与本文前面提到的等效DURABILITY= SCHEMA_ONLY或相同SCHEMA_AND_DATA。如果您选中此框,那么您将没有任何持久性,并且由于重新启动SQL Services或重新启动等原因,数据将消失(如果您使用的是TEMP,则可能是您想要的表) TABLE,不需要数据)。请务必注意这些选项,因为默认情况下未选中此选项。如果不确定要选择哪个选项,请不要选中此框。这样可以确保数据持久。单击下一步

请记住,这是为迁移表而制作的,因此新的优化表不能具有相同的主键名称。下一个屏幕将帮助重命名该键以及设置索引和存储桶计数。我将在下面解释存储桶数。

SQL Server中的内存优化表初学者指南

请注意,在上面的屏幕中,它为您提供了一个空间来重命名主键并创建新索引。如您所知,主键是索引,因此您必须进行设置。第二个索引有两个选项。您可以使用NONCLUSTERED INDEX,该索引非常适合具有许多范围查询且需要排序顺序的表,也可以使用NONCLUSTERED HASH索引,该索引更适合那些直接查找。如果选择后者,则还需要提供“ 桶数”的值。存储桶计数会极大地影响表的性能,因此您应该阅读有关如何正确设置此值的文档。在上述情况下,我将其保留为预先填充的值,然后选择Next

该表具有现有索引,因此下一步是设置要转换的索引。如果您没有任何现有索引,那么将跳过此部分。

SQL Server中的内存优化表初学者指南

请注意左侧的两个索引迁移选项。这意味着有两个要迁移的索引。

SQL Server中的内存优化表初学者指南

出现的下一个屏幕只是设置中选择的所有迁移选项的摘要。通过选择迁移,您会将表及其数据迁移为内存优化表,因此请谨慎操作。这可能是单击“ 脚本”按钮并将其编写为以后使用的好时机。请记住,我已经为此数据库提供了内存优化的文件组,因此没有为我创建一个文件组。如果尚不存在,您将在“摘要”屏幕中看到其创建。

SQL Server中的内存优化表初学者指南

如下所示,迁移成功。重命名旧表后,将创建一个新表,并复制数据。

SQL Server中的内存优化表初学者指南

这是结果。

SQL Server中的内存优化表初学者指南

 

如果我现在编写新表的脚本,您会看到它指出它是内存优化表,并且具有适当的存储区计数。另请注意,我没有选中会使我的桌子SCHEMA_ONLY坚固耐用的复选框,您会在DURABILTIY = SCHEMA_AND_DATA下面看到这一点。

SQL Server中的内存优化表初学者指南

如您所见,Memory Optimization Advisor使识别和迁移表到内存中优化表变得很简单。我强烈建议在尝试转换数据库中的任何表之前测试此过程。并非所有工作负载都适合使用此功能,因此请确保在实施之前进行尽职调查。当您准备好实施时,此工具可以帮助您简化该过程。

既然我已经说明了内存中的表和迁移到内存中的表,下一步就是查看索引,以及如何创建索引以及它们如何在这些表中工作。您可以想象索引对于这些类型的表来说称为内存优化索引是不同的,因此,请看一下它们与常规表有何不同。

在深入探讨此主题之前,非常重要的一点是要注意最大的差异。

首先,如果您正在运行SQL Server 2014,则必须在创建或迁移表时创建内存优化索引。您不能在不删除并重新创建表的情况下向现有表添加索引。2016年之后,您现在可以选择,并且此限制已被删除。

其次,在2017年之前,每个表(包括主键)只能有八个索引。请记住,每个表必须有一个主键来强制执行辅助副本,以使模式持久性最小。这意味着您实际上只能添加七个其他索引,因此请确保了解您的工作负载并相应地计划索引编制。从Microsoft开始,从SQL Server 2017(14.x)开始,在Azure SQL数据库中,不再对特定于内存优化的表和表类型的索引数进行限制。

第三,内存优化索引仅存在于内存中,它们不持久存储在磁盘上,也不记录在事务日志中。因此,这意味着它们也会在数据库启动时重新创建,并且在重新构建时确实会降低性能。

接下来,没有诸如针对内存中表的键查找之类的事情,因为所有索引本质上都是覆盖索引。索引使用指向实际行的指针来获取所需的字段,而不是像物理表那样使用主键。因此,它们在返回正确的数据时效率更高。

最后,这些索引也不存在碎片的情况,因为这些索引不会从磁盘读取。与磁盘索引不同,这些索引没有固定的页面长度。在磁盘索引上,使用B树中的物理页面结构,确定应该填充多少页面是填充因子的作用。由于这不是必需的,因此不存在碎片。

好了,现在您已经完成了所有这些工作,请查看可以创建的索引类型,并了解它们是什么以及如何创建它们。

非聚集HASH索引

该索引用于访问表的内存中版本,称为哈希。这些对于单例查找而不是值范围的谓词非常有用。这些是为寻求相等值而优化的。例如,WHERE Name =’Joe’。确定索引中要包括哪些内容时要记住的一点是:如果查询有两个或多个字段作为谓词,而索引仅包含这些字段之一,则将进行扫描。它不会寻求包含的那个领域。了解您的工作负载并在适当的字段(或其组合)上建立索引很重要。鉴于此内存中OLTP主要专注于繁重的插入/更新工作负载,而读取的工作量则较少,因此应少关注此问题。

这些类型的索引经过高度优化,并且如果索引中有很多重复的值,它们将无法很好地工作,您的值越独特,越能获得更好的索引性能。了解您的数据始终很重要。

当涉及这些索引时,了解您的内存消耗就很重要。哈希索引类型是固定长度的,并且消耗创建时确定的固定数量的内存。内存量由“存储桶计数”值确定。确保此值尽可能准确非常重要。正确确定此数字的大小可能会影响您的表现。微软认为,这个数字太低了,“会严重影响数据库的工作负载性能和恢复时间。” 同时,您可以在docs.microsoft上了解有关哈希索引的更多信息。

使用T-SQL(两种方法都得出相同的结果)

示例一(注意索引在表字段之后)

示例二(注意索引在字段之后)

 

非聚集索引

非聚集索引也用于访问表的内存版本,但是,它们针对范围值(例如小于和等于,不等式谓词和排序顺序)进行了优化。例如,“ 20190101”和“ 20191231”之间的“ WHERE DATE”以及“> 123123”中的“ WHERE DATE”。这些索引不需要存储区计数或固定的内存量。这些索引消耗的内存由实际的行数和索引的键列的大小确定,这使其创建起来更简单。

而且,与散列索引相反,散列索引需要谓词所需的所有字段都成为索引的一部分才能进行查找,而哈希索引则不需要。如果您的谓词具有多个字段,并且您的索引具有该字段中的一个作为其前导索引键值,那么您仍然可以进行查找。

使用T-SQL(两种方法都得出相同的结果)

示例一(注意索引在表字段之后)

示例二(注意索引在字段之后)

确定使用哪种索引类型可能很棘手,但是Microsoft在下表中提供了很好的指南。

SQL Server中的内存优化表初学者指南

结论

如您所见,与普通磁盘索引相比,In-Memory表索引(内存优化索引)的工作方式存在一些关键差异。与其他任何表设计一样,在着手创建或迁移到内存优化表之前,请考虑索引需求,这一点很重要。您会很高兴的。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638