Optimisation des performances à l’aide des technologies en mémoire dans SQL Database

  • Article

S’applique à : Azure SQL Database

Les technologies en mémoire vous permettent d’améliorer les performances de votre application, et potentiellement de réduire le coût de votre base de données.

Quand utiliser les technologies en mémoire

Grâce aux technologies en mémoire, vous pouvez améliorer vos performances au niveau des charges de travail suivantes :

  • Transactionnelle (traitement transactionnel en ligne (OLTP)) où la plupart des demandes lisent ou mettent à jour un plus petit jeu de données (par exemple, les opérations CRUD).
  • Analytique (traitement analytique en ligne (OLAP)) où la plupart des requêtes ont des calculs complexes à des fins de création de rapports, ainsi que des processus planifiés régulièrement qui effectuent des opérations de charge (ou de chargement en bloc) et/ou écrivent des modifications de données dans des tables existantes. Souvent, les charges de travail OLAP sont mises à jour régulièrement à partir de charges de travail OLTP.
  • Mixte (traitement transactionnel/analytique hybride (HTAP)) où les requêtes OLTP et OLAP sont exécutées sur le même jeu de données.

Les technologies en mémoire peuvent améliorer les performances de ces charges de travail en conservant dans la mémoire les données à traiter, en utilisant la compilation native des requêtes ou un traitement avancé comme le traitement par lots et des instructions SIMD qui sont disponibles sur le matériel sous-jacent.

Vue d’ensemble

Azure SQL Database comprend les technologies en mémoire suivantes :

  • OLTP en mémoire augmente le nombre de transactions par seconde et réduit la latence du traitement transactionnel. Les scénarios qui bénéficient de l’OLTP en mémoire sont : le traitement de transactions haut débit, notamment les données commerciales et de jeux, l’ingestion de données d’événements ou d’appareils IoT, la mise en cache, le chargement de données, les tables temporaires et les scénarios de variables de table.
  • Les index columnstore en cluster réduisent l’encombrement de stockage (jusqu'à 10 fois) et améliorent les performances des requêtes d’analyse et de création de rapports. Vous pouvez les utiliser avec des tables de faits dans vos mini-Data Warehouses pour faire tenir plus de données dans votre base de données et optimiser les performances. Vous pouvez également les utiliser avec des données historiques dans votre base de données opérationnelles pour archiver et être en mesure d’interroger jusqu’à 10 fois plus de données.
  • Les index columnstore sans cluster pour HTAP vous aident à obtenir un aperçu en temps réel de votre activité en interrogeant la base de données opérationnelle directement, sans avoir à exécuter de processus d’extraction, de transformation et de chargement (ETL) coûteux et à attendre que l’entrepôt de données se remplisse. Les index columnstore sans cluster permettent une exécution rapide des requêtes d’analyse sur la base de données OLTP, tout en réduisant l’impact sur la charge de travail opérationnelle.
  • Les index columnstore en cluster à mémoire optimisée pour HTAP vous permettent d’effectuer un traitement transactionnel rapide et d’exécuter simultanément et très rapidement des requêtes analytiques sur les mêmes données.

Les index Columnstore et In-Memory OLTP ont été introduits respectivement dans SQL Server en 2012 et 2014. Azure SQL Database, Azure SQL Managed Instance et SQL Server partagent la même implémentation pour les technologies en mémoire.

Remarque

Pour obtenir un didacticiel détaillé pas à pas pour illustrer les avantages des performances de la technologie In-Memory OLTP, à l’aide de l’exemple AdventureWorksLT de base de données et de ostress.exe, consultez l’exemple en mémoire dans Azure SQL Database.

Avantages de la technologie en mémoire

Les technologies en mémoire vous aident également à réduire les coûts grâce à un traitement plus efficace des requêtes et des transactions. En général, il n’est pas nécessaire de mettre à jour le niveau de tarification de la base de données pour obtenir des gains de performances. Dans certains cas, vous pourriez même être en mesure de réduire le niveau de tarification, tout en bénéficiant d’une amélioration des performances grâce aux technologies en mémoire.

À l’aide de l’OLTP en mémoire, Quorum Business Solutions a pu doubler leur charge de travail tout en améliorant les DTU de 70 %. Pour plus d’informations, consultez OLTP en mémoire dans la base de données Azure SQL.

Remarque

In-Memory OLTP est disponible dans les niveaux Premium (DTU) et Critique pour l'entreprise (vCores) d’Azure SQL Database. Le niveau de service Hyperscale prend en charge un sous-ensemble d’objets OLTP en mémoire. Pour plus d’informations, consultez Limitations d’hyperscale.

Les index Columnstore sont disponibles dans tous les niveaux de service, à l’exception du niveau De base et du niveau Standard lorsque l’objectif de service est inférieur à S3. Pour plus d’informations, consultez Changement de niveaux de service des bases de données contenant des index columnstore.

Cet article décrit des aspects de l’OLTP en mémoire et des index columnstore qui sont spécifiques à Azure SQL Database, et inclut également des exemples qui vous permettent de voir :

  • L’impact de ces technologies sur le stockage et les limites de taille des données.
  • Comment gérer le déplacement de bases de données qui exploitent ces technologies entre les différents niveaux tarifaires.
  • Utilisation illustrée de In-Memory OLTP, ainsi que d’index columnstore.

Pour plus d’informations sur les technologies en mémoire dans SQL Server, consultez :

OLTP en mémoire

La technologie OLTP en mémoire fournit des opérations d’accès aux données extrêmement rapides en conservant toutes les données en mémoire. Elle utilise également des index spécialisés, la compilation native des requêtes et un accès aux données sans verrou pour améliorer les performances de la charge de travail OLTP. Il existe deux façons d’organiser vos données OLTP en mémoire :

  • Le format rowstore à mémoire optimisée où chaque ligne est un objet de mémoire distinct. Il s’agit d’un format OLTP en mémoire classique optimisé pour les charges de travail OLTP hautes performances. Il existe deux types de tables à mémoire optimisée qui peuvent être utilisées dans le format rowstore à mémoire optimisée :

    • Tables durables (SCHEMA_AND_DATA) où les lignes placées en mémoire sont conservées après le redémarrage du serveur. Ce type de tables se comporte comme une table rowstore traditionnelle avec en plus les avantages des optimisations en mémoire.
    • Tables non durables (SCHEMA_ONLY) où les lignes ne sont pas conservées après le redémarrage. Ce type de table est conçu pour les données temporaires (par exemple, le remplacement de tables temporaires) ou les tables dans lesquelles vous devez rapidement charger des données avant de les déplacer vers une table persistante (les tables de mise en lots).

  • Le format columnstore à mémoire optimisée où les données sont organisées dans un format de colonne. Cette structure est conçue pour les scénarios HTAP où vous avez besoin d’exécuter des requêtes analytiques sur la même structure de données que celle où votre charge de travail OLTP s’exécute.

Notes

La technologie OLTP en mémoire est conçue pour les structures de données qui peuvent résider entièrement en mémoire. Comme les données en mémoire ne peuvent pas être déchargées sur le disque, utilisez une base de données avec suffisamment de mémoire. Consultez Limite de la taille des données et du stockage pour In-Memory OLTP pour plus d’informations.

Seuil de la taille des données et du stockage pour l’OLTP en mémoire

l’OLTP en mémoire inclut des tables optimisées en mémoire, qui sont utilisées pour stocker des données de l’utilisateur. Le volume de ces tables doit tenir dans la mémoire. Chaque objectif de service a un quota de mémoire ou une limite pour les tables mémoire optimisées, appelée stockage In-Memory OLTP.

Chaque objectif de service de base de données unique pris en charge et chaque objectif de service de pool élastique comprend une certaine quantité de stockage In-Memory OLTP :

Les éléments suivants sont pris en compte dans votre plafond de stockage OLTP en mémoire :

  • Lignes de données utilisateur actives dans des tables optimisées en mémoire et variables de table. Notez que les anciennes versions des lignes ne comptent pas dans le seuil.
  • Index de tables optimisées en mémoire.
  • Coûts de fonctionnement des opérations ALTER TABLE.

Si vous atteignez le seuil, vous recevrez une erreur de quota et vous ne serez plus en mesure d’insérer ou de mettre à jour des données. Pour atténuer cette erreur, supprimez des données ou augmentez l’objectif de service de la base de données ou du pool élastique.

Pour plus d’informations sur la surveillance de l’utilisation du stockage OLTP en mémoire et la configuration des alertes lorsque le seuil est presque atteint, consultez Surveiller le stockage en mémoire.

À propos des pools élastiques

Avec les pools élastiques, le stockage OLTP en mémoire est partagé entre toutes les bases de données dans le pool. Par conséquent, l’utilisation dans une base de données peut potentiellement affecter les autres bases de données. Voici deux solutions :

  • Configurez une valeur Max eDTU ou Max vCore pour les bases de données inférieur au nombre d’eDTU ou de vCores du pool dans son ensemble. Ce maximum limite également l’utilisation du stockage OLTP en mémoire dans n’importe quelle base de données du pool proportionnellement.
  • Configurez une valeur Min eDTU ou Min vCore supérieure à 0. Cette valeur minimale garantit que chaque base de données dans le pool possède la quantité de stockage OLTP en mémoire disponible qui correspond à l’élément Min eDTU ou Min vCore configuré.

Changement des niveaux de service des bases de données qui utilisent des technologies In-Memory OLTP

OLTP en mémoire n’est pas pris en charge dans les niveaux de service Usage général, Standard ou De base. Vous ne pouvez donc pas faire évoluer une base de données qui contient des objets OLTP en mémoire vers un de ces niveaux. Si vous souhaitez mettre à l’échelle une base de données vers l’un de ces niveaux de service, supprimez toutes les tables et types de tables optimisés en mémoire, ainsi que tous les modules T-SQL compilés en mode natif, ou convertissez-les en objets basés sur disque et modules T-SQL standard.

Lorsque vous effectuez un scale-down d’une base de données Critique pour l’entreprise ou Premium, les données des tables optimisées en mémoire doivent s’adapter au stockage OLTP en mémoire disponible dans l’objectif de service de destination de la base de données ou du pool élastique. Si vous essayez de réduire la base de données ou le pool élastique, ou de déplacer une base de données dans un pool élastique, et que l'objectif de service de destination ne dispose pas de suffisamment de stockage In-Memory OLTP disponible, l'opération échoue.

Déterminer si des objets In-Memory OLTP existent

Vous pouvez trouver par programmation si une base de données spécifique prend en charge l’OLTP en mémoire. Vous pouvez exécuter la requête Transact-SQL suivante :

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Si la requête renvoie 1, l’OLTP en mémoire est pris en charge dans cette base de données.

Les requêtes suivantes identifient tous les objets à supprimer avant qu'une base de données puisse être rétrogradée au niveau de service Usage général, Standard ou De base :

SELECT * FROM sys.tables WHERE is_memory_optimized = 1;
SELECT * FROM sys.table_types WHERE is_memory_optimized = 1;
SELECT * FROM sys.sql_modules WHERE uses_native_compilation = 1;

Columnstore en mémoire

La technologie columnstore en mémoire vous offre la possibilité de stocker et d’interroger une grande quantité de données dans les tables. La technologie columnstore utilise le format de stockage de données en colonnes et le traitement de requête par lots pour obtenir jusqu’à 10 fois les performances de requêtes dans les charges de travail OLAP par rapport au stockage traditionnel en lignes. Vous pouvez également obtenir jusqu’à 10 fois la compression de données par rapport à la taille des données décompressées.

Il existe deux types d’index columnstore pour organiser vos données :

  • Columnstore en cluster où toutes les données de la table sont organisées en colonnes. Dans ce type d’index, toutes les lignes de la table sont placées sous forme de colonnes qui compressent fortement les données et vous permet d’exécuter des requêtes analytiques et des rapports rapides sur la table. Selon la nature de vos données, leur taille peut être réduite de 10 à 100 fois. Les index columnstore en cluster permettent également une ingestion rapide de grandes quantités de données (chargement en masse), grâce à la compression des gros lots de données supérieurs à 100 000 lignes avant leur stockage sur le disque. Ce type d’index est un bon choix pour les scénarios d’entrepôt de données classiques.
  • Columnstore non-cluster où les données sont stockées dans une table rowstore traditionnelle et où un index supplémentaire au format columnstore est utilisé pour les requêtes analytiques. Ce type d’index permet un traitement transactionnel-analytique hybride (HTAP) : la possibilité d’exécuter une analytique rapide en temps réel sur une charge de travail transactionnelle. Les requêtes OLTP sont exécutées sur la table rowstore qui est optimisée pour l’accès à un petit ensemble de lignes, tandis que les requêtes OLAP sont exécutées sur l’index columnstore qui est le meilleur choix pour les analyses et l’analytique. L'optimiseur de requête choisit dynamiquement le format rowstore ou columnstore en fonction de la requête. Les index columnstore non-cluster ne réduisent pas la taille des données, car le jeu de données d’origine est conservé dans la table rowstore initiale sans aucun changement. Toutefois, la taille de l'index columnstore supplémentaire est inférieure de plusieurs ordres de grandeur à celle de l'index B-tree équivalent.

Remarque

La technologie columnstore en mémoire conserve uniquement les données qui sont nécessaires pour le traitement dans la mémoire, tandis que les données qui ne tiennent pas dans la mémoire sont stockées sur disque. Par conséquent, la quantité de données dans les structures columnstore peut dépasser la quantité de mémoire disponible.

Taille des données et stockage pour les index columnstore

Le volume des index columnstore ne doit pas forcément tenir dans la mémoire. Par conséquent, le seul seuil de taille des index est la taille de base de données globale maximale décrite dans les articles Modèle d’achat DTU et Modèle d’achat vCore.

Lors de l’utilisation d’index columnstore en cluster, la compression en colonnes est utilisée pour le stockage de table de base. Cette compression peut réduire considérablement l’encombrement de stockage des données utilisateur, ce qui signifie que vous pouvez entrer davantage de données dans la base de données. Le taux de compression peut être accrue d’avantage avec la compression d’archivage en colonnes. Le taux de compression que vous pouvez obtenir dépend de la nature des données, mais une compression égale à 10 fois n’est pas rare.

Par exemple, si vous disposez d’une base de données avec une taille maximale de 1 téraoctet (To), et que vous atteignez une compression de 10 fois à l’aide d’index columntore, vous pouvez afficher un total de 10 To de données utilisateur dans la base de données.

Lors de l’utilisation d’index columnstore sans cluster, la table de base est toujours stockée au format rowstore traditionnel. Par conséquent, les économies en stockage ne sont pas aussi significatives qu’avec des index columnstore en cluster. Toutefois, si vous remplacez un nombre d’index sans cluster traditionnels par un index columntore unique, vous pouvez toujours voir une économie globale d’espace de stockage pour la table. Vous pouvez également utiliser la compression de données rowstore pour la table de base.

Changement de niveaux de service des bases de données contenant des index columnstore

Si vous utilisez le modèle d’achat DTU et que votre base de données contient des index columnstore, votre application peut cesser de fonctionner si vous mettez à l’échelle votre base de données sous l’objectif de service S3. Les index columnstore sont pris en charge uniquement dans les niveaux de service Hyperscale, Critique pour l’entreprise et Premium, ainsi que dans le niveau de service Standard si vous utilisez S3 et versions ultérieures. Les index columnstore ne sont pas pris en charge dans le niveau de service standard ou de base. Lors de la rétrogradation de votre base de données vers un objectif ou niveau de service non pris en charge, votre index columnstore devient indisponible. Le système gère l’index lorsque vous exécutez des instructions DML, mais il n’utilise jamais l’index. Si vous mettez à l’échelle ultérieurement vers un objectif ou niveau de service pris en charge, l’index columnstore est immédiatement prêt à être exploité à nouveau.

Si vous avez un index columnstore cluster, la table entière devient indisponible si la base de données est mise à l’échelle vers un niveau de service ou un objectif de service non pris en charge. Supprimez tous les index columnstore cluster, en les remplaçant par des index cluster rowstore ou des tas, avant l’opération de mise à l’échelle.

Contenu connexe