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

  • Article

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

OLTP en mémoire est la technologie de premier plan disponible dans SQL Server et SQL Database pour optimiser les performances du traitement transactionnel, l'ingestion des données, le chargement des données, et les 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.

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, l'OLTP en mémoire améliore les performances de traitement transactionnel 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 internes entre les transactions exécutées simultanément. L'OLTP en mémoire n'est pas rapide parce qu'il est en mémoire ; il est rapide grâce à l'optimisation 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é.

Le fait que les données se trouvent en mémoire ne signifie pas pour autant que vous les perdrez 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, l'OLTP en mémoire fonctionne avec toutes les fonctionnalités de haute disponibilité et récupération d'urgence de SQL Server, comme les groupes de disponibilité, les instances de cluster de basculement, la sauvegarde/restauration, etc.

Pour tirer parti de l'OLTP en mémoire dans votre base de données, vous devez utiliser 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 qui est déclarée avec DURABILITY=SCHEMA_ONLY, ce qui veut dire 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.
  • Les types de tables à mémoire optimisée sont utilisés pour les ensembles de lignes de paramètres table (TVP), ainsi que pour les jeux de résultats intermédiaires dans les procédures stockées. Les types de tables à mémoire optimisée 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.

L'OLTP en mémoire est intégré dans SQL Server et Azure SQL Database. Comme ces objets se comportent de la même manière que leurs homologues traditionnels, vous pouvez généralement améliorer les performances simplement en apportant quelques modifications minimes à 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. Consultez l'exemple de script Transact-SQL pour chacun de ces types d'objets plus avant dans cet article.

Scénarios d'usage de l'OLTP en mémoire

L'OLTP en mémoire n'est pas un bouton d'accélération 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.

Attention

Problème connu : pour les bases de données avec des tables mémoire optimisées, l’exécution d’une sauvegarde de journal transactionnelle sans récupération, puis l’exécution ultérieure d’une restauration du journal des transactions avec récupération, peut entraîner un processus de restauration de base de données non satisfait. Ce problème peut également affecter la fonctionnalité d’expédition de journaux. Pour contourner ce problème, l’instance SQL Server peut être redémarrée avant de lancer le processus de restauration.

Voici une liste de scénarios et de modèles d'application pour lesquels des clients ont pu tirer profit de l'OLTP en mémoire.

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 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. Il est également souvent plus intéressant d'ingérer des données dans une base de données SQL Server plutôt que dans d'autres destinations, car SQL Server rend l'exécution des requêtes sur les données plus rapide 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 les données par lot dans une table sur disque comportant un index columnstore groupé, à l'aide d'un travail qui exécute 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 en mémoire rend le moteur de base de données dans SQL Server ou les bases de données Azure SQL une plateforme attrayante pour maintenir l'état de session (par exemple, pour une application ASP.NET) et pour la mise en cache.

L'état de session ASP.NET est un cas d'utilisation très efficace pour l'OLTP en mémoire. 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 : How bwin is using SQL Server 2016 (13.x) In-Memory OLTP to achieve unprecedented performance and scale (Comment bwin utilise la technologie OLTP en mémoire de SQL Server 2016 (13.x) pour atteindre des performances et un nombre d'utilisateurs 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.

Pour bien démarrer, utilisez l'état de session ASP.NET à mémoire optimisée en tirant profit des scripts publiés sur GitHub afin de remplacer les objets créés par le fournisseur d'état de session intégré de SQL Server : aspnet-session-state.

Étude de cas client

Remplacement d'objet tempdb

Utilisez des tables non durables et des types de tables à mémoire optimisée pour remplacer vos structures standard basées sur tempdb, telles que les tables temporaires, les variables de table et les ensembles de lignes de 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 bien démarrer, 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 optimisés en mémoire : 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 :

Le script suivant illustre les objets de l'OLTP en mémoire que vous pouvez créer dans votre base de données.

Commencez par configurer la base de données pour quelle utilise l'OLTP en mémoire.

-- 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

Contenu connexe