[SQL Server][In-Memory OLTP]記憶內資料表BUCKET_COUNT預估

隱約記得老師說要參考相異值數目來預估Bucket Count,來筆記Determining the Correct Bucket Count for Hash Indexes。

 

In-Memory OLTP

SQL Server 2014新功能,也是Enterprise  Edition限定版,可以提升 OLTP 資料庫應用程式效能。 減少交易處理的延遲,並且有助於改善暫時性資料案例 的效能。 

 

建立資料庫

CREATE DATABASE [imoltp]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'imoltp', FILENAME = N'C:\SQL\imoltp.mdf' , 
SIZE = 102400KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'imoltp_log', FILENAME = N'C:\SQL\imoltp_log.ldf' , 
SIZE = 20480KB , FILEGROWTH = 10%)
GO

 

建立最佳化的 FILEGROUP

ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS 
MEMORY_OPTIMIZED_DATA  
ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', 
filename='c:\SQL\imoltp_mod1') TO FILEGROUP imoltp_mod  

 

建立記憶體最佳化資料表

分別新增Disk Base 及In-Memory Table(schema_only)

USE IMOLTP
CREATE TABLE dbo.AcctBookDisk  
(  
    bu              varchar(3)   COLLATE Latin1_General_100_BIN2 not null,  
    acctnbr         varchar(8)   COLLATE Latin1_General_100_BIN2 not null,   
    createdate      datetime                                     not null,  
    name            nvarchar(50) 
                 COLLATE Chinese_Taiwan_Stroke_BIN2 not null,  
    balance         numeric(13,2)               not null,  
    CONSTRAINT PK_AcctBookDisk PRIMARY KEY NONCLUSTERED (acctnbr,bu)  
)  


CREATE TABLE dbo.AcctBookIm  
(  
    bu              varchar(3)   COLLATE Latin1_General_100_BIN2 not null,  
    acctnbr         varchar(8)   COLLATE Latin1_General_100_BIN2 not null,   
    createdate      datetime                                     not null,  
    name            nvarchar(50)   
          COLLATE Chinese_Taiwan_Stroke_BIN2 not null,  
    balance         numeric(13,2)               not null,  
    CONSTRAINT PK_AcctBook PRIMARY KEY NONCLUSTERED HASH(acctnbr,bu)  
    WITH (BUCKET_COUNT=1024)  
)  
WITH  (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_ONLY);


--記憶體最佳化資料表中的 (var)char 資料行必須使用字碼頁 1252 定序。
--select * from sys.fn_helpcollations()  
--  where collationproperty(name, 'codepage') = 1252;  
--(n)(var)char 資料行上的索引只能使用 BIN2 定序來指定 (請參閱第一個範例)。 
--下列查詢會擷取所有支援的 BIN2 定序:
--select * from sys.fn_helpcollations() where name like '%BIN2'  

 

先循序寫Disk Base及In-Memory Table 各100萬筆資料
SET NOCOUNT ON
 
PRINT '---Disk Base'
PRINT CONVERT(TIME,GETDATE())
DECLARE @I INT = 0;
WHILE @I < 1000000
BEGIN
SET @I = @I + 1
INSERT INTO AcctBookDisk
	VALUES 
    ('001', CONVERT(VARCHAR(8), @I), '20161028', 'ImDeveloper', 100)
END
PRINT CONVERT(TIME,GETDATE())

PRINT '---In-Memory'
PRINT CONVERT(TIME,GETDATE())
SET @I  = 0;
WHILE @I < 1000000
BEGIN
SET @I = @I + 1
INSERT INTO AcctBookIm
	VALUES 
    ('001', CONVERT(VARCHAR(8), @I), '20161028', 'ImDeveloper', 100)
END
PRINT CONVERT(TIME,GETDATE())

比較循序的效果:

# 循序寫入
Disk Base 236秒
In-Memory Table 10秒

In-Memory完勝! 而且寫入Disk Base table時,MBP的硬碟效能滿載。

這個階段看起來Bucket count還沒有影響!

 

換個做法,改試試

Insert Select
PRINT '---Disk Base'
PRINT CONVERT(TIME,GETDATE())
INSERT INTO AcctBookDisk
		SELECT
			A.BU
			,'D' + A.acctnbr
			,A.createdate
			,A.name
			,A.balance
		FROM AcctBookDisk A
PRINT CONVERT(TIME,GETDATE())

PRINT '---In-Memory'
PRINT CONVERT(TIME,GETDATE())
INSERT INTO AcctBookIm
		SELECT
			A.BU
			,'I' + A.acctnbr
			,A.createdate
			,A.name
			,A.balance
		FROM AcctBookIm A
PRINT CONVERT(TIME,GETDATE())

# 循序寫入 整批寫入
Disk Base 236秒 6秒
In-Memory Table 10秒 38秒

結果令人意外!是不是哪裡出狀況?

In-Memory變成龜兔賽跑的兔子!慢上許多個倍!

 

改用Natively Compiled Stored Procedures?
CREATE PROCEDURE usp_bulkinsert  
    WITH 
        EXECUTE AS OWNER,
        NATIVE_COMPILATION,  
        SCHEMABINDING  
AS  
BEGIN ATOMIC  
    WITH  
        (TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
        LANGUAGE = N'us_english')  
  
   INSERT INTO dbo.AcctBookIm
		SELECT
			 A.BU 
			,N'A'  + A.acctnbr
			,A.createdate
			,A.name 
			,A.balance
		FROM dbo.AcctBookIm A
END

# 循序寫入 整批寫入
Disk Base 236秒 6秒
In-Memory Table 10秒 38秒
In-Memory Table(Natively Compiled Stored Procedures)   39秒

 

還是慢! 一整個鬼打牆!哈~ 這不科學!(氣!老婆要跺腳了!!!)

 

Bucket Count

重新檢視In-memory table 建立索引時有一個bucket count,我們用了1024,如果放大到相異值數目的200萬看看!

好!因為是客戶使用的版本是SQL Server 2014,還不能直接修改資料表結構,我們先移除預存程序、移除資料表最後才重建資料表!

DROP PROC usp_bulkinsert
DROP TABLE AcctBookIm
CREATE TABLE dbo.AcctBookIm  
(  
    bu              varchar(3)   COLLATE Latin1_General_100_BIN2 not null,  
    acctnbr         varchar(8)   COLLATE Latin1_General_100_BIN2 not null,   
    createdate      datetime                                     not null,  
    name            nvarchar(50)   
       COLLATE Chinese_Taiwan_Stroke_BIN2 not null,  
    balance         numeric(13,2)               not null,  
    CONSTRAINT PK_AcctBookIm PRIMARY KEY NONCLUSTERED HASH(acctnbr,bu)  
     WITH (BUCKET_COUNT=2000000)  
)  
WITH  (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_ONLY);

循序建立100萬筆資料

PRINT '---In-Memory'
PRINT CONVERT(TIME,GETDATE())
DECLARE @I INT = 0;
WHILE @I < 1000000
BEGIN
SET @I = @I + 1
INSERT INTO AcctBookIm
VALUES ('001', CONVERT(VARCHAR(8), @I),'20161028', 'ImDeveloper', 100)
END
PRINT CONVERT(TIME,GETDATE())

這邊執行後還是約10秒,快!

 

接著重新執行Bulk Insert 

Good!科學了!超快!只要1秒! 

科學是一門通過經驗實證的方法,對現象(原來指自然現象,現泛指包括社會現象等現象)進行歸因的學科。

# 循序寫入 整批寫入
Disk Base 236秒 6秒
In-Memory Table(Bucket count = 1024) 10秒 38秒

IIn-Memory Table (Bucket count = 2000000)

10秒 1.3秒

 

真相大白!In-memory table 沉冤得雪!

 

MSDN建議:

在大部分情況下,值區計數應該介於索引鍵中相異值數目的 1 到 2 倍之間。 如果索引鍵包含許多重複的值,平均每個索引鍵值都有超過 10 個資料列,則改用非叢集索引

您不一定能夠預測某個特定索引鍵可能擁有或將會擁有多少個值。 如果 BUCKET_COUNT 值在索引鍵值實際數目的 5 倍以內,則效能應該是可以接受的。

 

計算最佳Bucket count語法

SELECT
  POWER(2,CEILING( LOG(COUNT(0)) / LOG(2))) AS 'BUCKET_COUNT'
FROM
  (SELECT DISTINCT bu,acctnbr
      FROM AcctBookIm) T

 

透過Dmv查詢

SELECT * FROM sys.dm_db_xtp_hash_index_stats

 

SQL Server 2016除了改善了許多語法限制後,可以直接修改索引中的Bucket Count。(Alter table)

ALTER TABLE dbo.AcctBookIm  
      ALTER index PK_AcctBookIm rebuild  
            WITH (BUCKET_COUNT=3000000); 

就在點還有In-Database R,一直努力說服客戶用2016,不過客戶也有希望等SP1、SP2後再升級的考量。

 

參考:

In-Memory OLTP (記憶體中最佳化)

記憶體最佳化檔案群組

Requirements for Using Memory-Optimized Tables

Determining the Correct Bucket Count for Hash Indexes

Determine BUCKET_COUNT for Hash Indexes for SQL Server Memory Optimized Tables

Altering Memory-Optimized Tables