分享方式:


示範:記憶體內部 OLTP 的效能改善

適用於:SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

本主題中的程式碼範例示範記憶體最佳化資料表的快速效能。 從傳統、解譯的 Transact-SQL 存取記憶體最佳化資料表中的資料時,此效能改善顯而易見。 若從原生編譯的預存程序 (NCSProc) 存取記憶體最佳化資料表中的資料,則此效能改善的幅度甚至更高。

若要查看記憶體中 OLTP 潛在效能改善的更完整示範,請參閱 In-Memory OLTP Performance Demo v1.0(記憶體中 OLTP 效能示範 1.0 版)。

現有文章中的程式碼範例為單一執行緒,且未利用記憶體內部 OLTP 的並行優點。 工作負載若是使用並行作業,將會有更高幅度的效能提升。 此程式碼範例僅示範某層面的效能改善 (即 INSERT 的資料存取效率)。

從 NCSProc 存取記憶體最佳化資料表中的資料時,可完全實現記憶體最佳化資料表所提供的效能改善。

程式碼範例

下列小節將描述每個步驟。

步驟 1a:使用 SQL Server

此第一個子區段中的步驟只有在 SQL Server 中執行時適用,而且如果是在 Azure SQL Database 中執行則不適用。 執行下列動作:

  1. 使用 SQL Server Management Studio (SSMS.exe) 連線至 SQL Server。 或者,任何與 SSMS.exe 類似的工具也可以。

  2. 手動建立名為 C:\data 的目錄。 範例 Transact-SQL 程式碼必須有預先存在的目錄。

  3. 執行簡短 T-SQL 來建立資料庫和其記憶體最佳化檔案群組。

go  
CREATE DATABASE imoltp;    --  Transact-SQL  
go  
  
ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]  
    CONTAINS MEMORY_OPTIMIZED_DATA;  
  
ALTER DATABASE imoltp ADD FILE  
    (name = [imoltp_dir], filename= 'c:\data\imoltp_dir')  
    TO FILEGROUP imoltp_mod;  
go  
  
USE imoltp;  
go  

步驟 1b:使用 Azure SQL Database 時的必要條件

只有在使用 Azure SQL Database 時才適用此子區段。 執行下列動作:

  1. 決定程式碼範例將使用的現有測試資料庫。

  2. 如果您決定建立新的測試資料庫,請使用 Azure 入口網站 來建立名為 imoltp 的資料庫。

如果您想要使用 Azure 入口網站進行這項作業的指示,請參閱 開始使用 Azure SQL Database

步驟 2:建立記憶體最佳化資料表和 NCSProc

此步驟建立記憶體最佳化資料表和原生編譯的預存程序 (NCSProc)。 執行下列動作:

  1. 使用 SSMS.exe 連接至新的資料庫。

  2. 在資料庫中執行下列 T-SQL。

go  
DROP PROCEDURE IF EXISTS ncsp;  
DROP TABLE IF EXISTS sql;  
DROP TABLE IF EXISTS hash_i;  
DROP TABLE IF EXISTS hash_c;  
go  
  
CREATE TABLE [dbo].[sql] (  
  c1 INT NOT NULL PRIMARY KEY,  
  c2 NCHAR(48) NOT NULL  
);  
go  
  
CREATE TABLE [dbo].[hash_i] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE TABLE [dbo].[hash_c] (  
  c1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),  
  c2 NCHAR(48) NOT NULL  
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);  
go  
  
CREATE PROCEDURE ncsp  
    @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].[hash_c] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
END;  
go  

步驟 3:執行程式碼

現在,您可以執行可示範記憶體最佳化資料表效能的查詢。 執行下列動作:

  1. 使用 SSMS.exe,在資料庫中執行下列 T-SQL。

    忽略任何速度或此第一次執行所產生的其他效能資料。 第一次執行確保執行數個僅一次性作業 (例如初始配置記憶體)。

  2. 同樣地,使用 SSMS.exe,在資料庫中重新執行下列 T-SQL。

go  
SET STATISTICS TIME OFF;  
SET NOCOUNT ON;  
  
-- Inserts, one at a time.  
  
DECLARE @starttime DATETIME2 = sysdatetime();  
DECLARE @timems INT;  
DECLARE @i INT = 1;  
DECLARE @rowcount INT = 100000;  
DECLARE @c NCHAR(48) = N'12345678901234567890123456789012345678';  
  
-- Harddrive-based table and interpreted Transact-SQL.  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
  BEGIN;  
    INSERT INTO [dbo].[sql] VALUES (@i, @c);  
    SET @i += 1;  
  END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'A: Disk-based table and interpreted Transact-SQL: '  
    + cast(@timems AS VARCHAR(10)) + ' ms';  
  
-- Interop Hash.  
  
SET @i = 1;  
SET @starttime = sysdatetime();  
  
BEGIN TRAN;  
  WHILE @i <= @rowcount  
    BEGIN;  
      INSERT INTO [dbo].[hash_i] VALUES (@i, @c);  
      SET @i += 1;  
    END;  
COMMIT;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'B: memory-optimized table with hash index and interpreted Transact-SQL: '  
    + cast(@timems as VARCHAR(10)) + ' ms';  
  
-- Compiled Hash.  
  
SET @starttime = sysdatetime();  
  
EXECUTE ncsp @rowcount, @c;  
  
SET @timems = datediff(ms, @starttime, sysdatetime());  
SELECT 'C: memory-optimized table with hash index and native SP:'  
    + cast(@timems as varchar(10)) + ' ms';  
go  
  
DELETE sql;  
DELETE hash_i;  
DELETE hash_c;  
go  

接下來是第二個測試執行所產生的輸出時間統計資料。

10453 ms , A: Disk-based table and interpreted Transact-SQL.  
5626 ms , B: memory-optimized table with hash index and interpreted Transact-SQL.  
3937 ms , C: memory-optimized table with hash index and native SP.  

另請參閱

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