Accélération des tables temporaires et des variables de table grâce à l’optimisation de la mémoire

S’applique à :SQL Server base de données Azure SQL Azure SQL Managed Instance

Si vous utilisez des tables temporaires, des variables de table ou des paramètres table, envisagez de les convertir pour utiliser des tables optimisées en mémoire et des variables de table pour améliorer les performances. Les modifications de code sont généralement minimes.

Cet article aborde les points suivants :

Scénarios qui plaident en faveur d’une conversion vers In-Memory.
Étapes techniques pour mettre en œuvre les conversions vers In-Memory.
Prérequis avant la conversion vers In-Memory.
Exemple de code qui met en évidence les avantages en matière de performances de l’optimisation en mémoire

R : Principes de base des variables de table optimisées en mémoire

Une variable de table optimisée en mémoire offre une grande efficacité en utilisant les mêmes algorithme et structures de données optimisés en mémoire que ceux utilisés par les tables optimisées en mémoire. L’efficacité est optimale quand la variable de table est accessible à partir d’un module compilé en mode natif.

Une variable de table optimisée en mémoire :

Est stockée uniquement en mémoire et n’a aucun composant sur le disque.
N’implique aucune activité d’E/S.
N’implique ni utilisation ni contention de tempdb.
Peut être transmis à une procédure stockée en tant que paramètre de type table (TVP).
Doit avoir au moins un index, soit de hachage, soit non clusterisé.
- Pour un index de hachage, le nombre de compartiments doit idéalement être égal à 1 à 2 fois le nombre de clés d’index uniques attendu, mais une surestimation du nombre de compartiments convient habituellement (jusqu’à 10 fois). Pour plus d’informations, consultez Index pour les tables à mémoire optimisée.

Types d’objets

OLTP en mémoire fournit les objets suivants qui peuvent être utilisés pour l’optimisation en mémoire des tables temporaires et des variables de table :

Tables optimisées pour la mémoire
- Durabilité = SCHEMA_ONLY
Variables de table optimisées en mémoire
- Doit être déclaré en deux étapes (plutôt qu’en ligne) :
  - CREATE TYPE my_type AS TABLE ...; , puis
  - DECLARE @mytablevariable my_type;.

B. Scénario : Remplacer la table temporaire globale

Le remplacement d’une table temporaire globale par une table SCHEMA_ONLY à mémoire optimisée est assez simple. La plus grande différence est de créer la table au moment du déploiement, et non de l’exécution. La création de tables à mémoire optimisée est plus longue que la création de tables traditionnelles en raison des optimisations au moment de la compilation. La création et la suppression de tables optimisées en mémoire dans le cadre de la charge de travail en ligne affectent les performances de la charge de travail, ainsi que les performances de rétablissement sur les fichiers secondaires du groupe de disponibilité Always On et la récupération de base de données.

Supposons que vous disposez de la table temporaire globale suivante.

CREATE TABLE ##tempGlobalB
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Envisagez de remplacer la table temporaire globale par la table optimisée en mémoire suivante qui affiche DURABILITY = SCHEMA_ONLY.

CREATE TABLE dbo.soGlobalB
(
    Column1 INT NOT NULL INDEX ix1 NONCLUSTERED,
    Column2 NVARCHAR (4000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

Étapes

La conversion de global temporary vers SCHEMA_ONLY s’effectue selon les étapes suivantes :

Créez la table dbo.soGlobalB une seule fois, comme vous le feriez pour n’importe quelle table traditionnelle sur disque.
Dans votre code Transact-SQL (T-SQL), supprimez la création de ##tempGlobalBtable. Il est important de créer la table optimisée en mémoire au moment du déploiement, et non au moment de l’exécution, pour éviter la surcharge de compilation fournie avec la création de table.
Dans votre T-SQL, remplacez toutes les mentions de ##tempGlobalBdbo.soGlobalB.

Chapitre C. Scénario : Remplacer la table temporaire de session

Les tâches de préparation pour remplacer une table temporaire de session impliquent plus de code T-SQL que pour le scénario de table temporaire globale précédent. Heureusement, le T-SQL supplémentaire ne signifie pas qu’un effort supplémentaire soit nécessaire pour effectuer la conversion.

Comme avec le scénario de la table temporaire globale, la plus grande différence consiste à créer la table au moment du déploiement et non de l’exécution, afin d’éviter la surcharge de la compilation.

Supposons que vous disposez de la table temporaire de session suivante.

CREATE TABLE #tempSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000)
);

Tout d’abord, créez la fonction table suivante pour filtrer sur @@spid. La fonction est utilisable par toutes les tables SCHEMA_ONLY que vous convertissez à partir de tables temporaires de session.

CREATE FUNCTION dbo.fn_SpidFilter
(@SpidFilter SMALLINT)
RETURNS TABLE
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
RETURN
    SELECT 1 AS fn_SpidFilter
    WHERE @SpidFilter = @@spid

Ensuite, créez la table SCHEMA_ONLY, ainsi qu’une stratégie de sécurité appliquée à la table.

Chaque table optimisée en mémoire doit avoir au moins un index.

Pour la table dbo.soSessionC, un index de hachage peut être préférable, si nous calculons la valeur BUCKET_COUNT appropriée. Toutefois, pour cet exemple, nous simplifions l’opération avec un index non cluster.

CREATE TABLE dbo.soSessionC
(
    Column1 INT NOT NULL,
    Column2 NVARCHAR (4000) NULL,
    SpidFilter SMALLINT DEFAULT (@@spid) NOT NULL,
    CONSTRAINT CHK_soSessionC_SpidFilter CHECK (SpidFilter = @@spid),
    INDEX ix_SpidFiler NONCLUSTERED (SpidFilter)
    -- INDEX ix_SpidFilter HASH
    --    (SpidFilter) WITH (BUCKET_COUNT = 64),
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

CREATE SECURITY POLICY dbo.soSessionC_SpidFilter_Policy
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter) ON dbo.soSessionC
    WITH (STATE = ON);
GO

Troisièmement, dans votre code T-SQL général :

Modifiez toutes les références à la table temporaire dans vos instructions Transact-SQL en spécifiant la nouvelle table optimisée en mémoire :
- Ancien :#tempSessionC
- Nouveau : dbo.soSessionC
Remplacez les instructions CREATE TABLE #tempSessionC dans votre code par DELETE FROM dbo.soSessionC, afin de garantir qu’une session ne soit pas exposée au contenu des tables inséré par une session précédente avec le même session_id. Il est important de créer la table optimisée en mémoire au moment du déploiement, et non au moment de l’exécution, pour éviter la surcharge de compilation fournie avec la création de table.
Supprimez les DROP TABLE #tempSessionC instructions de votre code. Si vous le souhaitez, vous pouvez insérer une DELETE FROM dbo.soSessionC instruction dans le cas où la taille de la mémoire est un problème potentiel.

D. Scénario : Une variable de table peut être définie avec MEMORY_OPTIMIZED=ON

Une variable de table traditionnelle représente une table dans la tempdb base de données. Pour des performances beaucoup plus rapides, vous pouvez optimiser la mémoire de votre variable de table.

Voici le T-SQL d’une variable de table traditionnelle. Son étendue s’arrête quand le lot ou la session se termine.

DECLARE @tvTableD TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));

Convertir inline en explicite

La syntaxe précédente permet de créer la variable de table inline. La syntaxe inline ne prend pas en charge l’optimisation de la mémoire. Nous allons donc convertir la syntaxe inline en syntaxe explicite pour le TYPE.

Étendue : La TYPE définition créée par le premier lot délimité par go persiste même après l’arrêt et le redémarrage du serveur. Toutefois, après le premier délimiteur go, la table déclarée @tvTableC reste définie uniquement jusqu’au délimiteur go suivant, puis le lot se termine.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR (10));
GO

SET NOCOUNT ON;

DECLARE @tvTableD AS dbo.typeTableD;
INSERT INTO @tvTableD (Column1) VALUES (1), (2);
SELECT * FROM @tvTableD;
GO

D.2 Convertir une table sur disque explicite en table optimisée en mémoire

Une variable de table optimisée en mémoire ne réside pas dans tempdb. L’optimisation en mémoire entraîne une augmentation de la vitesse, qui est souvent 10 fois supérieure ou plus.

La conversion vers un mode optimisé pour la mémoire s’effectue en une seule étape. Améliorez la création explicite TYPE comme suit, ce qui ajoute :

Un index. Là encore, chaque table optimisée en mémoire doit avoir au moins un index.
MEMORY_OPTIMIZED = ON.

CREATE TYPE dbo.typeTableD AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR (10))
    WITH (MEMORY_OPTIMIZED = ON);

Opération terminée.

E. Groupe de fichiers requis pour SQL Server

Sur Microsoft SQL Server, pour utiliser des fonctionnalités optimisées en mémoire, votre base de données doit avoir un FILEGROUP déclaré avec MEMORY_OPTIMIZED_DATA.

Azure SQL Database ne nécessite pas de créer ce FILEGROUP.

Condition préalable : le code Transact-SQL suivant pour un groupe de fichiers est requis pour les exemples de code T-SQL longs dans les sections ultérieures de cet article.

Vous devez utiliser SSMS.exe ou un autre outil qui peut envoyer du code T-SQL.
Collez l’exemple de code T-SQL de groupe de fichiers dans SSMS.
Modifiez le code T-SQL pour changer ses noms et chemins d’accès aux répertoires spécifiques à votre convenance.

Tous les répertoires dans la valeur FILENAME doivent déjà exister, à l’exception du dernier répertoire, qui ne doit pas déjà exister.

Exécutez votre code T-SQL modifié.

Il n’est pas nécessaire d’exécuter le FICHIER FILEGROUP T-SQL plusieurs fois, même si vous ajustez et réexécutez à plusieurs reprises la comparaison de vitesse T-SQL dans la sous-section suivante.

ALTER DATABASE InMemTest2
    ADD FILEGROUP FgMemOptim3 CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMemTest2
    ADD FILE (NAME = N'FileMemOptim3a', FILENAME = N'C:\DATA\FileMemOptim3a'
    -- C:\DATA\ preexisted.
) TO FILEGROUP FgMemOptim3;
GO

Le script suivant crée le groupe de fichiers pour vous et configure les paramètres de base de données recommandés : enable-in-memory-oltp.sql

Pour plus d’informations sur ALTER DATABASE ... ADD pour les fichiers et groupes de fichiers, consultez :

F. Test rapide pour prouver l’amélioration de la vitesse

Cette section contient du code Transact-SQL que vous pouvez exécuter pour tester et comparer le gain de performances obtenu pour INSERT-DELETE grâce à l’utilisation d’une variable de table optimisée en mémoire. Le code est composé de deux parties presque identiques sauf que, dans la première partie, le type de table est optimisé en mémoire.

Le test de comparaison dure environ 7 secondes. Pour exécuter l’exemple :

Condition préalable : vous devez déjà avoir exécuté le code T-SQL de groupe de fichiers de la section précédente.
Exécutez le script T-SQL INSERTDELETE suivant.

Remarquez l’instruction GO 5001, qui renvoie le code T-SQL 5 001 fois. Vous pouvez ajuster le nombre et l’exécuter à nouveau.

Quand vous exécutez le script dans une base de données SQL Azure, veillez à effectuer l’opération à partir d’une machine virtuelle dans la même région.

PRINT ' ';
PRINT '---- Next, memory-optimized, faster. ----';
DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

CREATE TYPE dbo.typeTableC_mem -- !!  Memory-optimized.
AS TABLE (
    Column1 INT NOT NULL INDEX ix1,
    Column2 CHAR(10)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _mem.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_mem;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _mem.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_mem;
GO

---- End memory-optimized.
-------------------------------------------------
---- Start traditional on-disk.
PRINT ' ';
PRINT '---- Next, tempdb based, slower. ----';

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

CREATE TYPE dbo.typeTableC_tempdb -- !!  Traditional tempdb.
AS TABLE (
    Column1 INT NOT NULL,
    Column2 CHAR(10)
);
GO

DECLARE @dateString_Begin NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_Begin,
    ' = Begin time, _tempdb.'
);
GO

SET NOCOUNT ON;

DECLARE @tvTableC dbo.typeTableC_tempdb;-- !!

INSERT INTO @tvTableC (Column1) VALUES (1), (2);
INSERT INTO @tvTableC (Column1) VALUES (3), (4);

DELETE @tvTableC;
GO 5001

DECLARE @dateString_End NVARCHAR(64) =
    CONVERT(NVARCHAR(64), GETUTCDATE(), 121);

PRINT CONCAT (
    @dateString_End,
    ' = End time, _tempdb.'
);
GO

DROP TYPE IF EXISTS dbo.typeTableC_tempdb;
GO

PRINT '---- Tests done. ----';
GO

Voici l'ensemble de résultats.

---- Next, memory-optimized, faster. ----
2016-04-20 00:26:58.033  = Begin time, _mem.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:26:58.733  = End time, _mem.

---- Next, tempdb based, slower. ----
2016-04-20 00:26:58.750  = Begin time, _tempdb.
Beginning execution loop
Batch execution completed 5001 times.
2016-04-20 00:27:05.440  = End time, _tempdb.
---- Tests done. ----

G. Prédire la consommation de mémoire active

Vous pouvez apprendre à prévoir les besoins en mémoire active de vos tables optimisées en mémoire avec les ressources suivantes :

Pour les variables de table plus importantes, les index non cluster utilisent plus de mémoire que pour les tablesoptimisées en mémoire. Plus le nombre de lignes et la clé d’index sont importants, plus la différence augmente.

Si la variable de table optimisée en mémoire est accessible uniquement avec une valeur de clé exacte par accès, un index de hachage peut être un meilleur choix qu’un index non cluster. Toutefois, si vous ne pouvez pas estimer la BUCKET_COUNT appropriée, un index NONCLUSTERED est un bon deuxième choix.

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-06-24