In-Memory table在Query performance方面表現如何呢?我用這篇來記錄我一些簡單測試。
使用In-Memory table，我必須要先讓大家知道一個事實，
如果只單單把disk table移轉至In-Memory table，其實對查詢效能並無多大改善，
因為SQL Server本來就會把相關data/index page存放至buffer pool，
所以查詢資料，我們大多也都在記憶體中存取，但In-Memory好處是你不用擔心發生lock,blocking和deadlock，
這些問題都不存在In-Memory 世界，針對OLTP系統一些主要的table，我之前也都轉換至In-Memory table，
而我的測試都是以真實世界考量為主，下面來看看和disk table測試比較結果。
note:由於我個人都不建議直接存取table，我一率都透過SP或View。
Point query:
Old SP
create index idx1_CustomerID on SalesOrderHeaderRico(CustomerID)
include(OrderDate,SalesOrderID,TaxAmt)
with(data_compression=page)
create proc usp_GetOrderByCustomerID(@CustomerID int)
as
set nocount on
select h.OrderDate,h.SalesOrderID,h.TaxAmt,d.SalesOrderDetailID
from dbo.SalesOrderHeaderRico h join dbo.SalesOrderDetailRico d on h.SalesOrderID=d.SalesOrderID
where h.CustomerID=@CustomerID
exec dbo.usp_GetOrderByCustomerID 29580
Native compiled SP
alter table dbo.SalesOrderHeaderRico_Mem
add index idx_CustomerID nonclustered(CustomerID)
create proc usp_GetOrderByCustomerIDNative(@CustomerID int)
with native_compilation,SCHEMABINDING
as
begin atomic
with(transaction isolation level=SNAPSHOT, language='english')
select h.OrderDate,h.SalesOrderID,h.TaxAmt,d.SalesOrderDetailID
from dbo.SalesOrderHeaderRico_Mem h join dbo.SalesOrderDetailRico_Mem d on h.SalesOrderID=d.SalesOrderID
where h.CustomerID=@CustomerID
end
SET SHOWPLAN_XML ON
GO
exec dbo.usp_GetOrderByCustomerIDNative 29580
GO
SET SHOWPLAN_XML OFF
結果
你可看到使用native compiled sp查詢時間改善超過3倍以上，All in-memory到目前為止還從未讓我失望過。
Note:native compiled sp都是序列化執行(不支援平行處理)，且建立native compiled sp時，
會將SP內所有TSQL都compiled為可存取In-Memory table的machine code(C)，
同時會產生該sp 的dll並包含最佳執行計畫，前提是相關in-memory table統計值也是最新(create table也會產生dll)，
這是和解譯SP的最大差異，好消息是，SQL2016開始，已經會自動更新in-memory table的統計值，
我們也可在更新完統計值後，手動執行sp_recompile再次編譯native compiled sp取得最佳執行計畫，
可以透過下面TSQL來取得相關資訊。
實務上，索引和統計值對於in-memory依然重要，我在production 監控native compiled sp 效能和CPU時間，
CPU時間和效能改善幾乎有30%以上，但如果你發現執行native compiled sp占用CPU時間超高(我踩過這效能問題)，
大部分主因都是full scan table造成，或是統計值過時(可手動更新in-memory table統計後，再透過sp_recompile編譯native compiled sp)。
另外，監控native compiled sp需要開啟 sys.sp_xtp_control_proc_exec_stats來收集統計資訊，
我詢問MS預設不啟用主因是什麼?，MS告知主因為將耗用20%以上CPU資源(depends on your proc)，
所以production目前我還沒真正啟用過。
Range query
Old SP
create index idx2 on SalesOrderHeaderRico(OrderDate)
include(SalesOrderID,TaxAmt)
with(data_compression=page)
create proc usp_GetOrderByOrderDate(@start datetime,@end datetime)
as
set nocount on
select h.OrderDate,h.SalesOrderID,h.TaxAmt,d.SalesOrderDetailID
from dbo.SalesOrderHeaderRico h join dbo.SalesOrderDetailRico d on h.SalesOrderID=d.SalesOrderID
where h.OrderDate>=@start and h.OrderDate <@end
exec dbo.usp_GetOrderByOrderDate '2011-03-25','2013-05-25'
Native Compiled SP
alter table SalesOrderHeaderRico_Mem
add index idx2 nonclustered (OrderDate)
create proc usp_GetOrderByOrderDateNative(@start datetime,@end datetime)
with native_compilation,schemabinding,execute as owner
as
begin atomic
with(transaction isolation level=snapshot,language='english')
select h.OrderDate,h.SalesOrderID,h.TaxAmt,d.SalesOrderDetailID
from dbo.SalesOrderHeaderRico_Mem h join dbo.SalesOrderDetailRico_Mem d on h.SalesOrderID=d.SalesOrderID
where h.OrderDate>=@start and h.OrderDate <@end
end
SET SHOWPLAN_XML ON
GO
exec dbo.usp_GetOrderByOrderDateNative '2011-03-25','2013-05-25'
GO
SET SHOWPLAN_XML OFF
GO
結果
--query sp's dll
SELECT
mod1.name,
mod1.description
from
sys.dm_os_loaded_modules as mod1
where
mod1.description = 'XTP Native DLL';
--query auto-update enabled for in-memory table
SELECT
quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) AS [table],
s.name AS [statistics object],
1-s.no_recompute AS [auto-update enabled]
FROM sys.stats s JOIN sys.tables o ON s.object_id=o.object_id
WHERE o.is_memory_optimized=1
--returns the procedure names and execution statistics for natively compiled stored procedures
select object_id,
object_name(object_id) as 'object name',
cached_time,
last_execution_time,
execution_count,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_elapsed_time,
last_elapsed_time,
min_elapsed_time,
max_elapsed_time
from sys.dm_exec_procedure_stats
where database_id=db_id() and object_id in (select object_id
from sys.sql_modules where uses_native_compilation=1)
order by total_worker_time desc
--query text as well as execution statistics for all queries in natively compiled stored procedures in the current database for which statistics have been collected
select st.objectid,
object_name(st.objectid) as 'object name',
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((qs.statement_end_offset-qs.statement_start_offset)/2) + 1) as 'query text',
qs.creation_time,
qs.last_execution_time,
qs.execution_count,
qs.total_worker_time,
qs.last_worker_time,
qs.min_worker_time,
qs.max_worker_time,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st
where st.dbid=db_id() and st.objectid in (select object_id
from sys.sql_modules where uses_native_compilation=1)
order by qs.total_worker_time desc
--query last update of stats
SELECT
object_name(si.[object_id])
AS [TableName]
, CASE
WHEN si.[index_id] = 0 then 'Heap'
WHEN si.[index_id] = 1 then 'CL'
WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
ELSE ''
END AS [IndexType]
, si.[name] AS [IndexName]
, si.[index_id]
AS [IndexID]
, CASE
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(m, -1, getdate())
THEN '!! More than a month OLD !!'
WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(wk, -1, getdate())
THEN '! Within the past month !'
WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent'
ELSE ''
END AS [Warning]
, STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update]
FROM sys.indexes AS si
WHERE OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1
ORDER BY [TableName], si.[index_id]
go
參考
[SQL SERVER]SQL2016-掌握SQL Server Function 效能
Natively Compiled Stored Procedures
Monitoring Performance of Natively Compiled Stored Procedures
Native Compilation of Tables and Stored Procedures
Statistics for Memory-Optimized Tables
Faster temp table and table variable by using memory optimization