Microsoft SQL Server
OLTP en mémoire (Hekaton)

Créer une table optimisée pour la mémoire

-- Create demo database
CREATE DATABASE SQL2016_Demo
 ON  PRIMARY
( 
    NAME = N'SQL2016_Demo', 
    FILENAME = N'C:\Dump\SQL2016_Demo.mdf', 
    SIZE = 5120KB, 
    FILEGROWTH = 1024KB 
 )
 LOG ON 
 ( 
    NAME = N'SQL2016_Demo_log', 
    FILENAME = N'C:\Dump\SQL2016_Demo_log.ldf', 
    SIZE = 1024KB, 
    FILEGROWTH = 10%
 )
GO

use SQL2016_Demo
go

-- Add Filegroup by MEMORY_OPTIMIZED_DATA type 
ALTER DATABASE SQL2016_Demo 
    ADD FILEGROUP MemFG CONTAINS MEMORY_OPTIMIZED_DATA 
GO


--Add a file to defined filegroup
ALTER DATABASE SQL2016_Demo ADD FILE
    ( 
        NAME = MemFG_File1,
        FILENAME = N'C:\Dump\MemFG_File1' -- your file path, check directory exist before executing this code
    ) 
TO FILEGROUP MemFG
GO

--Object Explorer -- check database created
GO

-- create memory optimized table 1
CREATE TABLE dbo.MemOptTable1  
(  
    Column1     INT         NOT NULL,  
    Column2     NVARCHAR(4000)  NULL,  
    SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  

    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
    INDEX ix_SpidFilter HASH (SpidFilter) WITH (BUCKET_COUNT = 64),  
      
    CONSTRAINT CHK_soSessionC_SpidFilter  
        CHECK ( SpidFilter = @@spid ),  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
         DURABILITY = SCHEMA_AND_DATA);  --or DURABILITY = SCHEMA_ONLY
go  

-- create memory optimized table 2
CREATE TABLE MemOptTable2
(
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    FullName NVARCHAR(200) NOT NULL, 
    DateAdded DATETIME NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Afficher les fichiers et les tables .dll créés pour les tables optimisées pour la mémoire

SELECT
    OBJECT_ID('MemOptTable1') AS MemOptTable1_ObjectID,
    OBJECT_ID('MemOptTable2') AS MemOptTable2_ObjectID
GO

SELECT 
    name,description 
FROM sys.dm_os_loaded_modules
WHERE name LIKE '%XTP%'
GO

Afficher toutes les tables optimisées pour la mémoire:

SELECT 
    name,type_desc,durability_desc,Is_memory_Optimized 
FROM sys.tables
    WHERE Is_memory_Optimized = 1
GO

Types de tables et tables temporaires optimisés en mémoire

Par exemple, il s'agit d'un type de table traditionnel basé sur tempdb:

CREATE TYPE dbo.testTableType AS TABLE
(
   col1 INT NOT NULL,
   col2 CHAR(10)
);

Pour optimiser ce type de table en mémoire, ajoutez simplement l'option memory_optimized=on et ajoutez un index s'il n'y en a aucun sur le type d'origine:

CREATE TYPE dbo.testTableType AS TABLE
(
   col1 INT NOT NULL,
   col2 CHAR(10)
)WITH (MEMORY_OPTIMIZED=ON);

La table temporaire globale est comme ceci:

CREATE TABLE ##tempGlobalTabel 
(  
    Col1   INT   NOT NULL ,  
    Col2   NVARCHAR(4000)  
);  

Table temporaire globale optimisée pour la mémoire:

CREATE TABLE dbo.tempGlobalTabel 
(  
    Col1   INT   NOT NULL   INDEX ix NONCLUSTERED,  
    Col2   NVARCHAR(4000)  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
         DURABILITY = SCHEMA_ONLY);  

Pour optimiser en mémoire les tables temporaires globales (## temp):

1. Créez une nouvelle table optimisée pour la mémoire SCHEMA_ONLY avec le même schéma que la table globale ##temp
   • S'assurer que la nouvelle table a au moins un index
2. Modifiez toutes les références à ##temp dans vos instructions Transact-SQL à la nouvelle température de la table optimisée pour la mémoire.
3. Remplacez les instructions DROP TABLE ##temp dans votre code par DELETE FROM temp , pour nettoyer le contenu
4. Supprimez les instructions CREATE TABLE ##temp de votre code - elles sont désormais redondantes

plus d'informations

Déclarez les variables de table optimisées par la mémoire

Pour des performances plus rapides, vous pouvez optimiser en mémoire votre variable de table. Voici le T-SQL pour une variable de table traditionnelle:

DECLARE @tvp TABLE  
( 
    col1   INT NOT NULL ,  
    Col2   CHAR(10) 
);  

Pour définir des variables optimisées en mémoire, vous devez d'abord créer un type de table optimisé en mémoire, puis en déclarer une variable:

CREATE TYPE dbo.memTypeTable 
AS TABLE  
(  
    Col1  INT NOT NULL INDEX ix1,  
    Col2  CHAR(10)  
)  
WITH  
    (MEMORY_OPTIMIZED = ON);  

Ensuite, nous pouvons utiliser le type de table comme ceci:

DECLARE @tvp memTypeTable
insert INTO @tvp
values (1,'1'),(2,'2'),(3,'3'),(4,'4'),(5,'5'),(6,'6')

SELECT * FROM @tvp

Résultat:

Col1    Col2
1       1         
2       2         
3       3         
4       4         
5       5         
6       6        

Créer une table temporelle avec version du système optimisée pour la mémoire

CREATE TABLE [dbo].[MemOptimizedTemporalTable]
(
    [BusinessDocNo] [bigint] NOT NULL,
    [ProductCode] [int] NOT NULL,
    [UnitID] [tinyint] NOT NULL,
    [PriceID] [tinyint] NOT NULL,
    [SysStartTime] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [SysEndTime] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]),

    CONSTRAINT [PK_MemOptimizedTemporalTable]  PRIMARY KEY NONCLUSTERED 
    (
        [BusinessDocNo] ASC,
        [ProductCode] ASC
    )
)
WITH ( 
    MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA, -- Memory Optimized Option ON
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[MemOptimizedTemporalTable_History] , DATA_CONSISTENCY_CHECK = ON ) 
)

plus d'informations