SQL Server 2016新特性： In-Memory OLTP
时间:2016-06-05 20:57 来源:linux.it.net.cn 作者:IT
内存中OLTP有助于OLTP工作负荷实现显著的性能改进，并减少了处理时间。可以通过将表声明成“内存中优化”来启用内存中OLTP的功能。内存优化表完全支持事务，并且可以使用Transact-SQL进行访问。 Transact-SQL存储过程可以被编译成机器代码从而进一步提升内存优化表的性能。引擎针对高并发进行设计，并使阻塞最小化。
下面的示例(取自MSDN)，展示了如何通过T-SQL创建memory-optimized filegroup、Memory-Optimized Tables，最终可以看到基于磁盘表和内存优化表之间的性能差异，及Native SP带来的进一步性能提升。
-
创建数据库，及其内存优化文件组（memory-optimized filegroup）
[sql] view plain copy
-
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
执行结果：
(责任编辑：IT)
内存中OLTP有助于OLTP工作负荷实现显著的性能改进，并减少了处理时间。可以通过将表声明成“内存中优化”来启用内存中OLTP的功能。内存优化表完全支持事务，并且可以使用Transact-SQL进行访问。 Transact-SQL存储过程可以被编译成机器代码从而进一步提升内存优化表的性能。引擎针对高并发进行设计，并使阻塞最小化。 下面的示例(取自MSDN)，展示了如何通过T-SQL创建memory-optimized filegroup、Memory-Optimized Tables，最终可以看到基于磁盘表和内存优化表之间的性能差异，及Native SP带来的进一步性能提升。
[sql] view plain copy
2、创建Memory-OptimizedTables, and NCSProc
3、执行下面的T-SQL，可看到内存优化表的性能状况
执行结果：
|