Vue d’ensemble et scénarios d’utilisation de l’OLTP en mémoire

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

In-Memory OLTP est la technologie de premier plan disponible dans les SQL Server et les SQL Database pour optimiser les performances du traitement transactionnel, de l’ingestion de données, du chargement des données et des scénarios de données temporaires. Cet article inclut une vue d’ensemble de cette technologie et une présentation des scénarios d’usage de l’OLTP en mémoire. Grâce à ces informations, vous pourrez déterminer si l’OLTP en mémoire est adapté à votre application. À la fin de cet article, vous trouverez un exemple illustrant les objets de l’OLTP en mémoire, ainsi que des liens vers une démonstration des performances de cette technologie et vers des ressources que vous pourrez utiliser pour la suite.

Cet article décrit la technologie OLTP In-Memory dans les SQL Server et les SQL Database. Pour plus d’informations sur les données en mémoire dans Azure SQL, consultez Optimiser les performances à l’aide des technologies en mémoire dans Azure SQL Base de données et Azure SQL Managed Instance et Blog : In-Memory OLTP dans Azure SQL Base de données.

Vue d’ensemble de l’OLTP en mémoire

L’OLTP en mémoire peut offrir des gains de performance considérables pour les charges de travail appropriées. Si certains clients ont constaté un gain de performances multiplié par 30 dans certains cas, les gains réellement obtenus dépendent de la charge de travail.

Mais d’où proviennent exactement ces gains de performance ? En substance, In-Memory OLTP améliore les performances du traitement des transactions en rendant l’accès aux données et l’exécution des transactions plus efficaces, et en supprimant la contention de verrous et de verrous entre les transactions exécutées simultanément. In-Memory OLTP n’est pas rapide, car il est en mémoire ; il est rapide, car il est optimisé autour des données en mémoire. Les algorithmes de stockage des données, d’accès et de traitement ont été entièrement repensés pour tirer parti des dernières améliorations en matière de calcul en mémoire et haute simultanéité.

Maintenant, ce n’est pas parce que les données vivent en mémoire que vous les perdez en cas de défaillance. Par défaut, toutes les transactions présentent une durabilité complète. Vous bénéficiez donc des mêmes garanties de durabilité que pour toute autre table de SQL Server : dans le cadre de la validation de transaction, toutes les modifications sont écrites dans le journal des transactions sur le disque. Si une défaillance survient après la validation de la transaction, vos données sont présentes lorsque la base de données est remise en ligne. En outre, In-Memory OLTP fonctionne avec toutes les fonctionnalités de haute disponibilité et de récupération d’urgence de SQL Server, comme les groupes de disponibilité Always On, les instances de cluster de basculement Always On (SQL Server), la sauvegarde/restauration, etc.

Pour utiliser In-Memory OLTP dans votre base de données, vous utilisez un ou plusieurs des types d’objets suivants :

  • Lestables optimisées en mémoire servent à stocker les données utilisateur. Vous déclarez qu’une table doit être optimisée en mémoire au moment de sa création.
  • Lestables non durables sont utilisées pour les données temporaires, soit pour la mise en cache, soit pour le jeu de résultats intermédiaire (à la place des tables temporaires traditionnelles). Une table non durable est une table à mémoire optimisée déclarée avec DURABILITY=SCHEMA_ONLY, ce qui signifie que les modifications apportées à ces tables n’entraînent aucune E/S. Cela évite la consommation de ressources d’E/S de journal lorsque la durabilité n’est pas un critère important.
  • Lestypes de tables optimisées en mémoire sont utilisés pour les paramètres table, ainsi que pour les jeux de résultats intermédiaires dans les procédures stockées. Ils peuvent être utilisés au lieu des types de tables traditionnels. Les variables de table et les paramètres table qui sont déclarés à l’aide d’un type de table optimisée en mémoire héritent des avantages des tables optimisées en mémoire non durables : accès efficace aux données et absence d’E/S.
  • Lesmodules T-SQL compilés en mode natif permettent d’accélérer encore plus l’exécution d’une transaction individuelle en réduisant les cycles processeur requis pour traiter les opérations. Vous déclarez qu’un module Transact-SQL doit être compilé en mode natif au moment de sa création. Les modules T-SQL suivants peuvent être compilés en mode natif : procédures stockées, déclencheurs et fonctions scalaires définies par l’utilisateur.

In-Memory OLTP est intégré à SQL Server et SQL Database. Étant donné que ces objets se comportent comme leurs équivalents traditionnels, vous pouvez souvent bénéficier d’avantages en matière de performances tout en apportant seulement des modifications minimales à la base de données et à l’application. De plus, vous pouvez avoir des tables optimisées en mémoire et des tables sur disque traditionnelles dans la même base de données, et exécuter simultanément des requêtes sur ces deux types de tables. Vous trouverez un script Transact-SQL avec un exemple pour chacun de ces types d’objets en bas de cet article.

Scénarios d’utilisation pour In-Memory OLTP

In-Memory OLTP n’est pas un bouton go-fast magique et ne convient pas à toutes les charges de travail. Par exemple, les tables à mémoire optimisée ne réduisent pas l’utilisation du processeur si la plupart des requêtes effectuent des opérations d’agrégation sur de grandes plages de données. Les index ColumnStore aident à ce scénario.

Voici une liste de scénarios et de modèles d’application dans lesquels nous avons vu des clients réussir avec In-Memory OLTP.

Traitement transactionnel à débit élevé et latence faible

C’est le scénario principal pour lequel nous avons créé l’OLTP en mémoire : prendre en charge de grands volumes de transactions, avec une latence faible homogène pour les transactions individuelles.

Les scénarios de charge de travail les plus fréquents sont les suivants : négoce d’instruments financiers, paris sportifs, jeux mobiles et diffusion publicitaire. Un autre modèle courant observé est un « catalogue » souvent lu et/ou mis à jour. Par exemple, vous avez des fichiers volumineux, qui sont répartis sur plusieurs nœuds de cluster, et vous cataloguez l’emplacement de chaque partition de fichier dans une table à mémoire optimisée.

Considérations relatives à l’implémentation

Utilisez des tables optimisées en mémoire pour vos tables de transactions principales, c’est-à-dire pour les tables qui présentent les transactions les plus critiques pour les performances. Utilisez des procédures stockées compilées en mode natif pour optimiser l’exécution de la logique associée à la transaction commerciale. Plus vous pourrez transmettre la logique aux procédures stockées dans la base de données, plus vous tirerez profit de l’OLTP en mémoire.

Pour commencer avec une application existante :

  1. Utilisez le rapport d’analyse des performances de transaction pour identifier les objets à migrer.
  2. Utilisez le Conseiller d’optimisation de la mémoire et le Conseiller de compilation native pour faciliter la migration.

Intégration de données, IoT (Internet des objets) compris

L’OLTP en mémoire est efficace pour ingérer en même temps d’importants volumes de données provenant de nombreuses sources différentes. Et il est souvent avantageux d’ingérer des données dans une base de données SQL Server par rapport à d’autres destinations, car SQL Server accélère l’exécution des requêtes sur les données et vous permet d’obtenir des insights en temps réel.

Les modèles d’application courants sont les suivants :

  • ingestion de relevés et d’événements de capteurs à des fins de notification et d’analyse d’historique ;
  • gestion des mises à jour par lot, même à partir de plusieurs sources, tout en réduisant l’impact sur la charge de travail de lecture simultanée.

Considérations relatives à l’implémentation

Utilisez une table optimisée en mémoire pour l’intégration de données. Si l’intégration consiste principalement en des insertions (plutôt que des mises à jour) et l’encombrement de stockage des données dans l’OLTP en mémoire est un critère important :

  • Utilisez un travail pour décharger régulièrement des données par lot dans une table sur disque avec un index columnstore cluster, à l’aide d’un travail qui effectue INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; ou
  • Utilisez une table à mémoire optimisée temporelle pour gérer les données d’historique ; dans ce mode, les données d’historique se trouvent sur le disque et le déplacement des données est géré par le système.

Le référentiel d’exemples SQL Server contient une application de réseau de distribution d’électricité intelligent qui utilise une table optimisée en mémoire temporelle, un type de table optimisée en mémoire et une procédure stockée compilée en mode natif afin d’accélérer l’intégration de données tout en gérant l’encombrement de stockage des données de capteur dans l’OLTP en mémoire :

Mise en cache et état de session

La technologie OLTP In-Memory fait du moteur de base de données dans les bases de données SQL Server ou Azure SQL une plateforme attrayante pour la maintenance de l’état de session (par exemple, pour une application ASP.NET) et pour la mise en cache.

ASP.NET’état de session est un cas d’usage réussi pour In-Memory OLTP. Avec SQL Server, un client est parvenu à atteindre 1,2 million de requêtes par seconde. Dans le même temps, il a commencé à utiliser l’OLTP en mémoire pour les besoins de mise en cache de toutes les applications de niveau intermédiaire de l’entreprise. Détails : Comment bwin utilise SQL Server 2016 (13.x) In-Memory OLTP pour obtenir des performances et une mise à l’échelle sans précédent

Considérations relatives à l’implémentation

Vous pouvez utiliser des tables à mémoire optimisée non durables comme magasin clé-valeur simple en stockant un objet BLOB dans une colonne varbinary(max). Vous pouvez également implémenter un cache semi-structuré avec prise en charge JSON dans SQL Server et SQL Database. Enfin, vous pouvez créer un cache relationnel complet via des tables non durables présentant un schéma relationnel complet, avec divers types et contraintes de données.

Prise en main de l’optimisation de la mémoire ASP.NET l’état de session à l’aide des scripts publiés sur GitHub pour remplacer les objets créés par le fournisseur d’état de session SQL Server intégré : aspnet-session-state

Étude de cas client

Remplacement d’objets tempdb

Utilisez des tables non durables et des types de tables à mémoire optimisée pour remplacer vos structures traditionnelles tempdb , telles que les tables temporaires, les variables de table et les paramètres table (TVP).

Les variables de table et les tables non durables optimisées en mémoire réduisent généralement l’utilisation du processeur par rapport aux variables de table et aux tables #temp traditionnels, et suppriment complètement les E/S de journal.

Considérations relatives à l’implémentation

Pour commencer, consultez : Improving temp table and table variable performance using memory optimization. (Amélioration des performances des tables temporaires et des variables de table à l’aide de l’optimisation de la mémoire.)

Étude de cas client

  • Un client est parvenu à améliorer les performances de 40 % simplement en remplaçant les paramètres table traditionnels par des paramètres table à mémoire optimisée : High Speed IoT Data Ingestion Using In-Memory OLTP in Azure (Intégration de données IoT haute vitesse à l’aide de l’OLTP en mémoire dans Azure)

ETL (extraction, transformation, chargement)

Les flux de travail ETL incluent souvent le chargement de données dans une table de mise en lots, les transformations de données et le chargement dans les tables finales.

Utilisez des tables optimisées en mémoire non durables pour la mise en lots des données. Elles suppriment complètement les E/S et optimisent l’efficacité de l’accès aux données.

Considérations relatives à l’implémentation

Si vous effectuez des transformations sur la table de mise en lots dans le cadre du flux de travail, vous pouvez utiliser des procédures stockées compilées en mode natif pour accélérer ces transformations. Si vous pouvez procéder à ces transformations en parallèle, l’optimisation de la mémoire vous offre des avantages supplémentaires en matière de mise à l’échelle.

Exemple de script

Avant de pouvoir commencer à utiliser l’OLTP en mémoire, vous devez créer un groupe de fichiers MEMORY_OPTIMIZED_DATA. Nous vous recommandons également d’utiliser le niveau de compatibilité de base de données 130 (ou supérieur) et de définir l’option de base de données MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT sur ON.

Vous pouvez utiliser le script situé à l’emplacement suivant pour créer le groupe de fichiers dans le dossier de données par défaut et configurer les paramètres recommandés :

L’exemple de script suivant illustre In-Memory objets OLTP que vous pouvez créer dans votre base de données.

Commencez par configurer la base de données pour In-Memory OLTP.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Vous pouvez créer des tables avec une durabilité différente :

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

Vous pouvez créer un type de table en tant que table en mémoire.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

Vous pouvez créer une procédure stockée compilée en mode natif. Pour plus d’informations, consultez Appel de procédures stockées compilées en mode natif à partir d’applications d’accès aux données.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

Ressources supplémentaires

Voir aussi

Étapes suivantes