RiCo技術農場

當資料庫使用in-memory必須要新增相關File Group和data file，

SQL2014預設和filestream共用，但在SQL2016已經分開，

基本上會有兩種類型檔案(Data和 Delta file)，這兩種檔案只會append only(表示I/O都是sequential)，

實務上我會將這兩種檔案類型和使用者資料庫data file分開存放且切多個檔案，

由於SQL Server會自動採用round robin平均建立實體檔案(delta和data會1:1 )，

檔案數量建議偶數，下面我來簡單實際看一下這些檔案初始情況。

 

create database mymemoryDB on primary
(
name='mymemoryDB',filename='D:\testSQLDF\mymemoryDB.mdf',size=1000mb,filegrowth=600mb,maxsize=unlimited
)
log on
(
name='mymemoryDB_Log',filename='D:\testSQLDF\mymemoryDB_Log.mdf',size=100mb,filegrowth=600mb,maxsize=unlimited
)

--add filegroup for in-memory
alter database mymemoryDB
add filegroup mymemoryDB_mem contains memory_optimized_data

--add file (Use different folders replace drives for demo)
alter database mymemoryDB
add file(name='mymemoryDB_mem_diskA1',filename='D:\testSQLDF\mymemoryDB_mem_diskA1')
to filegroup mymemoryDB_mem

--add file
alter database mymemoryDB
add file(name='mymemoryDB_mem_diskA2',filename='D:\testSQLDF\mymemoryDB_mem_diskA2')
to filegroup mymemoryDB_mem

--add file
alter database mymemoryDB
add file(name='mymemoryDB_mem_diskB1',filename='D:\testSQLDF\mymemoryDB_mem_diskB1')
to filegroup mymemoryDB_mem

--add file
alter database mymemoryDB
add file(name='mymemoryDB_mem_diskB2',filename='D:\testSQLDF\mymemoryDB_mem_diskB2')
to filegroup mymemoryDB_mem

-- Create In-Memory table
use mymemoryDB

create table testmrmOLTP
(
ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
Fname nvarchar(30) not null
) with(memory_optimized=on,durability=schema_and_data)

set nocount on
declare @step int =1
while(@step<=1000)
begin
insert into testmrmOLTP
select @step,N'rico'
set @step+=1
end

--we would see DATA and DELTA file in the container.
select t.container_id,t.relative_file_path,t.file_type,t.file_type_desc,t.file_size_in_bytes,t.file_size_used_in_bytes
,t.logical_row_count,t.state_desc
from  sys.dm_db_xtp_checkpoint_files t 
order by state desc,file_type desc

1.每一個data file初始大小約16 mb、delta file初始大小約1 mb。

2.一個data file一定會有一個delta file。

 

--Two files having same slot form a pair of DATA-DELTA 
select internal_storage_slot,        
       file_type_desc,
	   checkpoint_file_id,
	   checkpoint_pair_file_id
from   sys.dm_db_xtp_checkpoint_files 
where  internal_storage_slot is not null

參考

[SQL SERVER]SQL2016-Columnstore Indexes增強(3)

Configuring Storage for Memory-Optimized Tables

Creating and Managing Storage for Memory-Optimized Objects

Configuring the In-Memory OLTP File Group for High Performance