关闭

SQL Server 2016新特性: In-Memory OLTP

标签: In-Memory OLTP
2076人阅读 评论(0) 收藏 举报
分类:


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

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


  1.  创建数据库,及其内存优化文件组(memory-optimized filegroup

CREATE DATABASE imoltp;   
go

ALTER 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;
go

USE imoltp;
go

2创建Memory-OptimizedTables, and NCSProc

go
DROP PROCEDURE IF EXISTS ncsp;
DROP TABLE IF EXISTS sql;
DROP TABLE IF EXISTS hash_i;
DROP TABLE IF EXISTS hash_c;
go

CREATE TABLE [dbo].[sql] (
  c1 INT NOT NULL PRIMARY KEY,
  c2 NCHAR(48) NOT NULL
);
go

CREATE 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);
go

CREATE 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);
go

CREATE 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,可看到内存优化表的性能状况

go
SET 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';
go

DELETE sql;
DELETE hash_i;
DELETE hash_c;
go

执行结果:





0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:642468次
    • 积分:8699
    • 等级:
    • 排名:第2358名
    • 原创:177篇
    • 转载:23篇
    • 译文:49篇
    • 评论:80条
    最新评论