Optimiser les performances grâce aux technologies en mémoire dans Azure SQL Managed Instance

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 instance managée SQL. 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 :

  • Transactionnel (traitement transactionnel en ligne (OLTP)), dans lequel la plupart des requêtes lisent ou mettent à jour de plus petits ensembles de données, par exemple les opérations de création/lecture/mise à jour/suppression (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 Managed Instance prend en charge 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 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 illustrant l’utilisation de l’OLTP en mémoire, ainsi que des index columnaires.

Pour plus d’informations sur l’OLTP 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 dans lequel chaque ligne est un objet 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.

Remarque

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, assurez-vous d’utiliser une instance gérée SQL disposant de 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. 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

Réduire votre configuration matérielle ou votre nombre de vCore peut affecter négativement votre instance gérée SQL.

Les données des tables optimisées en mémoire doivent tenir dans la limite de stockage OLTP en mémoire correspondant à votre configuration matérielle et à votre nombre de vCores. Si vous essayez de passer à une configuration qui ne dispose pas d’un espace de stockage OLTP en mémoire suffisant, l’opération échoue.

Déterminer si des objets en mémoire existent

Il existe un moyen programmatique de déterminer si une base de données donnée dans votre instance gérée SQL 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 atteindre un taux de compression des données jusqu’à 10 fois supérieur à la taille des données non 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.
  • Magasin de colonnes non clusterisé où les données sont stockées dans une table traditionnelle en magasin de lignes et où il existe un index au format de magasin de colonnes, 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 une table rowstore, optimisée pour l’accès à un petit ensemble de lignes, tandis que les requêtes OLAP sont exécutées sur un index columnstore, qui constitue un meilleur choix pour les parcours 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 clusterisés ne diminuent pas la taille des données, car le jeu de données d’origine est conservé dans la table rowstore d’origine sans modification. Toutefois, la taille de l’index columnstore supplémentaire devrait être d’un ordre de grandeur inférieur à celle de l’index B-tree équivalent.

Remarque

La technologie de stockage en colonnes en mémoire conserve uniquement les données nécessaires au traitement en mémoire, tandis que le reste des données, qui ne peut pas y tenir, est stocké 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, la seule limite à la taille des index est la taille totale maximale de la base de données. Pour plus d’informations, consultez Limites de ressources Azure SQL Managed Instance. Azure SQL Managed Instance prend en charge les index columnstore dans tous les tiers.

Lorsque vous utilisez des index columnstore clusterisés, la compression par colonne est utilisée pour le stockage de la 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 d’une taille maximale de 1 téraoctet (To) et que vous obtenez un taux de compression de 10 à l’aide d’index columnstore, vous pouvez stocker 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 de nombreux index non clusterisés traditionnels par un seul index columnstore, vous pouvez tout de même constater une réduction globale de l’espace de stockage occupé par la table.

Contenu connexe

  • Last updated on