sql oltp_SQL Server 2016中的内存中OLTP增强功能

最新推荐文章于 2022-08-09 11:19:01 发布

culuo4781

最新推荐文章于 2022-08-09 11:19:01 发布

阅读量376

点赞数

文章标签：数据库大数据编程语言 python linux

原文链接：https://www.sqlshack.com/memory-oltp-enhancements-sql-server-2016/

版权

sql oltp

SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.

SQL Server内存中OLTP（在SQL Server 2014中引入时也称为Hekaton）使我们能够将特定的数据库表和合适的存储过程移到内存中，并将存储过程编译为本机x86代码。因此，您可以轻松地直接从内存中以最佳性能和最小数据访问延迟查询这些数据库对象。除此之外，SQL Server引擎将不再使用旧的闩锁和锁定机制来控制数据访问并发。相反，将使用高性能的行版本控制机制来控制并发性。由于直接从内存中读取数据，因此这种乐观的并发机制比普通的基于磁盘的处理快5到20倍。

In order to help us in the In-Memory OLTP migration decision, Microsoft provides the Analysis Migrate Report tool, also known as AMR tool, that analyzes a selected database objects and workload, then choose the suited tables and stored procedures that can be candidates for the In-Memory OLTP migration process.

为了帮助我们做出内存中OLTP迁移决定，Microsoft提供了“ 分析迁移报告”工具（也称为AMR工具），该工具可以分析选定的数据库对象和工作负荷，然后选择适合的表和存储过程作为候选对象内存中OLTP迁移过程。

Like any new feature introduced at the first time, In-Memory OLTP comes with a number of limitations in SQL Server 2014, such as the supported data types and features. In SQL Server 2016, many of these limitations and restrictions are eliminated, making the In-Memory OLTP feature more powerful and useful, allowing more systems to take advantage of this improved technology. In this article, we will go through some of these key improvements to In-Memory OLTP.

与首次引入的任何新功能一样，In-Memory OLTP在SQL Server 2014中也具有许多限制，例如受支持的数据类型和功能。在SQL Server 2016中，消除了许多这些限制，使内存中OLTP功能更强大，更有用，从而使更多系统可以利用此改进技术。在本文中，我们将对内存中OLTP进行一些关键的改进。

整理支持 (Collation Support)

To be able to use In-Memory OLTP in SQL Server 2014, you should use the BIN2 collation type for any character column that is used as an index key. Also, in any comparison between string values in the Natively Complied Stored Procedures, you should use the BIN2 collation only.

为了能够在SQL Server 2014中使用内存中OLTP，您应该对用作索引键的任何字符列使用BIN2排序规则类型。另外，在本机编译存储过程中的字符串值之间进行任何比较时，都应仅使用BIN2归类。

SQL Server 2016 version eliminates this collation limitation, where you can use any collation type for the character columns used as index key columns or the string values comparison in the Natively Compiled Stored Procedures. You may expect performance degradation when using a non-BIN2 collation for the character columns.

SQL Server 2016版本消除了此排序规则限制，您可以在其中将任何排序规则类型用作用作索引键列的字符列或“本机编译的存储过程”中的字符串值比较。将非BIN2归类用于字符列时，可能会导致性能下降。

Let us see how it works practically. A new testing database, InMemorySQLShackDemo, will be created, a new non-default filegroup will be added to the database to store the memory optimized data, a data file will be created in the new filegroup and finally the In-Memory OLTP feature will be enabled on that database, by running the below T-SQL script twice, the first time on SQL Server 2014 instance and the second time on SQL Server 2016 instance:

让我们看看它实际上是如何工作的。将创建一个新的测试数据库InMemorySQLShackDemo，将一个新的非默认文件组添加到数据库中以存储内存优化数据，在该新文件组中创建一个数据文件，最后将在内存中使用OLTP功能通过两次运行以下T-SQL脚本在该数据库上启用，第一次在SQL Server 2014实例上运行，第二次在SQL Server 2016实例上运行：

 
USE master
GO
CREATE DATABASE [InMemorySQLShackDemo] ON  PRIMARY 
( NAME = N'InMemorySQLShackDemo', FILENAME = N'D:\Data\InMemorySQLShackDemo.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'InMemorySQLShackDemo_log', FILENAME = N'D:\Data\InMemorySQLShackDemo_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
ALTER DATABASE InMemorySQLShackDemo ADD FILEGROUP InMemorySQLShackDemo_FG CONTAINS MEMORY_OPTIMIZED_DATA   
ALTER DATABASE InMemorySQLShackDemo ADD FILE (name='InMemorySQLShackDemoDF', filename='D:\Data\InMemorySQLShackDemo') TO FILEGROUP InMemorySQLShackDemo_FG   
ALTER DATABASE InMemorySQLShackDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO

The database is able to host the In-Memory data now. We will try to create a Memory-Optimized table on that database, with the EmpAddress column using the ARABIC collation type. Applying the below CREATE TABLE T-SQL statement on a SQL Server 2014 instance:

该数据库现在可以托管内存中的数据。我们将尝试在该数据库上创建内存优化表，并使用ARABIC排序规则类型使用EmpAddress列。将以下CREATE TABLE T-SQL语句应用于SQL Server 2014实例：

 
USE [InMemorySQLShackDemo]
GO
CREATE TABLE [dbo].[InMemoryDemo](
[EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID)
	 WITH (BUCKET_COUNT = 100000),
	[EmpName] [varchar](50) NOT NULL,
	[EmpDEPID] [int] NOT NULL,
       [EmpBirthDay] [datetime] NULL,
[EmpAddress] [varchar](50) COLLATE ARABIC_CI_AS NOT NULL
	
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

The CREATE TABLE statement will fail, showing that we are using a collation with code page different from the BIN2 collation type as shown in the error message below:

CREATE TABLE语句将失败，表明我们使用的代码页与BIN2排序规则类型不同的排序规则，如以下错误消息所示：

Trying the same CREATE TABLE statement, but this time using SQL Server 2016 instance, the table will be created successfully on that database with a non BIN2 collation type as shown clearly below:

尝试使用相同的CREATE TABLE语句，但是这次使用SQL Server 2016实例，将使用非BIN2归类类型在该数据库上成功创建表，如下所示：

新标准报告 (New Standard Reports)

SQL Server 2016 provides us with a new built-in standard report, which works at the database level, that shows useful information that helps us identifying database performance bottlenecks and assists in any In-Memory OLTP migration decision. The new standard report, Transaction Performance Analysis Overview, can be viewed by right-clicking on the database name, choose Reports, Standard Reports and then select the report name from the standard reports list as shown below:

SQL Server 2016为我们提供了一个新的内置标准报告，该报告在数据库级别工作，该报告显示了有用的信息，这些信息可帮助我们确定数据库性能瓶颈并协助做出任何内存中OLTP迁移决定。可以通过右键单击数据库名称，选择Reports ， Standard Reports ，然后从标准报告列表中选择报告名称来查看新的标准报告Transaction Performance Analysis Overview ，如下所示：

The Transaction Performance Analysis Overview report consists of two main parts: The Table Analysis for the database tables and the Stored Procedure Analysis for the database stored procedures as shown below:

事务性能分析概述报告由两个主要部分组成：数据库表的表分析和数据库存储过程的存储过程分析 ，如下所示：

To get a meaningful report, you have to run a workload on the selected database. If you choose the Tables Analysis part, a new sub-report will be opened that contains the Recommended Tables Based on Usage, that are suited to be migrated to In-Memory OLTP technology. Choosing a specific table, the new screen will show you the scan and contention statistics for that selected table. For the Stored Procedure Analysis, a list of stored procedures that are suited to be migrated to the In-Memory OLTP technology will be shown in the shape of Recommended Stored Procedures Based on Usage. Selecting a specific stored procedure, a details execution statistics about that stored procedure will be displayed.

为了获得有意义的报告，您必须在所选数据库上运行工作负载。如果选择“表分析”部分，将打开一个新的子报告，其中包含适合于迁移到内存中OLTP技术的“ 基于使用情况的推荐表” 。选择特定的表后，新屏幕将显示该表的扫描和争用统计信息。对于存储过程分析，将以“ 基于使用情况的建议存储过程”的形式显示适合迁移到内存中OLTP技术的存储过程的列表 。选择一个特定的存储过程，将显示有关该存储过程的详细执行统计信息。

ALTER TABLE操作支持 (ALTER TABLE Operations Support)

Schema changes are performed on the table using the ALTER TABLE T-SQL statement. In SQL Server 2014, it is not possible to modify the Memory-Optimized table’s schema using the ALTER TABLE statement after creating that table. In order to modify a Memory-Optimized table’s schema or rebuild an index on it, you have to drop the table and recreate it again. Starting from SQL Server 2016, this restriction is no longer available, you can easily use the ALTER TABLE statement to make schema changes to the already created Memory-Optimized table.

使用ALTER TABLE T-SQL语句在表上执行模式更改。在SQL Server 2014中，创建表后，无法使用ALTER TABLE语句修改内存优化表的架构。为了修改内存优化表的架构或在其上重建索引，您必须删除该表并重新创建。从SQL Server 2016开始，此限制不再可用，您可以轻松地使用ALTER TABLE语句对已创建的“内存优化”表进行架构更改。

The ALTER TABLE operations on the Memory-Optimized tables are offline. This means that the table that you are changing its schema is not available for the queries during the ALTER TABLE operation. All schema modification operations require more memory temporarily as a part of the ALTER TABLE operation, as a new copy of the table will be created under the hood. Most of the operations performed using the ALTER TABLE T-SQL statements run in parallel and are log-optimized. Which means that only the metadata changes will be written to the transaction log, rather than writing a complete copy of the table to the transaction log.

内存优化表上的ALTER TABLE操作处于脱机状态。这意味着您正在更改其架构的表不可用于ALTER TABLE操作期间的查询。作为ALTER TABLE操作的一部分，所有架构修改操作都会临时需要更多内存，因为将在后台创建表的新副本。使用ALTER TABLE T-SQL语句执行的大多数操作都是并行运行的，并且已对日志进行了优化。这意味着仅将元数据更改写入事务日志，而不是将表的完整副本写入事务日志。

Recall the previously created InMemorySQLShackDemo testing database with the In-Memory OLTP feature enabled on it, and the created InMemoryDemo table that is considered as Memory-Optimized table. If you try to rebuild the clustered index on that Memory-Optimized table hosted in SQL Server 2014 instance using the ALTER TABLE T-SQL statement below:

调用先前创建的启用了内存中OLTP功能的InMemorySQLShackDemo测试数据库，以及创建的被视为“内存优化”表的InMemoryDemo表。如果您尝试使用下面的ALTER TABLE T-SQL语句在SQL Server 2014实例中托管的内存优化表上重建聚集索引：

 
ALTER TABLE [InMemoryDemo] ALTER INDEX [PK_IM_EmpID] REBUILD WITH  (BUCKET_COUNT=200000 )

The query will fail as the ALTER TABLE statement on the Memory-Optimized table is not supported in the SQL Server 2014 version, as shown in the error message below:

由于SQL Server 2014版本不支持内存优化表上的ALTER TABLE语句，因此查询将失败，如以下错误消息所示：

Running the same ALTER TABLE statement but this time on the table hosted in SQL Server 2016 version:

在SQL Server 2016版本托管的表上运行相同的ALTER TABLE语句，但这一次：

 
ALTER TABLE InMemoryDemo ALTER INDEX [PK_IM_EmpID] REBUILD WITH  (BUCKET_COUNT=200000 )

The index will be rebuilt successfully without any error as shown below:

索引将成功重建，没有任何错误，如下所示：

Again, if we try to change the type of the EmpDEPID column from the Memory-Optimized table to be BIGINT by running the below ALTER TABLE…ALTER COLUMN T-SQL statement on the table hosted in SQL Server 2014 version:

同样，如果我们尝试通过在SQL Server 2014版本托管的表上运行以下ALTER TABLE…ALTER COLUMN T-SQL语句，将“内存优化”表中EmpDEPID列的类型更改为BIGINT，请执行以下操作：

 
ALTER TABLE InMemoryDemo ALTER COLUMN [EmpDEPID] [bigint] NOT NULL

The query will fail, showing that the ALTER TABLE T-SQL statement is not supported with the Memory-Optimized tables as shown in the error message below:

查询将失败，表明内存优化表不支持ALTER TABLE T-SQL语句，如以下错误消息所示：

Trying the same ALTER TABLE T-SQL statement on the table hosted in SQL Server 2016 version, the column will be modified successfully with no error as shown below:

在SQL Server 2016版本中托管的表上尝试使用相同的ALTER TABLE T-SQL语句，该列将成功修改，没有错误，如下所示：

The below changes can be also performed on the table hosted in SQL Server 2016 instance successfully using the ALTER TABLE T-SQL statement:

使用ALTER TABLE T-SQL语句，也可以在SQL Server 2016实例中托管的表上成功执行以下更改：

Add a new column to the Memory-Optimized table:

在“内存优化”表中添加新列：
Add a new index to the Memory-Optimized table:
将新索引添加到“内存优化”表中：
Drop an existing index on the Memory-Optimized table:

在“内存优化”表上删除现有索引：
Drop an existing column on the Memory-Optimized table:

在“内存优化”表上删除一个现有列：

外键约束支持 (Foreign Key Constraint Support)

In SQL Server 2014, it is not allowed to define a foreign key constraint in a Memory-Optimized table that references another table. Assume that we need to create a foreign key constraint in the InMemoryDemo table on the EmpDEPID column that references the ID column in the Department Memory-Optimized table. The Department table is created on both the SQL Server 2014 and SQL Server 2016 instances using the CREATE TABLE T-SQL statement below:

在SQL Server 2014中，不允许在引用另一个表的“内存优化”表中定义外键约束。假设我们需要在EmpDEPID列的InMemoryDemo表中创建一个外键约束，该约束引用了Department Memory-Optimized表中的ID列。使用下面的CREATE TABLE T-SQL语句在SQL Server 2014和SQL Server 2016实例上创建Department表：

 
CREATE TABLE [dbo].[Departments](
	[ID] [int] NOT NULL CONSTRAINT PK_IM_EmpDep PRIMARY KEY NONCLUSTERED HASH (ID)
	 WITH (BUCKET_COUNT = 100000),
	[DepName] [varchar](50) NOT NULL,
	) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

To create the foreign key constraint on the SQL Server 2014 and SQL Server 2016 instances, we will drop the InMemoryDemo table and create it again with the new foreign key constraint, as the ALTER TABLE statement is not supported in SQL Server 2014 as mentioned previously. This can be achieved using the DROP TABLE and CREATE TABLE T-SQL statements shown below:

若要在SQL Server 2014和SQL Server 2016实例上创建外键约束，我们将删除InMemoryDemo表，并使用新的外键约束再次创建该表，因为如上所述，SQL Server 2014不支持ALTER TABLE语句。这可以通过使用如下所示的DROP TABLE和CREATE TABLE T-SQL语句来实现：

 
USE [InMemorySQLShackDemo]
GO
DROP TABLE [dbo].[InMemoryDemo]
GO
CREATE TABLE [dbo].[InMemoryDemo](
[EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID)
	 WITH (BUCKET_COUNT = 100000),
	[EmpName] [varchar](50) NOT NULL,
	[EmpDEPID] [int] NOT NULL CONSTRAINT FK_EmpDepID REFERENCES DBO.Departments (ID),
       [EmpBirthDay] [datetime] NULL,
[EmpAddress] [varchar](50)
	
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Trying to create the table with the foreign key constraint in the SQL Server 2014 instance, the statement will fail showing that the Foreign Key is not supported with the Memory-Optimized tables in that SQL Server version as in the below error message:

尝试在SQL Server 2014实例中创建具有外键约束的表时，该语句将失败，表明该SQL Server版本中的内存优化表不支持外键，如以下错误消息所示：

Creating the table with the foreign key constraint using the same T-SQL statements but this time in the SQL Server 2016 instance, the table will be created successfully with the foreign key constraint without any error as shown below:

使用相同的T-SQL语句创建具有外键约束的表，但是这次在SQL Server 2016实例中，将成功使用外键约束创建表，且没有任何错误，如下所示：

The previous example shows that you can create a foreign key constraint on a Memory-Optimized table that references another Memory-Optimized table in SQL Server 2016. But can we create a foreign key constraint in a Memory-Optimized table that references a disk-based table? To check that, we will create the same previous Department table as a disk-based table using the CREATE TABLE T-SQL statement below:

上一示例显示您可以在引用SQL Server 2016中另一个内存优化表的内存优化表上创建外键约束。但是我们可以在引用基于磁盘的内存优化表中创建外键约束。表？要进行检查，我们将使用下面的CREATE TABLE T-SQL语句创建与基于磁盘的表相同的先前Department表：

 
USE [InMemorySQLShackDemo]
GO
CREATE TABLE [dbo].[Departments](
ID int,
DepName varchar(50)
)

If we try to drop and create the previous InMemoryDemo Memory-Optimized table with the foreign key that references the Department disk-based table, the query will fail in SQL Server 2016 too, showing that you can’t create a foreign key constraint in a Memory-Optimized to reference a disk-based table and vise verse as shown in the error message below:

如果我们尝试使用引用基于Department磁盘的表的外键删除并创建以前的InMemoryDemo内存优化表，则查询也会在SQL Server 2016中失败，这表明您无法在Windows Server 2008中创建外键约束。内存经过优化，可以引用基于磁盘的表和版本，如以下错误消息所示：

检查/唯一约束支持 (Check / Unique Constraint Support)

Another example for the In-Memory OLTP feature limitation in SQL Server 2014 is the ability to create a check or unique keys in the Memory-Optimized table. Assume that we need to add a constraint on the InMemoryDemo Memory-Optimized table to check that the EmpDEPID is always positive. This can be achieved in the SQL Server 2014 instance using the T-SQL script below that drops the table and create it again with the required check constraint, as the ALTER TABLE T-SQL statement is not supported in SQL Server 2014 version as described previously:

SQL Server 2014中内存中OLTP功能限制的另一个示例是能够在“内存优化”表中创建检查或唯一键的功能。假设我们需要在InMemoryDemo内存优化表上添加一个约束，以检查EmpDEPID始终为正。这可以在SQL Server 2014实例中使用下面的T-SQL脚本来实现，该脚本将删除表并使用所需的检查约束再次创建它，因为如上所述，SQL Server 2014版本不支持ALTER TABLE T-SQL语句：

 
USE [InMemorySQLShackDemo]
GO
DROP TABLE [dbo].[InMemoryDemo]
GO
CREATE TABLE [dbo].[InMemoryDemo](
[EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID)
	 WITH (BUCKET_COUNT = 100000),
	[EmpName] [varchar](50) NOT NULL,
	[EmpDEPID] [int] NOT NULL CONSTRAINT CK_EmpDepID CHECK ([EmpDEPID]>0),
       [EmpBirthDay] [datetime] NULL,
[EmpAddress] [varchar](50)
	
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Running the previous script, the statement will fail, showing that the CHECK constraint is not supported with the Memory-Optimized tables in SQL Server 2014 as below:

运行先前的脚本，该语句将失败，表明SQL Server 2014中的内存优化表不支持CHECK约束，如下所示：

The same T-SQL script will run successfully with no error in SQL Server 2016, as the check constraint is supported with the Memory-Optimized tables in that SQL Server version as you can see in the below snapshot:

您可以在以下快照中看到该SQL Server版本中的内存优化表支持检查约束，因此相同的T-SQL脚本将在SQL Server 2016中成功运行且没有错误。

可空列支持的索引 (Indexes on Nullable Columns Support)

SQL Server 2014 In-Memory OLTP engine doesn’t support creating an index on a column that allows NULL values. If you try to drop the InMemoryDemo Memory-Optimized table and create it again with the new index on the EmpBirthday Nullable column in SQL Server 2014 instance using the T-SQL statement below:

SQL Server 2014内存中OLTP引擎不支持在允许NULL值的列上创建索引。如果您尝试删除InMemoryDemo内存优化表并使用下面的T-SQL语句在SQL Server 2014实例的EmpBirthday Nullable列上使用新索引再次创建它：

 
USE [InMemorySQLShackDemo]
GO
DROP TABLE [dbo].[InMemoryDemo]
 
CREATE TABLE [dbo].[InMemoryDemo](
[EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID)
	 WITH (BUCKET_COUNT = 100000),
	[EmpName] [varchar](50) NOT NULL,
	[EmpDEPID] [int] NOT NULL,
       [EmpBirthDay] [datetime] NULL,
[EmpAddress] [varchar](50),
INDEX IX_EmpBirthDa NONCLUSTERED ([EmpBirthDay]
	
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

The script will fail, showing that it is not allowed to create an index on a nullable column in a Memory-optimized table, as in the error message below:

该脚本将失败，表明不允许在内存优化表中的可为空列上创建索引，如以下错误消息所示：

On the other hand, applying the same script in the SQL Server 2016 instance will run successfully with no error with the new index on the nullable column, which is supported now in SQL Server 2016 version, as you can see below:

另一方面，在SQL Server 2016实例中应用相同的脚本将成功运行，并且没有错误，并且可空列上的新索引已得到支持，SQL Server 2016版本现在支持该索引，如下所示：

大对象（LOB）数据类型支持 (Large Objects (LOB) Data Types Support)

SQL Server 2016 eliminates another limitation in the SQL Server 2014 In-Memory OLTP Engine that prevents the use of the Large Object (LOB) data types, such as the varchar(max), nvarchar(max) and varbinary(max), while creating the Memory-Optimized tables and the Natively Compiled Stored Procedures. Moreover, you can have rows in the Memory-Optimized tables with size that exceeds the 8060 bytes in SQL Server 2016, although it is recommended not to exceed that size for performance purposes. This is due to the fact that the columns that exceeds the 8060 bytes in-row storage will be stored off-row, with 8-byte in-row reference to the off-row data and separate internal table for each off-row column.

SQL Server 2016消除了SQL Server 2014内存中OLTP引擎中的另一个限制，该限制阻止在创建时使用大对象（LOB）数据类型，例如varchar（max），nvarchar（max）和varbinary（max）内存优化表和本地编译的存储过程。此外，您可以在“内存优化”表中使行的大小超过SQL Server 2016中的8060字节，但出于性能考虑，建议不要超过该大小。这是由于以下事实：超过8060字节行内存储的列将被行外存储，其中对行外数据使用8字节行内引用，并对每个行外列使用单独的内部表。

Assume that we need to modify the data type of the EmpName column from the InMemoryDemo Memory-Optimized table to be VARCHAR(MAX) data type by dropping the table and creating it again using the T-SQL script below in the SQL Server 2014 instance:

假设我们需要删除InMemoryDemo内存优化表中的EmpName列的数据类型，使其变为VARCHAR（MAX）数据类型，方法是删除该表并在下面SQL Server 2014实例中使用以下T-SQL脚本再次创建该表：

 
USE [InMemorySQLShackDemo]
GO
DROP TABLE [dbo].[InMemoryDemo]
 
CREATE TABLE [dbo].[InMemoryDemo](
	[EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID)
	 WITH (BUCKET_COUNT = 100000),
	[EmpName] [varchar](MAX) NOT NULL,
	[EmpAddress] [varchar](50)  NOT NULL,
	[EmpDEPID] [int] NOT NULL ,
	[EmpBirthDay] [datetime] NULL,
	 INDEX IX_EmpBirthDa NONCLUSTERED ([EmpBirthDay])
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

The previous script will fail, telling you that the VARCHAR(MAX) data type in not supported with the Memory-Optimized tables as in the error message below:

先前的脚本将失败，告诉您“内存优化”表不支持VARCHAR（MAX）数据类型，如以下错误消息所示：

While the same T-SQL script will run successfully in SQL Server 2016 instance that supports the VARCHAR(MAX) data type with the Memory-Optimized table as shown below:

虽然相同的T-SQL脚本将在支持VARCHAR（MAX）数据类型和“内存优化”表SQL Server 2016实例中成功运行，如下所示：

结论 (Conclusion)

SQL Server In-Memory OLTP feature was introduced for the first time in SQL Server 2014 version and considered a big step forward in Microsoft SQL Server product performance. It allows us to move all the table data to memory and query it directly from there with the fastest possible speed. The SQL Server 2014 In-Memory OLTP engine comes with number of limitations. In SQL Server 2016, many of these limitations are removed. In this article, we described practically most of these enhancements that are performed on the In-Memory OLTP engine in SQL Server 2016.

SQL Server内存OLTP功能是在SQL Server 2014版本中首次引入的，被认为是Microsoft SQL Server产品性能上的一大进步。它使我们能够将所有表数据移到内存中，并以最快的速度直接从那里查询。 SQL Server 2014内存中OLTP引擎具有许多限制。在SQL Server 2016中，删除了许多这些限制。在本文中，我们实际上描述了在SQL Server 2016中的内存中OLTP引擎上执行的大多数增强功能。

翻译自: https://www.sqlshack.com/memory-oltp-enhancements-sql-server-2016/

sql oltp

culuo4781

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
sql oltp_SQL Server 2016中的内存中OLTP增强功能

sql oltp SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures int...
复制链接

扫一扫