variables de table Memory-Optimized

En plus des tables optimisées en mémoire (pour un accès efficace aux données) et des procédures stockées compilées en mode natif (pour un traitement efficace des requêtes et l’exécution de logique métier) In-Memory OLTP introduit un troisième type d’objet : le type de table à mémoire optimisée. Une variable de table créée à l’aide d’un type de table à mémoire optimisée est une variable de table optimisée en mémoire.

Les variables de table optimisées en mémoire offrent les avantages suivants par rapport aux variables de table basées sur disque :

Les variables sont stockées uniquement en mémoire. L’accès aux données est plus efficace, car le type de table à mémoire optimisée utilise le même algorithme optimisé en mémoire et les mêmes structures de données utilisées pour les tables optimisées en mémoire, en particulier lorsque les variables sont utilisées dans les procédures stockées compilées en mode natif.
Avec les variables de table optimisées en mémoire, il n’existe aucune utilisation de tempdb. Les variables de table ne sont pas stockées dans tempdb et n’utilisent aucune ressource dans tempdb.

Les scénarios d’utilisation classiques pour les variables de table mémoire optimisée sont les suivants :

Stockage des résultats intermédiaires et création de jeux de résultats uniques basés sur plusieurs requêtes dans des procédures stockées compilées en mode natif.
Passage de paramètres de type table dans des procédures stockées compilées de manière native et des procédures stockées interprétées.
Remplacement des variables de table sur disque et, dans certains cas, des tables temporaires locales à une procédure stockée. Cela est particulièrement utile s’il y a beaucoup de concurrence liée à la base de données tempdb dans le système.
Les variables de table peuvent être utilisées pour simuler des curseurs dans des procédures stockées compilées en mode natif, ce qui peut vous aider à contourner les limitations de surface d’exposition dans les procédures stockées compilées en mode natif.

Comme les tables mémoire optimisées, SQL Server génère une DLL pour chaque type de table à mémoire optimisée. (La compilation est appelée lorsque le type de table à mémoire optimisée est créé et non lorsqu’il est utilisé pour créer des variables de table optimisées en mémoire.) Cette DLL inclut les fonctions permettant d’accéder aux index et de récupérer des données à partir des variables de table. Lorsqu’une variable de table optimisée en mémoire est déclarée en fonction du type de table, une instance de la table et des structures d’index correspondant au type de table est créée dans la session utilisateur. La variable de table peut ensuite être utilisée de la même façon que les variables de table sur disque. Vous pouvez insérer, mettre à jour et supprimer des lignes dans la variable de table, et vous pouvez utiliser les variables dans Transact-SQL requêtes. Vous pouvez également transmettre les variables dans des procédures stockées compilées et interprétées en mode natif, en tant que paramètres de type table (TVP).

L’exemple suivant montre un type de table à mémoire optimisée à partir de l’exemple OLTP basé sur AdventureWorks In-Memory (exemple SQL Server 2014 In-Memory OLTP).

CREATE TYPE Sales.SalesOrderDetailType_inmem
   AS TABLE
(
   OrderQty         smallint   NOT NULL,
   ProductID        int        NOT NULL,

   SpecialOfferID   int        NOT NULL
      INDEX  IX_SpecialOfferID  NONCLUSTERED,

   LocalID          int        NOT NULL,

   INDEX IX_ProductID HASH (ProductID)
      WITH ( BUCKET_COUNT = 8 )
)
WITH ( MEMORY_OPTIMIZED = ON );

L’exemple montre que la syntaxe des types de tables à mémoire optimisée est similaire aux types de tables basés sur disque, à l’exception suivante :

MEMORY_OPTIMIZED=ON indique que le type de table est optimisé en mémoire.
Le type doit avoir au moins un index. Comme pour les tables optimisées en mémoire, vous pouvez utiliser des index de hachage et non clusterisés.

Pour un index de hachage, le nombre de compartiments doit être d’environ un à deux fois le nombre de clés d’index uniques attendues. Pour plus d’informations, consultez Détermination du nombre de compartiments correct pour les index de hachage.
Les restrictions de type de données et de contrainte sur les tables optimisées en mémoire s’appliquent également aux types de tables à mémoire optimisée. Par exemple, dans les contraintes par défaut de SQL Server 2014, les contraintes de vérification ne sont pas prises en charge.

Comme les tables optimisées en mémoire, les variables de table optimisées en mémoire,

Ne prenez pas en charge les plans parallèles.
Doit tenir en mémoire et n’utiliser pas de ressources de disque.

Les variables de table basées sur disque existent dans tempdb. Les variables de table optimisées en mémoire existent dans la base de données utilisateur (mais elles n’consomment pas de stockage et ne sont pas récupérées).

Vous ne pouvez pas créer une variable de table optimisée en mémoire à l’aide de la syntaxe en ligne. Contrairement aux variables de table basées sur disque, vous devez d’abord créer un type.

Paramètres table

L’exemple de script suivant montre la déclaration d’une variable de table comme type Sales.SalesOrderDetailType_inmem de table à mémoire optimisée, l’insertion de trois lignes dans la variable et la transmission de la variable en tant que TVP dans Sales.usp_InsertSalesOrder_inmem.

DECLARE @od Sales.SalesOrderDetailType_inmem,  
  @SalesOrderID uniqueidentifier,  
  @DueDate datetime2 = SYSDATETIME()  
  
INSERT @od (LocalID, ProductID, OrderQty, SpecialOfferID) VALUES  
  (1, 888, 2, 1),  
  (2, 450, 13, 1),  
  (3, 841, 1, 1)  
  
EXEC Sales.usp_InsertSalesOrder_inmem  
  @SalesOrderID = @SalesOrderID,  
  @DueDate = @DueDate,  
 @OnlineOrderFlag = 1,  
  @SalesOrderDetails = @od

Les types de tables à mémoire optimisée peuvent être utilisés comme type pour les paramètres de table de procédure stockée (TVPs) et peuvent être référencés par les clients de la même manière que les types de tables basés sur disque et les TVPs. Par conséquent, l’appel de procédures stockées avec des tvps à mémoire optimisée, et les procédures stockées compilées en mode natif fonctionnent exactement de la même façon que l’appel de procédures stockées interprétées avec des tvps basés sur disque.

Table de remplacement #temp

L’exemple suivant montre les types de tables à mémoire optimisée et les variables de table comme remplacement des tables #temp locales d’une procédure stockée.

-- Using SQL procedure and temp table  
CREATE TABLE #tempTable (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
  
CREATE PROCEDURE sqlProc  
AS  
BEGIN  
  TRUNCATE TABLE #tempTable  
  
  INSERT #tempTable VALUES (1)  
  INSERT #tempTable VALUES (2)  
  INSERT #tempTable VALUES (3)  
  SELECT * FROM #tempTable  
END  
GO  
  
-- Using natively compiled stored procedure and table variable  
CREATE TYPE TT AS TABLE (c INT NOT NULL PRIMARY KEY NONCLUSTERED)  
GO  
  
CREATE PROCEDURE NCSPProc  
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
AS  
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')  
  DECLARE @tableVariable TT  
  INSERT @tableVariable VALUES (1)  
  INSERT @tableVariable VALUES (2)  
  INSERT @tableVariable VALUES (3)  
  SELECT c FROM @tableVariable  
END  
GO

Création d’un jeu de résultats unique

L’exemple suivant montre comment stocker les résultats intermédiaires et créer des jeux de résultats uniques basés sur plusieurs requêtes dans des procédures stockées compilées en mode natif. L’exemple calcule l’union SELECT c1 FROM dbo.t1 UNION SELECT c1 FROM dbo.t2.

CREATE DATABASE hk  
GO  
ALTER DATABASE hk ADD FILEGROUP hk_mod CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE hk ADD FILE( NAME = 'hk_mod' , FILENAME = 'c:\data\hk_mod') TO FILEGROUP hk_mod;  
  
USE hk  
GO  
  
CREATE TYPE tab1 AS TABLE (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON)  
  
CREATE TABLE dbo.t1 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
CREATE TABLE dbo.t2 (c1 INT NOT NULL, INDEX idx NONCLUSTERED(c1)) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)  
  
INSERT INTO dbo.t1 VALUES (1), (2)  
INSERT INTO dbo.t2 VALUES (3), (4)  
GO  
  
CREATE PROCEDURE dbo.p1  
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  
  AS  
  BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )  
  
    DECLARE @t dbo.tab1  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t1;  
  
    INSERT @t (c1)  
    SELECT c1 FROM dbo.t2;  
  
    SELECT c1 FROM @t;  
  END  
GO  
  
EXEC dbo.p1  
GO

Consommation de mémoire pour les variables de table

La consommation de mémoire pour les variables de table est similaire aux tables optimisées en mémoire, à l’exception des index non cluster. Si vous insérez beaucoup de lignes dans des variables de table optimisées en mémoire avec des index non cluster et si les clés d’index sont volumineuses, ces variables de table utilisent une quantité disproportionnée de mémoire. Les index non clusterisés sur les grandes variables de table nécessitent proportionnellement plus de mémoire qu'un index non clusterisé nécessiterait pour le même nombre de lignes insérées dans une table (plus de mémoire dans les pages d'index).

La mémoire des variables de table provient du pool de ressources Resource Governor de la base de données.

Contrairement aux tables optimisées en mémoire, la mémoire consommée (y compris les lignes supprimées) par les variables de table est libérée lorsque la variable de table sort de l’étendue.

La mémoire est comptabilisée comme faisant partie du seul consommateur de mémoire PGPOOL de la base de données.

Voir aussi

prise en charge deTransact-SQL pour In-Memory OLTP

Last updated on 2017-07-14

Partager via