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

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

1、用MSDN例子测试一下

 

use master
go
--1、先创建包含内存优化文件组的数据库
CREATE DATABASE imoltp2
ON
PRIMARY(NAME = [imoltp2_data],
FILENAME = 'd:\data\imoltp2_mod1.mdf', size=500MB)
, FILEGROUP [imoltp2_mod] CONTAINS MEMORY_OPTIMIZED_DATA( -- name of the memory-optimized filegroup
NAME = [imoltp2_dir],  -- logical name of a memory-optimized filegroup container
FILENAME = 'd:\data\imoltp2_dir') -- physical path to the container
LOG ON (name = [imoltp2_log], Filename='d:\data\imoltp2_log.ldf', size=500MB)
GO

--2、创建表和本机编译存储过程
use imoltp2
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'xx')
   DROP PROCEDURE xx
GO


IF EXISTS (SELECT name FROM sysobjects WHERE name = 'sql')
   DROP TABLE sql
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash')
   DROP TABLE hash
GO

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'hash1')
   DROP TABLE hash1
GO


create table [sql]
(
c1 int not null primary key,
c2 nchar(48) not null
)
go

create 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)
go

create 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)
go

CREATE PROCEDURE xx 
       @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].[hash1] values (@i, @c)
                 set @i += 1
       end
END
GO
--3、演示内存优化表的性能
set statistics time off
set nocount on

-- inserts - 1 at a time


declare @starttime datetime2 = sysdatetime(),
       @timems int

declare @i int = 1
declare @rowcount int = 100000
declare @c nchar(48) = N'12345678901234567890123456789012345678'

-----------------------------
--- disk-based table and interpreted Transact-SQL
-----------------------------

begin tran
while @i <= @rowcount
begin
       insert into [sql] values (@i, @c)
       set @i += 1
end
commit

set @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 = 1
set @starttime = sysdatetime()

begin tran
while @i <= @rowcount
begin
       insert into [hash] values (@i, @c)
       set @i += 1
end
commit


set @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, @c

set @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