问题引出

菜鸟一口气写了好几篇关于MSSQL On Linux的文章了，老鸟看在眼里喜在心理，于是又来到菜鸟窝边：“MSSQL On Linux目前有很多炫酷的功能都不支持，比如AlwaysOn，Mirroring，SQLAgent，Replication等，但是In-Memory OLTP还是支持的，你去好好研究下，也许以后，说不定阿里云RDS SQL Server会跑在Linux平台上哟。”。
领了命令的菜鸟开始了MSSQL On Linux In-Memory OLTP的研究之旅。

数据库配置

为了使用In-Memory OLTP，推荐将数据库兼容级别设置为130。

USE AdventureWorks2008R2
GO
IF NOT EXISTS(
    SELECT TOP 1 *
    FROM sys.databases as d
    WHERE d.name = Db_Name()
    AND d.compatibility_level >= 130
)
BEGIN
    ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;
END
GO

让memory-optimized table在SNAPSHOT事务隔离级工作，当用户访问memory-optimized table时使用的较低隔离级别将自动提升到 SNAPSHOT 隔离。

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO

在创建memory-optimized table之前，增加Memory Optimized FILEGROUP。

ALTER DATABASE AdventureWorks2008R2 
    ADD FILEGROUP AdventureWorks2008R2_mod CONTAINS memory_optimized_data
GO 

EXEC sys.xp_create_subdir 'C:\var\opt\mssql\data\AdventureWorks2008R2\MOD'
GO
ALTER DATABASE AdventureWorks2008R2 
    ADD FILE (NAME='AdventureWorks2008R2_mod', FILENAME='C:\var\opt\mssql\data\AdventureWorks2008R2\MOD\AdventureWorks2008R2_mod') 
    TO FILEGROUP AdventureWorks2008R2_mod
GO

创建内存优化表

接下来，创建memory-optimized table，内存优化表数据存储在内存里面，与基于磁盘的表最大的区别在于不需要SQL Server系统将数据从磁盘读入到SQL Server缓存里面，加之in-Memory OLTP是lock and latch free的架构设计，因此效率有极大的提升。

USE AdventureWorks2008R2
go
IF OBJECT_ID('Orders','U') IS NOT NULL
BEGIN
    DELETE FROM dbo.Orders
    DROP TABLE dbo.Orders
END 
GO

CREATE TABLE dbo.Orders (
OrderID INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
, OrderDate datetime2 NOT NULL INDEX ix_OrderDate NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000)
, CustomerID int
, SourceID int
, StatusID int
, Amount decimal (18, 2)
)
--WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
GO

创建本地编译存储过程

最后，创建本地编译存储过程，SQL Server允许此类型的存储过程来访问内存优化表。本地编译存储过程的T-SQL语句会被直接编译为机器码，以本地DLL的方式进行快速的数据访问和查询，相对于传统的T-SQL语句拥有更高的性能。

USE AdventureWorks2008R2
Go
CREATE PROCEDURE dbo.usp_InsertSampleOrders (
    @InsertTotalCount int 
)
WITH NATIVE_COMPILATION, SCHEMABINDING AS 
BEGIN ATOMIC 
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    DECLARE 
        @i INT = 0
    ;

    WHILE @i < @InsertTotalCount 
    BEGIN 
        INSERT INTO dbo.Orders values (SYSDATETIME(), 1, 1, 1, 1);
        
        SET
            @i = @i + 1
        ;
    END
END
GO

USE AdventureWorks2008R2
GO
IF OBJECT_ID('dbo.usp_QuerySamplesOrders','p') IS NOT NULL
    DROP PROC dbo.usp_QuerySamplesOrders
GO

CREATE PROCEDURE dbo.usp_QuerySamplesOrders(
    @queryCount int
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH   
(  TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
  LANGUAGE = 'english')  
  
  SELECT TOP(@queryCount) OrderID,OrderDate,CustomerID,SourceID,StatusID,Amount
  FROM dbo.orders

END
GO

创建基于磁盘的表

为了进行对照测试，创建常规的磁盘表对象和存储过程对象，和内存优化表是对应起来的。

USE TestDB
GO

IF OBJECT_ID('Orders','U') IS NOT NULL
BEGIN
    TRUNCATE TABLE dbo.Orders
    DROP TABLE dbo.Orders
END 
GO
CREATE TABLE Orders (
OrderID INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
, OrderDate datetime2
, CustomerID int
, SourceID int
, StatusID int
, Amount decimal (18, 2)
)
GO



USE TestDB
Go
CREATE PROCEDURE dbo.usp_InsertSampleOrders (
    @InsertTotalCount int 
)
AS
BEGIN    
    SET NOCOUNT ON
    DECLARE 
        @i INT = 0
    ;

    WHILE @i < @InsertTotalCount 
    BEGIN 
        INSERT INTO dbo.Orders values (SYSDATETIME(), 1, 1, 1, 1);
        
        SET
            @i = @i + 1
        ;
    END
END
GO

USE TestDB
GO
IF OBJECT_ID('dbo.usp_QuerySamplesOrders','p') IS NOT NULL
    DROP PROC dbo.usp_QuerySamplesOrders
GO

CREATE PROCEDURE dbo.usp_QuerySamplesOrders(
    @queryCount int
)
AS 
BEGIN
    SELECT TOP(@queryCount) OrderID,OrderDate,CustomerID,SourceID,StatusID,Amount
    FROM dbo.orders

END
GO

性能测试

以下是对比内存优化表和磁盘表的性能测试对比，主要是从写和读场景来对比的。

内存优化表INSERT测试

内存优化表写入测试，利用本地编译存储过程分别写入10万，20万，40万，80万和160万笔记录，最后统计存储过程执行时间。

USE AdventureWorks2008R2
Go
SET NOCOUNT ON
SET STATISTICS TIME ON
EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 100000
SET STATISTICS TIME OFF
DELETE FROM dbo.Orders
SET STATISTICS TIME ON
EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 200000
SET STATISTICS TIME OFF
DELETE FROM dbo.Orders
SET STATISTICS TIME ON
EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 400000
SET STATISTICS TIME OFF
DELETE FROM dbo.Orders
SET STATISTICS TIME ON
EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 800000
SET STATISTICS TIME OFF
DELETE FROM dbo.Orders
SET STATISTICS TIME ON
EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 1600000
SET STATISTICS TIME OFF

执行时间如下：

从内存表最终的执行时间消耗来看，写入10万，20万，40万，80万和160万笔记录时间消耗分别为966，1874,3765,7433和15537毫秒。写入160万条记录数只花了15.5秒时间，平均每秒写入量超过10万比记录，内存优化表的写效率还是相当高的。

磁盘表执行INSERT测试

接下来是对基于磁盘的表进行写入测试，方法还是调用存储过程分别写入10万，20万，40万，80万和160万笔记录，最后来看时间消耗。

USE TestDB
Go
SET NOCOUNT ON

declare
    @ms_ticks1 bigint
    ,@ms_ticks2 bigint
    ,@ms_ticks3 bigint
    ,@ms_ticks4 bigint
    ,@ms_ticks5 bigint
    ,@ms_ticks6 bigint
    ;

SELECT TOP 1 @ms_ticks1 = ms_ticks
FROM sys.dm_os_sys_info WITH(NOLOCK)

EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 100000

SELECT TOP 1 @ms_ticks2 = ms_ticks
FROM sys.dm_os_sys_info WITH(NOLOCK)

TRUNCATE TABLE dbo.Orders

EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 200000

SELECT TOP 1 @ms_ticks3 = ms_ticks
FROM sys.dm_os_sys_info WITH(NOLOCK)

TRUNCATE TABLE dbo.Orders

EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 400000

SELECT TOP 1 @ms_ticks4 = ms_ticks
FROM sys.dm_os_sys_info WITH(NOLOCK)

TRUNCATE TABLE dbo.Orders

EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 800000

SELECT TOP 1 @ms_ticks5 = ms_ticks
FROM sys.dm_os_sys_info WITH(NOLOCK)

TRUNCATE TABLE dbo.Orders

EXEC dbo.usp_InsertSampleOrders  @InsertTotalCount = 1600000

SELECT TOP 1 @ms_ticks6 = ms_ticks
FROM sys.dm_os_sys_info WITH(NOLOCK)
TRUNCATE TABLE dbo.Orders


SELECT
    ms_ticks1 = @ms_ticks1
    ,ms_ticks2 = @ms_ticks2
    ,ms_ticks3 = @ms_ticks3
    ,ms_ticks4 = @ms_ticks4
    ,ms_ticks5 = @ms_ticks5
    ,ms_ticks6 = @ms_ticks6
    ,[2-1] = @ms_ticks2 - @ms_ticks1
    ,[3-2] = @ms_ticks3 - @ms_ticks2
    ,[4-3] = @ms_ticks4 - @ms_ticks3
    ,[5-4] = @ms_ticks5 - @ms_ticks4
    ,[6-5] = @ms_ticks6 - @ms_ticks5
;

执行结果如下图

从基于磁盘的表写入时间消耗来看，10万，20万，40万，80万和160万笔记录写入时间消耗分别为30171，58147，108002,231528和473753毫秒，160万条记录，平均秒钟不到4000条。

内存优化表QUERY测试

内存优化表读取测试，利用本地编译存储过程分别读出10万，20万，40万，80万和160万笔记录，最后统计存储过程执行时间。

-----Query Testing
USE AdventureWorks2008R2
Go
SET NOCOUNT ON
SET STATISTICS TIME OFF
SET STATISTICS TIME ON
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 100000
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 200000
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 400000
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 800000
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 1600000
SET STATISTICS TIME OFF

内存优化表执行读取测试的最终结果如下图：

在做数据读取操作的同时，菜鸟也通过Linux命令iostat查看IO统计情况。

# iostat -x 1

从监控来看，SQL Server on Linux全程没有IO读取磁盘操作

磁盘表执行QUERY测试

基于磁盘的表执行查询测试，为了防止SQL Server的数据缓存对测试结果造成影响，在每一批测试之前，手动清空SQL Server数据缓存（Buffer Pool）。

USE TestDB
Go
SET NOCOUNT ON
SET STATISTICS TIME OFF
SET STATISTICS TIME ON
DBCC DROPCLEANBUFFERS
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 100000
DBCC DROPCLEANBUFFERS
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 200000
DBCC DROPCLEANBUFFERS
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 400000
DBCC DROPCLEANBUFFERS
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 800000
DBCC DROPCLEANBUFFERS
EXEC dbo.usp_QuerySamplesOrders  @queryCount = 1600000
SET STATISTICS TIME OFF

同样在一边读取测试操作的时候，菜鸟一边使用iostat查看磁盘IO读取情况。可以明显的看到Linux系统有IO Read操作，这从侧面证明了基于磁盘的表Query是需要从物理硬盘读取数据的。

由于在每一批测试读取操作之前添加了删除缓存的动作，所以显示结果不是太好看，我手动删了无用信息。最终执行测试结果如下：

对比测试结果

这里对内存优化表和磁盘表写入和读取效率做一个细致的对比测试结果。

INSERT操作

将我们上面测试的结果做一个表格来对比，其中TimeCost(ms)指时间消耗，以毫秒为单位；IRPMS是指每毫秒写入表中的记录笔数。从表中数据我们可以发现两个规律：
不管是内存表还是磁盘表，写入数据速度都比较稳定，不会有太大的上上下下波动，内存表稳定在每毫秒103-107之间，磁盘表每毫秒稳定在3.3-3.7之间。
内存表写入效率很高，是磁盘表的近30倍。

将表格数据做一个炫酷无比，直截了当的图形出来：

SELECT操作

相通的原理，我们将内存表和磁盘表的读取操作测试统计表格如下：

将表格数据使用图形化的方式形象出来：

从读取操作来看，使用磁盘表的读取和使用内存优化表时间消耗和QRPMS（Query Records Per Milliseconds）几乎持平，差异非常小。这一点，是非常出乎菜鸟的意外，令菜鸟百思不得其解的，按照常理，从磁盘读取数据和内存读取数据效率应该会有天壤之别的，难道是菜鸟测试方法不对，又或是哪里搞错了？如果有知道的朋友，请不吝赐教，帮菜鸟批评指正。
菜鸟突然灵光一现，难道是因为高大上的Apple PCIE SSD读取效率已经达到内存效率级别了？带着这个问题，菜鸟使用Disk Speed Test工具测试了下PCIE SSD的读写效率，以下是对5GB大小临时文件的读写效率截图：

Apple PCIE SSD效率还真不是盖的，写效率可以达到625MB/S，读取效率是写的近三倍，达到了1862MB/S。但是，8GB DDR3-1600双通道内存的读取效率理论值1600642/1024/8可以达到25600MB/S，即25GB/S的速度啊。所以，就算是Apple PCIE SSD卡的读取效率也仅仅是内存效率读取效率的不到1/12啊。

写在最后

虽然菜鸟研究了MSSQL on Linux In-Memory OLTP，的确内存优化表的写效率可以达到磁盘写效率的30倍，但是读效率没有太大的提升，这点让菜鸟十分忐忑，将信将疑。

MSSQL On Linux In-Memory OLTP

问题引出

数据库配置

创建内存优化表

创建本地编译存储过程

创建基于磁盘的表

性能测试

内存优化表INSERT测试

磁盘表执行INSERT测试

内存优化表QUERY测试

磁盘表执行QUERY测试

对比测试结果

INSERT操作

SELECT操作

写在最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MSSQL On Linux In-Memory OLTP

问题引出

数据库配置

创建内存优化表

创建本地编译存储过程

创建基于磁盘的表

性能测试

内存优化表INSERT测试

磁盘表执行INSERT测试

内存优化表QUERY测试

磁盘表执行QUERY测试

对比测试结果

INSERT操作

SELECT操作

写在最后

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像