Inspection des zones initiales dans OLTP en mémoire

  • Article

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

Cet article est destiné aux développeurs qui souhaitent se familiariser en quelques minutes avec les principes de base des fonctionnalités de performances OLTP en mémoire de Microsoft SQL Server et d’Azure SQL Database.

Pour OLTP en mémoire, cet article fournit les éléments suivants :

  • Explications rapides des fonctionnalités
  • Exemples de code de base qui implémentent les fonctionnalités

SQL Server et SQL Database ne varient que légèrement dans leur prise en charge des technologies en mémoire.

Les blogueurs font parfois référence à l’OLTP en mémoire sous le terme Hekaton.

Avantages des fonctionnalités en mémoire

SQL Server fournit des fonctionnalités en mémoire qui peuvent améliorer considérablement les performances de nombreux systèmes d’applications. Les considérations les plus simples sont décrites dans cette section.

Fonctionnalités d’OLTP (traitement transactionnel en ligne)

Les systèmes qui doivent traiter de nombreuses instructions SQL INSERT simultanément sont d’excellents candidats pour les fonctionnalités OLTP.

  • Nos tests d’évaluation montrent que des vitesses 5 à 20 fois supérieures peuvent être obtenues en adoptant les fonctionnalités en mémoire.

Les systèmes qui traitent des calculs lourds dans Transact-SQL constituent d’excellents candidats.

  • Une procédure stockée dédiée aux calculs lourds peut s’exécuter jusqu’à 99 fois plus rapidement.

Vous pourrez par la suite consulter les articles suivants qui offrent des démonstrations des gains de performances offerts par l’OLTP en mémoire :

Fonctionnalités d’analytique opérationnelle

L’analytique en mémoire fait référence aux instructions SQL INSERT qui agrègent des données transactionnelles, généralement par l’inclusion d’une clause GROUP BY. Le type d’index appelé columnstore est central à l’analytique opérationnelle.

Il existe deux scénarios principaux :

  • L’analytique opérationnelle par lot fait référence aux processus d’agrégation qui s’exécutent soit après les heures de bureau, soit sur du matériel secondaire qui comporte des copies des données transactionnelles.
  • L’analytique opérationnelle en temps réel fait référence aux processus d’agrégation qui s’exécutent pendant les heures de bureau et sur le matériel principal utilisé pour les charges de travail transactionnelles.

Cet article se concentre sur OLTP et non sur l’analyse. Pour plus d’informations sur la façon dont les index columnstore permettent à SQL de bénéficier de l’analytique, consultez :

columnstore

Une série d’excellents billets de blog expliquent de manière élégante les index columnstore selon plusieurs perspectives. La majorité des billets décrivent en détail le concept d’analytique opérationnelle en temps réel, que columnstore prend en charge. Ces billets ont été créés par Sunil Agarwal, responsable de programme chez Microsoft, en mars 2016.

analytique opérationnelle en temps réel

  1. Analytique opérationnelle en temps réel à l’aide de la technologie en mémoire
  2. Analytique opérationnelle en temps réel - Vue d’ensemble d’un index columnstore non cluster
  3. Analytique opérationnelle en temps réel : Exemple simple utilisant un index columnstore non cluster dans SQL Server 2016
  4. Analytique opérationnelle en temps réel : Opérations DML et index columnstore non cluster dans SQL Server 2016
  5. Analytique opérationnelle en temps réel : Index columnstore non cluster filtré
  6. Analytique opérationnelle en temps réel : Option de délai de compression pour l’index columnstore non cluster
  7. Analytique opérationnelle en temps réel : Option de délai de compression avec index columnstore non cluster et performances
  8. Analytique opérationnelle en temps réel : Tables optimisées en mémoire et index columnstore

Défragmenter un index columnstore

  1. Défragmentation d’index columnstore à l’aide de la commande REORGANIZE
  2. Stratégie de fusion d’index columnstore pour REORGANIZE

Importation en bloc des données

  1. Cluster columnstore : Chargement en bloc
  2. Index cluster columnstore : Optimisations du chargement des données - Journalisation minimale
  3. Index cluster columnstore : Optimisations du chargement des données - Importation en bloc parallèle

Fonctionnalités de l’OLTP en mémoire

Examinons les fonctionnalités principales de l’OLTP en mémoire.

Tables optimisées en mémoire

Le mot clé T-SQL MEMORY_OPTIMIZED, dans l’instruction CREATE TABLE, permet à la table créée d’exister dans la mémoire active, et non sur le disque.

Une table optimisée en mémoire a une représentation d’elle-même dans la mémoire active et une copie secondaire sur le disque.

  • La copie sur disque sert pour les opérations de récupération de routine après un redémarrage, puis arrêt, du serveur ou de la base de données. Cette dualité « disque plus mémoire » est totalement masquée pour l’utilisateur et pour votre code.

Modules compilés en mode natif

Le mot clé T-SQL NATIVE_COMPILATION, dans l’instruction CREATE PROCEDURE, permet de créer une procédure stockée compilée en mode natif. Les instructions T-SQL sont compilées en code machine lors de la première utilisation de la procédure native chaque fois que la base de données bascule en ligne. Les instructions T-SQL ne subissent plus l’interprétation lente de chaque instruction.

  • La compilation native peut être cent fois plus rapide que le mode interprété.

Un module natif ne peut référencer que des tables optimisées en mémoire. Il ne peut pas référencer de tables sur disque.

Il existe trois types de modules compilés en mode natif :

Disponibilité dans Azure SQL Database

OLTP en mémoire et columnstore sont disponibles dans Azure SQL Database. Pour plus d’informations, consultez Optimiser les performances à l’aide des technologies en mémoire dans SQL Database.

1. Assurer un niveau de compatibilité >= 130

Cette section est la première d’une série de sections numérotées qui illustrent la syntaxe Transact-SQL que vous pouvez utiliser pour implémenter des fonctionnalités OLTP en mémoire.

Tout d’abord, il est important que votre base de données soit définie sur un niveau de compatibilité d’au moins 130. Le code T-SQL suivant indique le niveau de compatibilité actuel sur lequel votre base de données actuelle est définie.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Le code T-SQL suivant met à jour le niveau, si nécessaire.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Élever au niveau capture instantanée (SNAPSHOT)

Une transaction impliquant à la fois une table basée sur disque et une table optimisée en mémoire est une transaction entre conteneurs. Dans ce type de transaction, il est essentiel que la partie optimisation en mémoire de la transaction fonctionne au niveau d’isolation de la transaction nommé SNAPSHOT.

Pour appliquer de manière fiable ce niveau aux tables optimisées en mémoire dans une transaction entre conteneurs, modifiez le paramétrage de votre base de données en exécutant le code T-SQL suivant.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Créer un groupe de fichiers (FILEGROUP) optimisé

Dans Microsoft SQL Server, avant de créer une table optimisée en mémoire, vous devez créer un groupe de fichiers en lui associant la déclaration CONTAINS MEMORY_OPTIMIZED_DATA. Le groupe de fichiers est attribué à votre base de données. Pour plus d'informations, consultez le site Internet suivant :

Dans Azure SQL Database, vous ne devez et ne pouvez pas créer un groupe de fichiers de ce type.

L’exemple de script T-SQL suivant active une base de données pour OLTP en mémoire et configure tous les paramètres recommandés. Il fonctionne avec SQL Server et Azure SQL Database : enable-in-memory-oltp.sql.

Notez que toutes les fonctionnalités SQL Server ne sont pas prises en charge pour les bases de données avec un groupe de fichiers MEMORY_OPTIMIZED_DATA. Pour plus d’informations sur les limitations, consultez Fonctionnalités SQL Server non prises en charge pour l’OLTP en mémoire.

4. Créer une table optimisée en mémoire

Le mot-clé Transact-SQL essentiel est le mot-clé MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

Les instructions Transact-SQL INSERT et SELECT sur une table optimisée en mémoire sont les mêmes que pour une table normale.

ALTER TABLE pour les tables optimisées en mémoire

ALTER TABLE...ADD/DROP peut ajouter ou supprimer une colonne dans une table optimisée en mémoire ou un index.

  • CREATE INDEX et DROP INDEX ne peuvent pas être exécutés sur une table optimisée en mémoire, utilisez ALTER TABLE... ADD/DROP INDEX à la place.
  • Pour plus d’informations, consultez Modification des tables optimisées en mémoire.

Planifier vos tables et index mémoire optimisés

5. Créer une procédure stockée compilée en mode natif (procédure native)

Le mot clé essentiel est NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

Le mot clé SCHEMABINDING signifie que les tables référencées dans la procédure native ne peuvent pas être supprimées, sauf si celle-ci est d’abord supprimée. Pour plus d’informations, consultez Création de procédures stockées compilées en mode natif.

Notez que vous n’avez pas besoin de créer une procédure stockée compilée en mode natif pour accéder à une table à mémoire optimisée. Vous pouvez également référencer les tables à mémoire optimisée à partir de procédures stockées traditionnelles et de lots ad hoc.

6. Exécuter la procédure native

Remplissez la table avec deux lignes de données.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Vient ensuite un appel EXECUTE à destination de la procédure stockée en mode natif.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

Voici la sortie réelle de PRINT :

-- 2 = Latest SalesOrderId, for CustomerId = 42

Guide de la documentation et étapes suivantes

Les exemples simples précédents vous donnent les bases pour l’apprentissage des fonctionnalités plus avancées de l’OLTP en mémoire. Les sections suivantes présentent les considérations particulières que vous pouvez être amené à connaître et indiquent les sources d’informations qui détaillent chacune d’elles.

Pourquoi les fonctionnalités de l’OLTP en mémoire fonctionnent-elles plus rapidement ?

Les sous-sections suivantes décrivent brièvement comment fonctionne l’OLTP en mémoire en interne pour fournir de meilleures performances.

Dans quelle mesure les performances des tables optimisées en mémoire sont-elles plus rapides ?

Double nature : une table optimisée en mémoire présente une double nature : une représentation en mémoire active et une autre sur le disque dur. Chaque transaction est validée dans les deux représentations de la table. Les transactions s’exécutent par rapport à la représentation en mémoire active, qui est beaucoup plus rapide. Les tables optimisées en mémoire tirent parti de la vitesse supérieure qu’offre la mémoire active par rapport au disque. En outre, grâce à la souplesse supérieure de la mémoire active, vous pouvez facilement mettre en place une structure de table plus avancée qui est optimisée pour la vitesse. De plus, comme la structure avancée ne fait pas appel à la pagination, elle évite la surcharge et la contention liées aux verrous et aux verrouillages tournants.

Aucun verrou : la table optimisée en mémoire s’appuie sur une approche optimiste des objectifs concurrents que sont, d’une part, l’intégrité des données et, d’autre part, la concurrence et le débit élevé. Pendant la transaction, la table ne place de verrous sur aucune version des lignes de données mises à jour. Cela peut réduire considérablement la contention dans certains systèmes à volumes élevés.

Versions de ligne : au lieu de verrous, la table optimisée en mémoire ajoute une nouvelle version d’une ligne mise à jour à la table elle-même, et non dans tempdb. La ligne d’origine est conservée jusqu’à ce que la transaction soit validée. Pendant la transaction, les autres processus peuvent lire la version d’origine de la ligne.

  • Si plusieurs versions d’une ligne sont créées pour une table basée sur disque, les versions de ligne sont stockées temporairement dans tempdb.

Moins de journalisation : les versions avant et après des lignes mises à jour sont conservées dans la table optimisée en mémoire. La paire de lignes fournit la plupart des informations qui sont traditionnellement écrites dans le fichier journal. Ainsi, le système écrit moins d’informations, et moins souvent, dans le journal. L’intégrité transactionnelle est néanmoins assurée.

Dans quelle mesure les performances des procédures natives sont-elles plus rapides ?

Convertir une procédure stockée interprétée normale en une procédure stockée compilée en mode natif réduit considérablement le nombre d’instructions à exécuter pendant l’exécution.

Compromis des fonctionnalités en mémoire

Comme cela est courant en informatique, les gains de performance procurés par les fonctionnalités en mémoire sont un compromis. Les meilleures fonctionnalités présentent des avantages qui compensent sensiblement les coûts supplémentaires qu’elles engendrent. Vous trouverez des instructions complètes sur les compromis dans l’article suivant :

Le reste de cette section répertorie les principaux éléments à prendre en considération pour la planification et les compromis.

Compromis des tables optimisées en mémoire

Estimer la mémoire : vous devez estimer la quantité de mémoire active que votre table optimisée en mémoire est appelée à consommer. Votre système informatique doit avoir une capacité de mémoire suffisante pour héberger une table optimisée en mémoire. Pour plus d'informations, consultez le site Internet suivant :

Partitionner votre table volumineuse : une façon de répondre à la demande d’une quantité de mémoire active élevée consiste à partitionner votre table volumineuse en parties en mémoire qui stockent les lignes de données récentes à chaud , tandis que les autres parties sur le disque comportent les lignes héritées à froid (telles que les commandes qui ont été entièrement livrées et terminées). Ce partitionnement est un processus manuel de conception et d’implémentation. Consultez l'article :

Compromis des procédures natives

  • Une procédure stockée compilée en mode natif ne peut pas accéder à une table sur disque. Une procédure native ne peut accéder qu’à des tables optimisées en mémoire.
  • Quand une procédure native s’exécute pour la première fois après la toute dernière remise en ligne du serveur ou de la base de données, elle doit être recompilée une fois. Cette opération retarde l’exécution de la procédure native.

Observations importantes sur les tables optimisées en mémoire

Lesindex pour les tables optimisées en mémoire sont, à certains égards, différents des index sur les tables sur disque traditionnelles. Les index de hachage sont disponibles uniquement pour les tables à mémoire optimisée.

Vous devez vous assurer qu’il y aura suffisamment de mémoire active pour votre table optimisée en mémoire planifié et ses index. Consultez l'article :

Vous pouvez déclarer une table optimisée en mémoire avec DURABILITY = SCHEMA_ONLY :

  • Cette syntaxe indique au système d’ignorer toutes les données de la table optimisée en mémoire quand la base de données est déconnectée. Seule la définition de table est conservée.
  • Quand la base de données est remise en ligne, la table optimisée en mémoire est rechargée, vide, en mémoire active.
  • Les tables SCHEMA_ONLY peuvent constituer une bonne alternative aux tables #temporary dans tempdb, quand plusieurs milliers de lignes sont impliqués.

Vous pouvez aussi déclarer des variables de table comme mémoire optimisée. Consultez l'article :

Observations importantes sur les modules compilés en mode natif

Les types de modules compilés en mode natif disponibles par le biais de Transact-SQL sont les suivants :

Une fonction définie par l’utilisateur compilée en mode natif s’exécute plus rapidement qu’une fonction définie par l’utilisateur interprétée. Voici quelques éléments à prendre en considération avec les fonctions définies par l’utilisateur :

  • Quand une instruction T-SQL SELECT utilise une fonction définie par l’utilisateur, celle-ci est toujours appelée une fois par ligne retournée.
    • Les fonctions définies par l’utilisateur ne s’exécutent jamais en ligne et, à la place, sont toujours appelées.
    • La distinction « interprété/compilé » est moins importante que la surcharge d’appels répétés inhérente à toutes les fonctions définies par l’utilisateur.
    • La surcharge d’appels des fonctions définies par l’utilisateur reste souvent acceptable dans la pratique.

Pour obtenir des données de test et des explications sur les performances des fonctions définies par l’utilisateur natives, consultez :

Guide de la documentation pour les tables optimisées en mémoire

Consultez les autres articles suivants qui traitent de considérations spéciales sur les tables à mémoire optimisée :

Guide de la documentation pour les procédures natives

L’article suivant, et ses articles enfants dans la table des matières, décrivent en détail les procédures stockées compilées en mode natif.

Voici des articles qui contiennent du code pour illustrer les gains de performance que vous pouvez obtenir à l’aide de l’OLTP en mémoire :