OLTP

/*SQL Server 2014 中的新增功能内存优化表。
内存中 OLTP 是集成到 SQL Server 引擎中的内存优化的数据库引擎。
若要使用 内存中 OLTP，请将经常访问的表定义为内存优化表。 内存优化表是完全可事务的、并可以使用Transact-SQL进行访问。Transact-SQL存储过程可以编译成为机器码，从而进一步地提高内存优化表的性能。
该引擎是专为高并发而设计的，并且阻塞是最小的。
它使用无闩锁数据结构和多版本乐观并发控制，低延迟和高吞吐量。实际的性能提升取决于许多因素，但通常可实现 5 到 20 倍的性能改进。*/

 

--演示：内存 OLTP 的性能改进

 

------------------
USE master

GO

CREATE DATABASE imoltp
ON PRIMARY
(NAME = [imoltp_demo_data], FILENAME = 'C:\sqlDATA\imoltp\imoltp_demo_data.mdf', size=200MB)
LOG ON (name = [imoltp_demo_log], Filename='C:\sqlDATA\imoltp\imoltp_demo_log.ldf', size=100MB)
GO

ALTER DATABASE imoltp
ADD FILEGROUP imoltpFG CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE imoltp

ADD FILE
(
NAME = 'imoltpFile',
FILENAME ='C:\sqlDATA\imoltp\imoltpFile'
)

TO FILEGROUP [imoltpFG]
GO

----------或者
CREATE DATABASE imoltp
ON
PRIMARY(NAME = [imoltp_demo_data], FILENAME = 'C:\sqlDATA\imoltp\imoltp_demo_data.mdf', size=200MB)
, FILEGROUP [imoltpFG]
CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [imoltpFile],
FILENAME = 'C:\sqlDATA\imoltp\imoltpFile')
GO
-----------------------------
USE imoltp
GO

--内存优化表
CREATE TABLE InMemTable
(
[ID] FLOAT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 102400),
[Name] NVARCHAR(50) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
go

--如果数据库级的排序规则没有指定为中文代码页，请一定把中文字符保存在nvarchar类型中
-- 创建表时必须创建一个非空的主键，并且必须建立非聚集的Hash索引和BUCKET_COUNT参数
--当在定义表的时候，会指定其“持久性”。一个内存优化表可以是持久的或非持久的。

--（1）对于一个持久表是将数据存储在内存中，而且也保存在内存优化文件组中。
--（2）对于一个非持久表，数据是仅存储在内存中的，所以，如果系统崩溃或重启，数据就会丢失。
--在SQL Server 2014中默认用的是持久表，当定义一个持久内存优化表的时候，你还必须定义一个基于非聚集哈希索引的主键。
--在一个哈希索引中，数据是通过一个内存散列表进行访问的，而非固定大小页。哈希索引是在内存优化表中唯一支持的索引类型。
/*
并且所有行的长度被限制在8060字节，且没有行外数据。事实上，8060字节限制在表创建时就已强制执行，因此与基于磁盘的表不同，拥有两个VARCHAR(5000)列的内存优化表是不能被创建的。

内存优化表可以用两个DURABILITY值中的一个来进行定义，SCHEMA_AND_DATA或SCHEMA_ONLY，前者是默认值。采用DURABILITY = SCHEMA_ONLY定义的内存优化表，意味着表数据的修改不进行日志记录，并且表中的数据不保留在磁盘上。但是，架构会被持久化成数据库元数据的一部分，所以SQL Server重新启动数据库恢复后，空表将可供使用。

正如前面提到的，内存优化表必须至少拥有一个索引，这一要求可以通过自动创建支持主键约束的索引的方式来实现。除了那些采用SCHEMA_ONLY选项创建的表外，其他所有表都必须声明一个主键。至少必须声明一个索引来支持PRIMARY KEY约束。

创建成哈希索引的PRIMARY KEY索引，必须为其指定存储桶的数量。

当创建一个内存优化表时，内存中OLTP引擎为访问该表将生成并编译DML例程，并将这些例程加载为DLL文件。 SQL Server本身不执行在内存优化表上实际的数据操作（记录分裂），而是当访问内存优化表时为所需的操作调用对应的DLL。

创建内存优化表时，除了已经列出的数据类型的限制之外，还有一些限制。
?没有DML触发器
?没有外键或者CHECK约束
?除主键外没有唯一索引
?包括支持主键的索引在内，最多只有8个索引

此外，一旦表被创建后，不允许更改表的架构。与使用ALTER TABLE不同，你需要删除并重新创建表。另外，并没有具体的索引DDL命令（如CREATE INDEX，ALTER INDEX，DROP INDEX）。索引总是作为创建表的一部分进行创建。

*/

--聚集索引表,-- Create a traditional disk-based table.
CREATE TABLE DiskBasedTable
(
[ID] FLOAT NOT NULL PRIMARY KEY,
[Name] NVARCHAR(50) NOT NULL
)
go
--------------

--另一方面，内存优化表支持本地编译存储过程，只要那些存储过程只引用内存优化表。
--在这种情况下，存储过程可以转化为本地代码，这样会执行更快且要比典型存储过程需要更少的内存。
--除了只引用内存优化表，一个本地编译存储过程必须是模式绑定的并运行在一个特定执行内容内。另外，每个本地编译存储过程必须完全由一个原子块组成。

CREATE PROCEDURE [Proc_InMemTable_Insert]
@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].InMemTable values (@i, @c)
set @i += 1
end
END
go

 

CREATE PROCEDURE [Proc_InMemTable_update]
@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
UPDATE [dbo].InMemTable SET name=@c WHERE id=@i
set @i += 1
end
END
GO

CREATE PROCEDURE [Proc_InMemTable_delete]
@rowcount int
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
DELETE FROM [dbo].InMemTable WHERE id=@i
set @i += 1
end
END
GO

------------------------- 效率评测 -------------------------

SET STATISTICS TIME OFF;
SET NOCOUNT ON;

--Insert
DECLARE @i INT=1,@iMax INT = 5000
DECLARE @v NCHAR(48)='123456789012345678901234567890123456789012345678'
Declare @timems INT
DECLARE @starttime DATETIME2 = sysdatetime()

-- Disk-based table queried with interpreted Transact-SQL.

while @i<=@iMax
begin
insert into DiskBasedTable (id,name) values(@i, @v)
set @i+=1
end
set @timems=datediff(ms, @starttime, sysdatetime())
select cast(@timems as varchar(10)) + ' ms (Insert into disk-based table with insert Transact-SQL).'

-- Memory-optimized table queried with a natively-compiled stored procedure.

set @starttime=SYSDATETIME()
exec [Proc_InMemTable_Insert] @rowcount=@iMax, @c=@v
SET @timems = datediff(ms, @starttime, sysdatetime());
SELECT CAST(@timems AS VARCHAR(10)) + ' ms (Insert into memory-optimized table with natively-compiled stored procedure).';

------------------

--update
--时间较长，故分段执行另设变量
DECLARE @u INT=1,@uMax INT = 5000 --5000条记录
DECLARE @uv NCHAR(48)='1234567890123456789012345678901234567890abcdefgh'
DECLARE @ut DATETIME2 = sysdatetime()
Declare @timemsupdate INT
set nocount on
while @u<=@uMax
begin
update [dbo].[DiskBasedTable] set name=@uv where id=@u
set @u+=1
end

set @timemsupdate=datediff(ms, @ut, sysdatetime())
select cast(@timemsupdate as varchar(10)) + ' ms (update the disk-based table with update Transact-SQL).'

set @ut=SYSDATETIME()
exec [Proc_InMemTable_update] @rowcount=@uMax, @c=@uv
set @timemsupdate=datediff(ms, @ut, sysdatetime())
select cast(@timemsupdate as varchar(10)) + ' ms (update the memory-optimized table with natively-compiled stored procedure).'
-------------- delete ------------------------------------------
--时间较长，故分段执行另设变量
DECLARE @d INT=1,@dMax INT = 5000 --5000条记录
DECLARE @dt DATETIME2 = sysdatetime()
Declare @timemsdel INT
set nocount on
while @d<=@dMax
begin
delete from [dbo].[DiskBasedTable] where id=@d
set @d+=1
end
set @timemsdel=datediff(ms, @dt, sysdatetime())
select cast(@timemsdel as varchar(10)) + ' ms (delete the disk-based table with delete Transact-SQL).'

set @dt=SYSDATETIME()
exec [dbo].[Proc_InMemTable_delete] @rowcount=@dMax
set @timemsdel=datediff(ms, @dt, sysdatetime())
select cast(@timemsdel as varchar(10)) + ' ms (delete the memory-optimized table with natively-compiled stored procedure).'

--总结 效率：内存表对比普通的磁盘表，　在增、删、改方面有非常大的优势，　甚至达到了上百倍！

 

/* 可行性：内存表的限制比较大，比如数据库用了内存表之后就不能使用复制、镜像、链接服务器，但alwaysOn是支持的
内存表也不能使用触发器、约束，内存表的结构和索引建立之后就不能修改等　 而且必须配合本地编译的存储过程效率才能提升。仅适用于数据库不需要被限制的功能（复制、镜像等），　而且表的增、删、改非常频繁的情况。*/

/*
内存优化表的限制

1. 不支持的数据类型：varchar(max)、nvarchar(max)、image、xml、text、ntext、rowversion、datetimeoffset、geography、geometry、hierarchyid、sql_variant、UDT;
2. 每行的总字节数不得超过 8060 个字节;
3. 不支持外键或约束检查
4. 支持 IDENTITY(1, 1)。 但是不支持使用 IDENTITY(x, y)（其中 x != 1 或 y != 1 ）定义的标识列。
5. 不支持dml触发器
6. 内存优化表中的 (var)char 列必须使用代码页 1252 排序规则。 此限制不适用于 n(var)char 列。 下列代码检索所有 1252 排序规则：
select * from sys.fn_helpcollations() where collationproperty(name, 'codepage') = 1252;
7. 如果数据库排序规则不是代码页 1252 排序规则，则本机编译的存储过程不能使用 (var)char 类型的参数、局部变量或字符串常量。
8. 无法修改表结构，只能删除表再重建
9. 索引只能建hash非聚焦索引， 不能建聚焦索引。
10. 索引只能在建表时建立，不能重建索引。

*/

/*
内存优化表（Memory Optimized Tables）承诺会带来显著的性能提升，但往往很难使用。使用困难很大一部分来源于不能改变它的表结构和索引这一事实。

之前的规避措施是创建一张临时表，把数据复制过来，删除原来的内存优化表，然后创建并且载入新的内存优化表。：

改变bucket总数。bucket总数太高会浪费内存，太低则损害性能。

内存优化表通常受限于绑定本地编译存储过程的schema。

在2016版本中本地编译存储过程也可以更改。当新版本的存储过程在进行编译时，将继续使用原来的版本。一旦编译完成，执行挂起请求，数据库切换到新版本上去。与表一样，以前更改本地存储编译过程要求在创建新的存储过程之前先删除已有的存储过程。在编译窗口期，这会导致执行失败。

*/

 

----------select--
CREATE PROCEDURE [Proc_Inmem_select]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
SELECT id,name FROM dbo.InMemTable
END
GO

DECLARE @st DATETIME2 = sysdatetime()
set nocount on
select id,name from [dbo].[DiskBasedTable]
select 'select (t_disk): '+ convert(varchar(10), datediff(ms, @st, sysdatetime()))
set @st=SYSDATETIME()
exec [Proc_Inmem_select]
select 'select (t_mem_nc): '+ convert(varchar(10), datediff(ms, @st, sysdatetime()))