SQLServer2014内存优化表相关文摘

SQL2014学习笔记1--初识内存优化表

SQL2014学习笔记2--内存优化表索引简介

SQL2014学习笔记3--内存优化表的事务

SQL2014学习笔记4--内存优化表的备份、还原、恢复

专家坐堂：微软SQL Server 2014疑难解析

微软内存表的官方文档

SQL2014发布后，最令我感兴趣的是列索引的改进和新增的内存优化表。前者让2012中鸡肋的新功能具备了真正用于实际环境的可能，后者则很可能是又一个激动人心的新鸡肋。。。。不过不管怎么说，有就比没有好，这一版做的不够好下一版改进就是了

USE [master]
GO
CREATE DATABASE [DB_TEST_MEMTB]
 ON  PRIMARY 
( NAME = N'DB_TEST_MEMTB_DATA',FILENAME = N'D:DB_TEST_MEMTB_DATA.mdf',SIZE = 512000KB,MAXSIZE = UNLIMITED,FILEGROWTH = 1024KB ),--下面的文件就是数据流文件了
 FILEGROUP [MEM_DIR] CONTAINS MEMORY_OPTIMIZED_DATA  DEFAULT
( NAME = N'DB_TEST_MEMTB_DIR',FILENAME = N'D:DB_TEST_MEMTB_DIR',MAXSIZE = UNLIMITED)
 LOG ON 
( NAME = N'DB_TEST_MEMTB_LOG',FILENAME = N'D:DB_TEST_MEMTB_LOG.',MAXSIZE = 2048GB,FILEGROWTH = 1024KB)
GO

USE DB_TEST_MEMTB
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),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

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'

-----------------------------
--- 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,sysdatetime())
select ' memory-optimized table w/ hash index and interpreted Transact-SQL: ' + cast(@timems as varchar(10)) + ' ms'

-----------------------------
--- Compiled Hash
-----------------------------
set @starttime = sysdatetime()

exec xx @rowcount,@c

set @timems = datediff(ms,sysdatetime())
select 'memory-optimized table w/hash index and native SP:' + cast(@timems as varchar(10)) + ' ms'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!