Table temporaire et variable de table plus rapides à l’aide de l’optimisation en mémoire

Article
05/30/2023

S’applique à :SQL Server Azure SQL Database Azure SQL Managed Instance

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

Cet article aborde les points suivants :

Scénarios en faveur d’une conversion en mémoire.
Étapes techniques pour implémenter les conversions en mémoire.
Configuration requise avant la conversion en mémoire.
Exemple de code qui met en évidence les avantages en matière de performances de l’optimisation en mémoire

A. 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 aucune contention ni utilisation de tempdb.
Peut être passée dans une procédure stockée comme un paramètre table.
Doit avoir au moins un index, de hachage ou non cluster.
- 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 optimisées en mémoire.

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 en mémoire
- Durabilité = SCHEMA_ONLY
Variables de table optimisées en mémoire
- Doit être déclaré en deux étapes (plutôt qu’inline) :
  - CREATE TYPE my_type AS TABLE ...; , puis
  - DECLARE @mytablevariable my_type;.

B. Scénario : Remplacer global tempdb ##table

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 ont un impact sur les performances de la charge de travail, ainsi que sur les performances de rétablissement sur les serveurs 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);

B.1 Étapes

La conversion de la table temporaire globale en SCHEMA_ONLY s’effectue comme suit :

Créez la table dbo.soGlobalB unique comme n’importe quelle table sur disque classique.
À partir de votre transact-SQL, supprimez la création de la table ##tempGlobalB . Il est important de créer la table à mémoire optimisée au moment du déploiement, et non de l’exécution, pour éviter la surcharge de compilation qui accompagne la création de la table.
Dans votre T-SQL, remplacez toutes les mentions de ##tempGlobalB par dbo.soGlobalB.

C. Scénario : remplacer le #table tempdb 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 code T-SQL supplémentaire n’implique pas plus de travail 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 sera utilisable par toutes les tables SCHEMA_ONLY que vous convertissez à partir des 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é sur la table.

Notez que 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    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_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 :

Remplacez toutes les références à la table temporaire dans vos instructions Transact-SQL par 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, pour garantir qu’une session n’est pas exposée au contenu de table inséré par une session précédente avec la même valeur session_id. Il est important de créer la table à mémoire optimisée au moment du déploiement, et non de l’exécution, pour éviter la surcharge de compilation qui accompagne la création de la table.
Supprimez les instructions DROP TABLE #tempSessionC de votre code. Vous pouvez éventuellement insérer une instruction DELETE FROM dbo.soSessionC au cas où la taille de la mémoire serait un problème potentiel.

D. Scénario : Une variable de table peut afficher MEMORY_OPTIMIZED=ON

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

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

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

D.1 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 en mémoire. C’est pourquoi nous allons convertir la syntaxe inline en syntaxe explicite pour TYPE.

Étendue : la définition TYPE créée par le premier lot délimité par une commande go est conservée 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 est conservée uniquement jusqu’à ce que le délimiteur go suivant soit atteint et que le lot se termine.

CREATE TYPE dbo.typeTableD  
    AS TABLE  
    (  
        Column1  INT   NOT NULL ,  
        Column2  CHAR(10)  
    );  
go  
        
SET NoCount ON;  
DECLARE @tvTableD 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 se trouve 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 en table optimisée en mémoire est effectuée en une seule étape. Améliorez la création TYPE explicite pour obtenir le résultat suivant, 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

Dans Microsoft SQL Server, pour utiliser les fonctionnalités optimisées en mémoire, votre base de données doit avoir un groupe de fichiers qui est déclaré avec MEMORY_OPTIMIZED_DATA.

La base de données SQL Azure ne nécessite pas la création de ce groupe de fichiers.

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 de nom de fichier doivent exister au préalable, à l’exception du répertoire final.

Exécutez votre code T-SQL modifié.

Il est inutile d’exécuter le code T-SQL de groupe de fichiers plusieurs fois, même si vous ajustez et réexécutez à plusieurs reprises le code T-SQL de comparaison de vitesse 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 fournit le code Transact-SQL que vous pouvez exécuter pour tester et comparer le gain de vitesse pour INSERT-DELETE à partir de 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 INSERT-DELETE suivant.

Notez l’instruction « GO 5001 », qui renvoie le code T-SQL 5001 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 le jeu de résultats obtenu.

---- 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 valeur BUCKET_COUNT appropriée, un index non cluster représente une bonne alternative.

H. Voir aussi

Tables à mémoire optimisée.
Définition de la durabilité des objets à mémoire optimisée.
Mise à jour cumulative pour éliminer le risque d’erreurs « Mémoire insuffisante » incorrectes (annonce dans un billet de blog datant de septembre 2017).
- Versions de build de SQL Server 2016 fournit des informations complètes sur les versions, Service Packs et mises à jour cumulatives.
- Ces erreurs incorrectes occasionnelles ne figurent pas dans SQL Server Enterprise Edition.