SQL Server 2016新特性： In-Memory OLTP

来源：互联网发布：剑网3脸型数据清洗编辑：程序博客网时间：2024/06/05 08:52

内存中OLTP有助于OLTP工作负荷实现显著的性能改进，并减少了处理时间。可以通过将表声明成“内存中优化”来启用内存中OLTP的功能。内存优化表完全支持事务，并且可以使用Transact-SQL进行访问。 Transact-SQL存储过程可以被编译成机器代码从而进一步提升内存优化表的性能。引擎针对高并发进行设计，并使阻塞最小化。

下面的示例(取自MSDN)，展示了如何通过T-SQL创建memory-optimized filegroup、Memory-Optimized Tables，最终可以看到基于磁盘表和内存优化表之间的性能差异，及Native SP带来的进一步性能提升。

创建数据库，及其内存优化文件组（memory-optimized filegroup）

CREATE DATABASE imoltp;   goALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]    CONTAINS MEMORY_OPTIMIZED_DATA;ALTER DATABASE imoltp ADD FILE    (name = [imoltp_dir], filename= 'c:\data\imoltp_dir')    TO FILEGROUP imoltp_mod;goUSE imoltp;go

2、创建Memory-OptimizedTables, and NCSProc

goDROP PROCEDURE IF EXISTS ncsp;DROP TABLE IF EXISTS sql;DROP TABLE IF EXISTS hash_i;DROP TABLE IF EXISTS hash_c;goCREATE TABLE [dbo].[sql] (  c1 INT NOT NULL PRIMARY KEY,  c2 NCHAR(48) NOT NULL);goCREATE TABLE [dbo].[hash_i] (  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  c2 NCHAR(48) NOT NULL) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);goCREATE TABLE [dbo].[hash_c] (  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  c2 NCHAR(48) NOT NULL) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);goCREATE PROCEDURE ncsp    @rowcount INT,    @c NCHAR(48)  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  AS   BEGIN ATOMIC   WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  DECLARE @i INT = 1;  WHILE @i <= @rowcount  BEGIN;    INSERT INTO [dbo].[hash_c] VALUES (@i, @c);    SET @i += 1;  END;END;Go

3、执行下面的T-SQL，可看到内存优化表的性能状况

goSET STATISTICS TIME OFF;SET NOCOUNT ON;-- Inserts, one at a time.DECLARE @starttime DATETIME2 = sysdatetime();DECLARE @timems INT;DECLARE @i INT = 1;DECLARE @rowcount INT = 1000000;DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';-- Harddrive-based table and interpreted Transact-SQL.BEGIN TRAN;  WHILE @i <= @rowcount  BEGIN;    INSERT INTO [dbo].[sql] VALUES (@i, @c);    SET @i += 1;  END;COMMIT;SET @timems = datediff(ms, @starttime, sysdatetime());SELECT 'A: Disk-based table and interpreted Transact-SQL: '    + cast(@timems AS VARCHAR(10)) + ' ms';-- Interop Hash.SET @i = 1;SET @starttime = sysdatetime();BEGIN TRAN;  WHILE @i <= @rowcount    BEGIN;      INSERT INTO [dbo].[hash_i] VALUES (@i, @c);      SET @i += 1;    END;COMMIT;SET @timems = datediff(ms, @starttime, sysdatetime());SELECT 'B: memory-optimized table with hash index and interpreted Transact-SQL: '    + cast(@timems as VARCHAR(10)) + ' ms';-- Compiled Hash.SET @starttime = sysdatetime();EXECUTE ncsp @rowcount, @c;SET @timems = datediff(ms, @starttime, sysdatetime());SELECT 'C: memory-optimized table with hash index and native SP:'    + cast(@timems as varchar(10)) + ' ms';goDELETE sql;DELETE hash_i;DELETE hash_c;go

执行结果：

0 0