Optimiser les performances en utilisant les technologies en mémoire d’Azure SQL Database et Azure SQL Managed Instance

  • Article

S’applique à :Azure SQL Managed Instance

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. OLTP en mémoire est disponible dans le niveau de service Critique pour l’entreprise d’Azure SQL Managed Instance.

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 et Azure SQL Managed Instance disposent des 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 en matière de 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 Managed Instance.

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. Une fois dans le niveau de service Critique pour l’entreprise d’Azure SQL Managed Instance, vous n’avez généralement pas besoin de mettre à niveau l’instance managée SQL 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.

Cet article décrit des aspects de l'OLTP en mémoire et des index columnstore qui sont spécifiques à Azure SQL Database et Azure SQL Managed Instance, 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. Ce concept est appelé stockage OLTP en mémoire.

Le niveau de service Critique pour l’entreprise inclut une certaine quantité de mémoire OLTP maximale en mémoire, déterminée par le nombre de vCores.

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.

Modifier la configuration matérielle ou le nombre de vCores

La rétrogradation de votre configuration matérielle ou du nombre de vCores peut avoir un impact négatif sur votre instance managée SQL.

Les données des tables mémoire optimisées doivent correspondre à la limite de stockage OLTP en mémoire pour votre configuration matérielle et votre nombre vCore. 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 à l’aide de la technologie en mémoire :

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 l’article Niveaux du service relatifs aux bases de données SQL. Pour plus d’informations, consultez Limites de ressources Azure SQL Managed Instance. SQL Managed Instance prend en charge les index columnstore dans tous les niveaux.

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.

Contenu connexe