Vue d’ensemble et scénarios d’utilisation

S’applique à : ouiSQL Server (toutes les versions prises en charge) OuiAzure SQL Database

OLTP en mémoire est la technologie de premier plan disponible dans SQL Server et SQL Database pour optimiser les performances du traitement transactionnel, de l’ingestion et 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 présente la technologie OLTP en mémoire dans SQL Server et SQL Database. Le billet de blog suivant contient une description détaillée des avantages en matière de performances et d’utilisation des ressources dans SQL Database : OLTP en mémoire dans Azure SQL Database

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. Cette technologie n’est pas rapide parce qu’elle est en mémoire, mais parce qu’elle est optimisée à travers la présence 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 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 dans 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. De plus, l’OLTP en mémoire est compatible avec toutes les fonctionnalités de haute disponibilité et de récupération d’urgence de SQL Server, comme Always On, la sauvegarde et la 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 :

  • Les tables 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.
  • Les tables 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 optimisée en mémoire 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 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.
  • Les modules 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.

OLTP en mémoire est intégré à SQL Server et SQL Database. Et comme ces objets se comportent de façon similaire aux objets standard équivalents, 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. Un script Transact-SQL présentant un exemple de chacun de ces types d’objets est fourni vers la fin de 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 (dans ce scénario, utilisez plutôt des index columnstore).

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 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. 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 cluster, à 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 temporelle à mémoire optimisée, un type de table à mémoire optimisée et une procédure stockée compilée en mode natif afin d’accélérer l’ingestion des 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 d’OLTP en mémoire rend SQL vraiment intéressant pour le maintien de 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 2015 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 aussi 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 tempdb standard, telles que les tables temporaires, les variables de table et les paramètres table.

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

Études de cas clients

  • 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)
  • SentryOne a considérablement amélioré sa capacité d’ingestion des données grâce à une latence quasi nulle dans sa solution de supervision. Pour cela, l’entreprise a remplacé les tables tempdb par les tables OLTP en mémoire, dans le cadre de ses améliorations de scalabilité d’entreprise : Solution provider breaks through performance ceiling with data monitoring innovation. (Un fournisseur de solution atteint des performances inédites en innovant dans le domaine de la supervision des données.)

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.

Considérations relatives à l’implémentation

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.

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 :

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
-- 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
-- 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
-- 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