SQL2014内存表性能之内存中 OLTP 的性能改进测试

先贴1个例子，后续补充完整的测试例子....

1、用MSDN例子测试一下

use mastergo--1、先创建包含内存优化文件组的数据库CREATE DATABASE imoltp2ONPRIMARY(NAME = [imoltp2_data],FILENAME = 'd:\data\imoltp2_mod1.mdf', size=500MB), FILEGROUP [imoltp2_mod] CONTAINS MEMORY_OPTIMIZED_DATA( -- name of the memory-optimized filegroupNAME = [imoltp2_dir],  -- logical name of a memory-optimized filegroup containerFILENAME = 'd:\data\imoltp2_dir') -- physical path to the containerLOG ON (name = [imoltp2_log], Filename='d:\data\imoltp2_log.ldf', size=500MB)GO--2、创建表和本机编译存储过程use imoltp2goIF EXISTS (SELECT name FROM sysobjects WHERE name = 'xx')   DROP PROCEDURE xxGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'sql')   DROP TABLE sqlGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash')   DROP TABLE hashGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash1')   DROP TABLE hash1GOcreate table [sql](c1 int not null primary key,c2 nchar(48) not null)gocreate table [hash](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 [hash1](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 xx        @rowcount int,       @c nchar(48)WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS  BEGIN ATOMIC  WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')       declare @i int = 1       while @i <= @rowcount       begin                 INSERT INTO [dbo].[hash1] values (@i, @c)                 set @i += 1       endENDGO--3、演示内存优化表的性能set statistics time offset nocount on-- inserts - 1 at a timedeclare @starttime datetime2 = sysdatetime(),       @timems intdeclare @i int = 1declare @rowcount int = 100000declare @c nchar(48) = N'12345678901234567890123456789012345678'-------------------------------- disk-based table and interpreted Transact-SQL-----------------------------begin tranwhile @i <= @rowcountbegin       insert into [sql] values (@i, @c)       set @i += 1endcommitset @timems = datediff(ms, @starttime, sysdatetime())select 'Disk-based table and interpreted Transact-SQL: ' + cast(@timems as varchar(10)) + ' ms'/*Disk-based table and interpreted Transact-SQL: 1996 ms*/-------------------------------- Interop Hash-----------------------------set @i = 1set @starttime = sysdatetime()begin tranwhile @i <= @rowcountbegin       insert into [hash] values (@i, @c)       set @i += 1endcommitset @timems = datediff(ms, @starttime, sysdatetime())select ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + cast(@timems as varchar(10)) + ' ms'/* memory-optimized table w/ hash index and interpreted Transact-SQL: 1478 ms */-------------------------------- Compiled Hash-----------------------------set @starttime = sysdatetime()exec xx @rowcount, @cset @timems = datediff(ms, @starttime, sysdatetime())select 'memory-optimized table w/hash index and native SP:' + cast(@timems as varchar(10)) + ' ms'/*memory-optimized table w/hash index and native SP:268 ms*/

引用：http://technet.microsoft.com/zh-cn/library/dn530757.aspx