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 OLTP en mémoire 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 OLTP en mémoire, à 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

Les technologies en mémoire sont disponibles aux niveaux Premium et Critique pour l’entreprise.

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 :

  • Vous verrez l’impact de ces technologies sur le stockage et les limites de taille des données.
  • Vous verrez ensuite comment gérer le déplacement de bases de données qui exploitent ces technologies entre les différents niveaux tarifaires.
  • Vous verrez deux exemples qui illustrent l'utilisation de l'OLTP en mémoire, ainsi que les 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 OLTP en mémoire 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. Étant donné que vous gérez la mémoire directement dans SQL Database, nous disposons du concept de quota pour les données utilisateur. Ce concept est appelé stockage OLTP en mémoire.

Chaque niveau tarifaire de base de données unique pris en charge et chaque niveau tarifaire de pool élastique inclut une certaine quantité de stockage OLTP en mémoire.

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 le niveau tarifaire de la base de données ou du pool.

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 MaxvCore pour les bases de données inférieur au nombre d’eDTU ou de vCores du pool dans son ensemble. Cette valeur maximale limite l’utilisation du stockage OLTP en mémoire, dans n’importe quelle base de données du pool, à la taille qui correspond au nombre d’eDTU.
  • Configurez une valeur Min-eDTU ou MinvCore 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 vCore configuré.

Changement des niveaux de service des bases de données qui utilisent des technologies OLTP en mémoire

Vous pouvez toujours mettre à niveau votre base de données vers un niveau supérieur, comme passer du niveau Usage général (vCore) au niveau Critique pour l’entreprise ou de Standard (DTU) à Premium. Les ressources et les fonctionnalités disponibles ne font qu’augmenter.

Toutefois, le passage à un niveau inférieur peut impacter négativement votre base de données. L’impact est particulièrement évident quand vous descendez du niveau Critique pour l’entreprise au niveau Usage général (ou de Premium à Standard ou De base) et que votre base de données contient des objets OLTP en mémoire. Vous pouvez facilement trouver des objets en mémoire dans votre base de données.

Les tables optimisées par la mémoire ne sont pas disponibles après la mise à niveau (même si elles restent visibles). Les mêmes considérations s’appliquent lors de la réduction du niveau tarifaire d’un pool élastique ou du déplacement d’une base de données avec des technologies en mémoire vers un pool élastique de niveau Usage général, Standard ou De base.

Important

OLTP en mémoire n’est pas pris en charge dans les niveaux Usage général, Standard ou De base. Vous ne pouvez donc pas déplacer une base de données qui contient des objets OLTP en mémoire vers un de ces niveaux. Avant de rétrograder la base de données vers un niveau standard/de base, supprimez toutes les tables à mémoire optimisée et tous les types de table, ainsi que tous les modules T-SQL compilés en mode natif.

Effectuer un scale-down des ressources dans le niveau Critique pour l’entreprise : Les données des tables à mémoire optimisée doivent tenir dans le stockage OLTP en mémoire qui est associé au niveau de la base de données ou de l’instance gérée, ou qui est disponible dans le pool élastique. Si vous essayez d’effectuer un scale-down du niveau ou de déplacer la base de données dans un pool qui n’a pas de stockage OLTP en mémoire suffisant, l’opération échoue.

Déterminer si des objets en mémoire existent

Vous pouvez comprendre 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 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 de modèles columnstore pour organiser vos données :

  • Columnstore en cluster où toutes les données de la table sont organisées en colonnes. Dans ce modèle, 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. Le modèle columnstore en cluster permet é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. Choisissez ce modèle 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 au format columnstore est utilisé pour les requêtes analytiques. Ce modèle permet un traitement transactionnel-analytique hybride (HTAP) : la possibilité d’exécuter une analytique performante 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 doit être inférieure à celle de l’index B-tree équivalent.

Notes

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 en mémoire peut dépasser la quantité de mémoire disponible.

Taille des données et stockage pour les index columnstore

Le volume des index columntore 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. De plus, la 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.

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

Le passage d’une base de données unique au niveau De base ou Standard n’est peut-être pas possible si le niveau de votre cible est inférieur à S3. Les index columnstore sont pris en charge uniquement sur le niveau tarifaire Critique pour l’entreprise/Premium et sur le niveau Standard, S3 et supérieur, et non sur le niveau De base. Lors de la rétrogradation de votre base de données vers un niveau non pris en charge, votre index columnstore devient indisponible. Le système maintient l’index columnstore, mais il ne tire jamais profit de l’index. Si vous mettez à niveau ultérieurement vers un niveau pris en charge, l’index columnstore est immédiatement prêt à être exploité à nouveau.

Si vous disposez d’un index columnstore en cluster, la totalité de la table devient indisponible après le passage à une version antérieure. Supprimez tous les index columnstore cluster (et remplacez par les index cluster rowstore) avant de rétrograder votre base de données vers un niveau ou un niveau non pris en charge.

Contenu connexe