SQL Server 2008

Performances des requêtes d'entrepôts de données

Sunil Agarwal and Torsten Grabs and Dr. Joachim Hammer

 

Vue d'ensemble:

• Optimisation de requêtes de jointure en étoile
• Parallélisme de tables partitionnées
• Compression de pages et de lignes
• Affichages indexés alignés sur les partitions

SQL Server 2008 offrira des capacités d'entreposage de données relationnelles beaucoup plus puissantes que son prédécesseur. Cependant, vous vous demanderez peut-être toujours comment utiliser cette toute nouvelle technologie pour créer un entrepôt de données performant pour l'aide à la décision pour des milliards de lignes ou vous

souhaiterez peut-être découvrir les fonctionnalités qui peuvent vous aider à obtenir des performances de requêtes optimales pour vos requêtes et rapports d'aide à la décision, ou connaître le type d'améliorations de performances auquel vous pouvez vous attendre de manière réaliste avec cette nouvelle version de SQL Server®.

Les questions se multiplient à mesure que nous approchons de la date de mise sur le marché de la version finale. Nous espérons que cet aperçu approfondi sur certaines des fonctionnalités d'entreposage de données associées aux performances principales dans SQL Server 2008 vous permettra de vous préparer à la version finale.

Conception de base de données logique : Modélisation dimensionnelle

Les applications métier transactionnelles disposent généralement d'un schéma de base de données normalisé. La conception de schéma de base de données logique pour l'entreposage de données relationnelles met un accent moindre sur la normalisation. De nombreuses conceptions d'entrepôt de données relationnelles suivent aujourd'hui une approche de modélisation dimensionnelle, rendue populaire par Ralph Kimball et Margy Ross dans leur livre intitulé The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling. (Boîte à outils de l'entreposage de données : guide complet de la modélisation dimensionnelle)

Si vous passez beaucoup de temps à l'entreposage de données, vous serez certainement familier avec les modèles de schémas communs pour les entrepôts de données relationnelles (tels que les schémas en étoile ou en flocon). La modélisation dimensionnelle fait la distinction entre les tables de dimension et les tables de faits. Les tables de dimension contiennent les données principales (telles que les produits, les clients, les magasins ou les pays) tandis que les tables de faits contiennent les données transactionnelles (telles que les ventes, les commandes, les achats ou les retours).

Les tables de dimension et les tables de faits sont liées par les relations clé primaire (PK, Primary Key)/clé étrangère (FK, Foreign Key). Vous constaterez que beaucoup d'entrepôts de données n'appliquent pas les contraintes FK comme une façon de maintenir les besoins de stockage à un minimum. Ceci enregistre la surcharge de stockage des index sous-jacents et conserve les coûts de maintenance de la table de faits au niveau le plus bas. Les tables de dimension dans un entrepôt de données sont souvent assez petites. Elles contiennent généralement des milliers voire plusieurs millions de lignes. En revanche, les tables de faits peuvent être très volumineuses et contenir des centaines de millions voire des milliards de lignes. C'est pourquoi la conception logique doit vraiment faire attention aux besoins de stockage de la table de faits.

Ce facteur de taille a également des implications dans le choix de la clé que vous faites à partir d'une table de dimension pour maintenir les relations table de dimension/table de faits. Les clés composites basées sur la clé d'entreprise de la dimension (c'est-à-dire l'identificateur réel de l'entité représentée par la dimension) couvrent normalement plusieurs colonnes. Gardez à l'esprit qu'il s'agit d'un problème pour la clé étrangère correspondante dans la table de faits étant donné que la clé composite multicolonnes sera répétée pour chaque ligne de table de faits.

Pour répondre à ceci, il est courant d'utiliser des petites clés de substitution pour implémenter les relations entre une table de faits et ses dimensions. La clé de substitution est une colonne d'identité de type entier qui est utilisée en tant qu'une clé primaire artificielle de la table de dimension. Avec la table de faits se reportant à la clé de substitution plus petite, la réduction des grandes tables de faits en vue des besoins de stockage est significative. La Figure 1 illustre un schéma d'entrepôt de données modélisées de manière dimensionnelle à l'aide des tables de faits et de dimension avec des clés de substitution.

Figure 1** Exemple de schéma en étoile avec une table de fait et deux tables de dimension **(Cliquer sur l'image pour l'agrandir)

La conception de schéma en flocon s'étale sur une ou plusieurs dimensions sur plusieurs niveaux (par exemple, le client, le pays et la région pour une dimension de client) et par conséquent, normalise les dimensions plus grandes pouvant souffrir de redondances excessives dans les données. Les niveaux sont représentés par des tables séparées, donnant au schéma la forme d'un flocon. En revanche, la conception de schéma en étoile n'étale pas ses dimensions sur les tables. Un schéma en étoile a la forme d'une étoile et les tables de dimension sont groupées autour d'une table de faits en son centre.

Avec les schémas en étoile ou en flocon modélisés de manière dimensionnelle, les requêtes d'aide à la décision suivent un modèle typique : la requête sélectionne plusieurs mesures d'intérêt de la table de faits, joint les lignes de fait avec une ou plusieurs dimensions le long des clés de substitution, place des prédicats de filtres sur les colonnes d'entreprise des tables de dimension, les groupe par une ou plusieurs colonnes d'entreprise et agrège les mesures récupérées de la table de faits pendant un temps défini. Ce qui suit illustre ce modèle. On appelle parfois également une requête de jointure en étoile :

select ProductAlternateKey,
CalendarYear,sum(SalesAmount)
from FactInternetSales Fact
     join DimTime 
on Fact.OrderDateKey = TimeKey
     join DimProduct 
on DimProduct.ProductKey =
   Fact.ProductKey
where CalendarYear between 2003 and 2004
      and ProductAlternateKey like 'BK%'
group by ProductAlternateKey,CalendarYear

Conception physique

De nombreuses requêtes SQL dans votre entrepôt de données relationnelles suivent la structure de la requête de jointure en étoile. Cependant, au fil du temps, les requêtes d'aide à la décision varient parce que les responsables tentent continuellement de mieux comprendre leurs données d'entreprise fondamentales. C'est pourquoi les charges de travail pour les entrepôts de données ont tendance à avoir un rapport de requêtes ad hoc élevé. Ceci rend d'autant plus difficile l'élaboration physique pour les requêtes d'aide à la décision et un schéma d'entrepôt de données modélisé de manière dimensionnelle.

Avec SQL Server, les concepteurs d'entrepôts de données commencent généralement par l'élaboration du modèle ou l'élaboration physique qu'ils améliorent progressivement à mesure que les charges de travail évoluent. Vous pouvez adopter et modifier ce modèle pour satisfaire votre propre environnement d'entrepôt de données. Le cas échéant, gardez à l'esprit les méthodes recommandées pour l'élaboration physique de base de données, telles que l'impact des performances causé par la maintenance d'index pour les mises à jour et les besoins de stockage des index.

Table de faits

L'élaboration du modèle a pour but d'anticiper la requête en forme d'étoile typique et de générer les index sur la table de faits. L'index cluster de la table de faits utilise plusieurs colonnes de clés de substitution de dimension (colonnes de clé étrangère) comme clés d'index. Les colonnes les plus fréquemment utilisées doivent se produire dans la liste de clés d'index. Vous souhaiterez peut-être prendre le temps de vérifier que ceci fournit en effet un chemin d'accès adéquat pour les requêtes les plus fréquemment exécutées dans votre charge de travail.

De plus, le modèle crée un index non cluster sur une colonne unique pour la colonne de substitution de dimension (clé étrangère) dans la table de faits. Ceci fournit un chemin d'accès très efficace pour les requêtes qui sont très sélectives dans l'une de leurs dimensions.

Le but de l'index cluster est de fournir des performances optimales pour la majorité des requêtes dans votre charge de travail. La série d'index non cluster cible les requêtes qui récupèrent les mesures de tables de faits pour un client ou un produit spécifique. Ces index non cluster garantissent, par exemple, que vous n'avez pas besoin de numériser la table de faits pour récupérer les données de ventes pour un seul client.

Tables de dimension

Lorsque vous appliquez l'élaboration du modèle aux tables de dimension, vous devez créer des index pour chaque table de dimension. Ceux-ci incluent un index de contrainte de clé primaire non cluster sur la colonne de clé de substitution de la dimension et un index cluster sur les colonnes de la clé d'entreprise de l'entité de dimension. Pour les grandes tables de dimension, vous devriez également prendre en considération l'ajout d'index non cluster sur les colonnes qui sont fréquemment utilisées dans des prédicats hautement sélectifs.

L'index cluster facilite efficacement l'extraction, transformation et chargement (ETL) dans la fenêtre de maintenance de l'entrepôt de données, qui est souvent un processus critique. Avec des dimensions qui changent lentement, par exemple, les lignes existantes sont mises à jour alors que les lignes pas encore présentes dans la dimension sont ajoutées à la table de dimension. Pour aboutir, ce modèle d'accès nécessite une recherche bien exécutée et la mise à jour de la table de dimension au moment de l'ETL.

L'élaboration du modèle que nous vous avons présenté est un bon point de départ pour les élaborations physiques dans les entrepôts de données relationnelles créés avec SQL Server. Basé sur cette installation typique d'entrepôt de données relationnelles, nous pouvons explorer les nouvelles fonctionnalités clés dans SQL Server 2008.

Optimisation de requêtes de jointure en étoile

Le traitement de la table de faits est généralement ce qui coûte le plus cher lors de l'exécution d'une requête de jointure en étoile dans un entrepôt de données relationnelles modélisé de manière dimensionnelle. Vous le constaterez aisément étant donné que même les requêtes hautement sélectives récupèrent beaucoup plus de lignes à partir de la table de faits que de toute dimension. Par conséquent, l'utilisation du chemin d'accès optimal dans la table de faits est essentielle pour des performances de requêtes idéales.

Avec SQL Server, l'optimiseur de requête choisit automatiquement le chemin d'accès avec le coût estimé le plus faible à partir d'alternatives. Dans le contexte d'entreposage de données, l'objectif principal consiste à s'assurer que l'optimiseur de requête considère les alternatives de chemins d'accès probantes pour le plan d'exécution de la requête de jointure en étoile. SQL Server inclut diverses fonctionnalités dans son optimiseur de requête pour fournir automatiquement des plans d'exécution de requête de jointure en étoile performants.

Envisagez les requêtes de jointure en étoile comme étant divisées en trois classes différentes, comme décrit à la figure 2. Ces classes étendues aident également le moteur SQL Server à identifier les sélections de plan appropriées à ces requêtes. Le concept principal sur lequel repose SQL Server est la sélectivité de ces requêtes par rapport à la table de faits. Moins une requête utilise de lignes de la table de faits et plus elle sera considérée comme sélective. Le pourcentage de lignes récupérées de la table de faits est utilisé pour fournir l'intuition de ces classes de requêtes. Ces pourcentages représentent des valeurs des déploiements de clients typiques mais elles ne sont pas des limites strictes utilisées pour générer des définitions de chemin d'accès.

Figure 2** Plages de sélectivité pour les requêtes de jointure en étoile **(Cliquer sur l'image pour l'agrandir)

La première classe couvre des requêtes extrêmement sélectives, traitant jusqu'à 10 % des lignes dans la table de faits. La deuxième classe, avec une sélectivité moyenne, est composée de requêtes traitant de 10 à 75 % des lignes de table de faits. Les requêtes de la troisième classe, avec une sélectivité faible, nécessitent le traitement de plus de 75 % de lignes enregistrées dans la table de faits. Les zones de la figure soulignent également les sélections fondamentales du plan d'exécution de la requête au sein de chaque classe de sélectivité.

Sélection du plan en fonction de la sélectivité

Étant donné que les requêtes en étoile à sélectivité élevée ne récupèrent pas plus de 10 % des lignes de table de faits, ces requêtes offrent un accès aléatoire à la table de faits. Par conséquent, les plans de requête pour cette classe se reposent largement sur les jointures de boucle imbriquées en association avec des recherches d'index non cluster et des recherches de signets dans la table de faits. Étant donné que celles-ci effectuent des E/S aléatoires dans la table de faits, elles sont beaucoup moins performantes par rapport aux E/S séquentielles lorsque nous devons récupérer des parties plus importantes de la table de faits. Ceci entraîne des plans de requête différents à mesure que le nombre de lignes de la table de faits augmente au-delà d'un certain nombre.

Étant donné que les requêtes en étoile à sélectivité moyenne traitent une fraction significative des lignes dans la table de faits, les jointures de hachage avec les analyses de tables de faits ou les analyses de plages de tables de faits sont généralement le choix préféré du chemin d'accès dans la table de faits. SQL Server utilisent des filtres bitmap pour améliorer les performances de ces jointures de hachage.

La figure 3 illustre comment SQL Server utilise ces filtres bitmap pour améliorer des performances de jointures lors de l'exécution de requête de jointure en étoile. La figure affiche un plan pour une requête contre deux tables de dimension, Product et Time, associant la table de faits à leurs clés de substitution. La requête utilise des prédicats de filtre, tels que les clauses WHERE, contre les deux tables de dimension afin qu'une seule ligne se qualifie pour chaque dimension. Ceci est indiqué par les petites tables rouges en regard de leurs deux opérateurs de jointure.

Figure 3** Plan de requête de jointure en étoile avec traitement de réduction de jointure **(Cliquer sur l'image pour l'agrandir)

L'implémentation de jointure pour chaque jointure est une jointure de hachage, qui autorise SQL Server à utiliser les informations relatives à la qualification des lignes à partir des tables de dimension dans ce que nous appelons les informations de réduction de jointure pour les deux tables de dimension. Les zones vertes de la figure représentent les structures de données d'informations de réduction de jointure. Une fois renseigné à partir des tables de dimension sous-jacentes, SQL Server déplace ces structures de données automatiquement lors de l'exécution de requête vers l'opérateur qui traite la table de faits, telle qu'une analyse de table. Cet opérateur utilise les informations des lignes de tables de dimension pour éliminer les lignes de table de faits qui ne se qualifieront pas pour les conditions de jointure contre les dimensions.

SQL Server supprime ces lignes de table de faits très tôt lors du traitement de requête, après avoir récupéré la ligne de la table de faits. Ceci permet des économies de processeurs et d'E/S de disque étant donné que les lignes supprimées ne doivent pas être traitées au sein d'autres opérateurs du plan de requête. SQL Server utilise une représentation bitmap pour implémenter efficacement les structures de données d'informations de réduction de jointure au moment de l'exécution de requête.

Pipeline d'optimisation de la jointure en étoile

Le processus d'optimisation utilise des heuristiques standard pour l'optimisation de requête de jointure afin de générer une série initiale d'alternatives de plans d'exécution de requête. Les extensions à objectif spécial sont alors invoquées pour produire des alternatives de plans de requête supplémentaires.

Dans le cas d'entreposage de données, l'extension détecte les schémas en étoile, les schémas en flocon et les modèles de requête en étoile puis analyse la sélectivité de la requête par rapport à la table de faits. Si le schéma et la forme de la requête correspondent au modèle, SQL Server ajoute automatiquement d'autres plans de requête à l'espace du plan, qui est alors couvert par l'optimisation à faible coût pour choisir le plan de requête la plus prometteur pour l'exécution.

Au moment de l'exécution de requête, SQL Server surveille également la sélectivité réelle de la réduction de jointure au moment de l'exécution. Si la sélectivité change, SQL Server réorganise les structures de données d'informations de réduction de jointure dynamiquement pour que celle qui est la plus sélective s'applique en premier.

Heuristiques de jointure en étoile

De nombreuses élaborations physiques pour les entrepôts de données suivent le schéma en étoile mais ne spécifient pas complètement les relations entre les tables de faits et de dimension, comme mentionné précédemment pour les contraintes de clés étrangères, par exemple. Sans contrainte de clés étrangères spécifiées explicitement, SQL Server doit dépendre des heuristiques pour détecter les modèles de requête de schéma en étoile. Les heuristiques suivantes sont appliquées pour détecter les modèles de requête de jointure en étoile :

1. La plus grande des tables participant à la jointure n-aire correspond à la table de faits. Il existe des restrictions supplémentaires relatives à la taille minimale de la table de faits. Par exemple, même si la table la plus grande ne dépasse pas la taille spécifiée, la jointure n-aire n'est pas considérée comme une jointure en étoile.
2. Toutes les conditions de jointure des jointures binaires dans une requête de jointure en étoile doivent être des prédicats d'égalité de colonnes uniques. Les jointures doivent être des jointures internes. Bien que ceci puisse sembler restrictif, la plupart des jointures entre la table de faits et les tables de dimension sur la clé de substitution dans les schémas en étoile typiques sont couvertes. Si une jointure dispose d'une condition de jointure plus complexe qui ne s'ajuste pas au modèle décrit ci-dessus, la jointure est exclue de la jointure en étoile. Une jointure à cinq branches, par exemple, peut mener à une jointure en étoile trilatérale (avec deux jointures ajoutées ultérieurement), si deux des jointures disposent de prédicats de jointure plus complexes.

Notez qu'il s'agit de règles heuristiques. Il existe peu de cas où les heuristiques choisissent une table de dimension comme table de faits. Ceci a une influence sur le choix du plan mais n'a pas vraiment d'impact sur l'exactitude du plan sélectionné. Les jointures binaires impliquées dans une jointure en étoile sont alors triées par sélectivité décroissante. La sélectivité de jointure dans ce contexte est définie comme un ratio de cardinalité d'entrée de la table de faits et de cardinalité de résultats de la jointure. La sélectivité de jointure indique à quel point une dimension spécifique réduit la cardinalité d'une table de faits. En règle générale, nous envisageons des jointures avec la sélectivité la plus élevée en premier.

Le processeur de requête de SQL Server applique automatiquement l'optimisation aux requêtes suivant le modèle de jointure en étoile et les conditions mentionnées ci-dessus lorsque les plans de requête résultants ont des coûts de requête estimés attrayants. Par conséquent, vous ne devez pas modifier votre application pour qu'elle profite de cette amélioration de performances significative. Cependant, il est important de noter que certaines des optimisations de jointure en étoile, telles que la réduction de jointure, ne sont disponibles que dans SQL Server Enterprise Edition.

Résultats de performance de jointure en étoile

Dans le cadre de l'effort de développement pour l'optimisation de jointure en étoile dans SQL Server 2008, nous avons mené plusieurs études de performances basées sur les charges de travail d'évaluation et de clients réels. Il est utile de regarder les résultats de trois de ces charges de travail.

Entrepôt de données de l'équipe de ventes de Microsoft Cette charge de travail suit les performances d'un entrepôt de données utilisé en interne pour aider à la décision dans l'organisation des ventes chez Microsoft. Nous avons pris un instantané de la base de données avec une taille d'environ 750 Go (avec les index). Les requêtes dans cette charge de travail sont un défi pour le traitement de requêtes, étant donné que beaucoup d'entre elles contiennent plus de 10 jointures.

Client détaillant Cette série d'expériences se base sur un client d'entreposage de données pour les commerces de détail (avec un magasin traditionnel et une présence en ligne). Le client est caractérisé par un schéma en flocon modélisé de manière dimensionnelle et des requêtes de jointure en étoile canonique. Nous avons utilisé environ 100 Go de données brutes pour renseigner un instantané de l'entrepôt pour nos expériences.

Charge de travail pour l'aide à la décision Cette série d'expériences examine les performances d'une charge de travail d'aide à la décision sur une base de données modélisée de manière dimensionnelle de 100 Go. La figure 4 illustre les résultats de ces trois charges de travail. La figure trace des moyennes géométriques normalisées du temps de réponse aux requêtes par rapport à toutes les requêtes dans la charge de travail. Cette métrique est un bon indicateur des performances de requêtes attendues lors de l'exécution d'une requête arbitraire à partir de la charge de travail. Les barres dans la figure comparent les performances de référence (1.0) lorsque l'optimisation de jointure en étoile n'est pas utilisée par rapport aux performances de jointure optimisée en étoile. Toutes ces exécutions ont été effectuées avec SQL Server 2008.

Figure 4** Améliorations de performances grâce à l'optimisation de jointure en étoile **(Cliquer sur l'image pour l'agrandir)

Comme vous le constatez dans la figure, toutes les charges de travail s'améliorent de manière significative, de 12 à 30 %. Tandis que votre kilométrage individuel variera, nous pensons que les charges de travail d'aide à la décision par rapport à SQL Server Engine seront améliorées d'environ 15 à 20 % en fonction de l'extension des optimisations de jointures en étoile spécifiques (nouveautés de SQL Server 2008).

Parallélisme de table partitionnée

Pour accélérer le traitement de requête dans les grands entrepôts de données, les administrateurs de base de données partitionnent souvent les tables de faits importantes par date. Ainsi, les données sont placées sur différents groupes de fichiers, réduisant la quantité de données à rechercher lors du traitement des lignes au sein d'une plage de données spécifique et utilisant les performances simultanées du système de disque sous-jacent lorsque les groupes de fichiers sont déployés sur plusieurs disques physiques.

SQL Server 2005 a introduit la capacité à partitionner une grande relation en parties logiques plus petites pour améliorer l'administration et la gestion des grandes tables. Il a également permis d'améliorer de manière significative le traitement de requêtes, surtout lorsqu'il s'agit d'applications d'aide à la décision importantes.

Malheureusement, certains clients utilisant SQL Server 2005 ont remarqué des problèmes de performances associés aux requêtes sur ces tables partitionnées, en particulier, lors de l'exécution sur des ordinateurs multiprocesseurs à mémoire partagée parallèle. Lors du traitement de requêtes parallèles sur les tables partitionnées dans SQL Server 2005, des instances ne peuvent être possibles que lorsqu'un seul sous-ensemble de threads disponibles est attribué à l'exécution de la requête.

Pensez à un ordinateur 64 cœurs dans lequel les requêtes peuvent utiliser jusqu'à 64 threads en parallèle et une requête concerne deux partitions. Avec SQL Server 2005, il ne reçoit que 2 des 64 threads et par conséquent, peut uniquement utiliser 2/64 (3,1 %) de la puissance du processeur de l'ordinateur. Il a été signalé que pour certaines requêtes, les performances dans le cas partitionné pourraient être 10 fois plus compliquées que lorsque la même requête est exécutée sur le même ordinateur sur une version non partitionnée de la même table de faits.

Il est important de noter que SQL Server 2005 a été optimisé pour les requêtes concernant une partition unique. Dans ce cas, le processeur de requête attribuera tous les threads disponibles pour exécuter l'analyse. Cette optimisation spéciale a entraîné une amélioration significative des performances pour les requêtes de partition unique s'exécutant sur des ordinateurs à plusieurs cœurs. Les clients peuvent donc s'attendre à ce type de comportement pour les requêtes concernant plusieurs partitions.

La nouvelle fonctionnalité de parallélisme de table partitionnée (PTP) dans SQL Server 2008 améliore les performances de requête en utilisant au mieux la puissance de traitement du matériel existant sans tenir compte du nombre de partitions d'une requête ou de la taille relative des partitions individuelles. Dans un scénario d'entrepôt de données typique avec une table de faits partitionnée, les utilisateurs verront une amélioration significative des requêtes s'exécutant sur des plans parallèles, surtout si le nombre de cœurs de processeurs disponibles est plus important que le nombre de partitions concernées par la requête. Cette nouvelle fonctionnalité est prête à l'emploi et ne nécessite aucun ajustement ni configuration.

Il s'agit d'une table de faits représentant des données de ventes organisées par dates de vente sur les quatre partitions. Le diagramme de la figure 5 vous aidera à visualiser cet exemple. Notez qu'au lieu d'un seul index en cluster pour toute la plage de dates, comme c'est le cas pour le cas non partitionné, il y a généralement un index en cluster sur la colonne de date pour chaque partition de la table de faits. Supposons maintenant que la requête R récapitule les ventes des sept derniers jours. Étant donné que les nouvelles données de ventes entrent dans la table de faits en continu via la dernière partition (appelée P4), la requête concernera probablement plusieurs partitions en fonction du moment de son exécution. Ceci est illustré dans la première ligne du diagramme par la manière dont la requête R1 ne concerne qu'une seule partition tandis que la requête R2 concerne deux partitions étant donné que les données pertinentes au moment de l'exécution s'étalent sur les partitions P3 et P4.

Figure 5** Nouvelle fonctionnalité PTP à l'œuvre **(Cliquer sur l'image pour l'agrandir)

Supposons désormais qu'il existe huit threads disponibles. L'exécution de R1 et R2 sur SQL Server 2005 peut entraîner un comportement imprévu. SQL Server 2005 a été optimisé : si l'optimiseur sait qu'au moment de la compilation, une seule partition sera concernée par la requête, cette partition sera traitée comme une table non partitionnée et un plan sera généré et pourra accéder à la table avec tous les threads disponibles.

Le résultat : la requête R1 impliquant une partition unique (P3) sera dans un plan traité par huit threads (non affichés). Dans le cas de R2, qui concerne deux partitions, l'exécuteur attribue un seul thread à chaque partition même si le matériel sous-jacent a des threads supplémentaires disponibles. Par conséquent, R2 n'utilisera qu'une très petite fraction de la puissance du processeur disponible et s'exécutera certainement beaucoup plus lentement que R1.

L'exécution de R1 et R2 sur SQL Server 2008 entraîne une meilleure utilisation du matériel disponible, de meilleures performances et un comportement plus prévisible. Dans le cas de R1, l'exécuteur attribue une nouvelle fois les huit threads disponibles pour traiter les données dans P2 (non affiché). Pendant ce temps, R2 aura pour résultat un plan parallèle dans lequel l'exécuteur attribue tous les threads disponibles à P3 et P4 à tour de rôle, produisant l'effet illustré à la ligne inférieure du diagramme où chacune des deux partitions reçoivent quatre threads. Le processeur reste entièrement utilisé et les performances de R1 et R2 sont comparables.

Cette allocation de threads à tour de rôle active les requêtes pour une exécution améliorée avec des cœurs de processeurs supplémentaires par rapport au nombre de partitions accessibles par la requête. Malheureusement, il existe des cas dans lesquels l'allocation de threads à des partitions n'est pas aussi simple que dans cet exemple.

Les gains en termes de performances de SQL Server 2005 à SQL Server 2008 pour le scénario de table partitionnée sur un ordinateur avec des processeurs à plusieurs cœurs sont illustrés à la figure 6. Ce graphique anecdotique souligne les performances d'analyse pour les tables partitionnées. Pour ce test particulier, qui a été effectué sur un système avec 64 cœurs et 256 Go de mémoire RAM, nous avons partitionné une seule table de 121 Go en 11 partitions de 11 Go chacune. Pour la série de tests décrite dans cette figure, nous avons utilisé une organisation de fichiers de tas avec des démarrages de tampons à froid et à chaud. Toutes les requêtes exécutent des analyses simples par rapport aux données.

Figure 6** Performances en matière d'analyse pour SQL Server avec la nouvelle fonctionnalité PTP activée **(Cliquer sur l'image pour l'agrandir)

L'axe Y affiche le temps de réponse (en sec) et l'axe X indique le degré de parallélisme, qui est analogue au nombre de threads attribués à la requête. Comme vous pouvez le voir, dans les cas de démarrages à froid et à chaud, les temps de réponse continuent à décroître tandis que le degré de parallélisme atteint 22. À ce stade, le système E/S devient saturé pour le cas du démarrage à froid. Ceci est dû au fait que la requête utilisée dans cet exemple est liée aux E/S. Pour plus de charges de travail liées au processeur, cette restriction peut ne pas exister ou ne se produire qu'à un degré de parallélisme plus élevé.

Cependant, la courbe représentant le cas du démarrage à froid continue à afficher une diminution en matière de temps de réponse à mesure que le degré de parallélisme augmente. Sur SQL Server 2005, les deux courbes commençaient à se stabiliser lorsque le degré de parallélisme atteignait 11 étant donné que le nombre de threads par partition se limitait à 1 lors du traitement de partitions multiples.

Dans la pratique, il est important de noter que le gain en matière de temps de réponse pour les nombres croissants de degré de parallélisme n'est jamais linéaire. Le comportement attendu ressemble plutôt à une fonction étape, qui reflète la réalité, c'est-à-dire que la requête attend sur la sous-partie la plus lente. Par exemple, l'ajout d'un thread supplémentaire à cette analyse n'améliore pas le temps de finition d'une requête tant que toutes les analyses restantes n'ont pas reçu les threads supplémentaires leur permettant également de se terminer plus rapidement.

Nous avons effectué plusieurs expériences pour tester le nouveau comportement PTP pour plusieurs configurations de fichiers et matériels. Lors de ces tests, nous avons observé un comportement similaire sur le plan de mise à l'échelle des débits à mesure que le degré de parallélisme augmente au delà d'un thread ou d'une partition.

Enfin, la nouvelle fonctionnalité PTP dans SQL Server 2008 améliore également la lisibilité de plans de requête et permet un meilleur aperçu dans l'exécution de certaines charges de travail. Par exemple, dans le cadre de la fonctionnalité PTP, la manière dont les plans en série et parallèles sont représentés dans le showplan XML a été améliorée et les informations de partitionnement fournies dans les plans d'exécution lors de l'exécution et de la compilation ont également été améliorées.

Compression de données

À mesure que l'analyse décisionnelle devient monnaie courante, les entreprises transmettent de plus en plus de données dans leurs entrepôts de données à des fins d'analyse. Il en résulte une croissance exponentielle de la taille des données à gérer. En 1995, la première étude de Winter Corporation sur la taille des bases de données a indiqué que le système le plus important au monde contenait un téraoctet de données. Dix années plus tard, la base de données la plus importante contenait environ 100 fois plus. Fait encore plus étonnant : la taille des entrepôts de données triple tous les deux ans. Ceci crée de nouveaux défis pour gérer de telles quantités de données et offrir des niveaux acceptables de performances pour les requêtes d'entrepôts de données. Ces requêtes sont généralement complexes, impliquant plusieurs jointures et agrégats et accèdent à de grandes quantités de données. Il n'est pas rare pour de nombreuses requêtes dans la charge de travail d'être liées aux E/S.

La compression de données natives a pour but de résoudre ce problème. SQL Server 2005 SP2 a introduit un nouveau format de stockage de longueur variable, le format de stockage VarDecimal pour les données numériques et décimales. Ce nouveau format de stockage peut réduire la taille de vos bases de données de manière significative. À leur tour, le gain de place peut aider à améliorer les performances de requêtes liées aux E/S de deux manières. Tout d'abord, il y a moins de pages à lire. Ensuite, étant donné que les données sont stockées compressées dans un pool de tampons, la durée de vie de la page est accrue (en d'autres termes, la probabilité selon laquelle la page requise sera trouvée dans le tampon est accrue). Bien sûr, le gain de place obtenu grâce à la compression de données a un coût UC en raison du processus de compression et de décompression des données.

SQL Server 2008 génère le format de stockage VarDecimal, offrant deux types de compression : compression de ligne et compression de page La compression de ligne étend le format de stockage VarDecimal en stockant tous les types de données de longueur fixe à un format de stockage de longueur variable.

Certains exemples de types de données de longueur fixe sont des entiers, des caractères et des données flottantes. Bien que SQL Server enregistre ces types de données au format de durée variable, la sémantique des types de données restera inchangée (un type de données est toujours le type de données de longueur fixe pour l'application). Par conséquent, vous pouvez obtenir les avantages de la compression de données sans devoir modifier quoi que ce soit dans vos applications.

La compression de page réduit la redondance de données dans les colonnes dans une ou plusieurs lignes sur une page donnée. Elle utilise une implémentation propriétaire de l'algorithme LZ78 (Lempel-Ziv), stockant les données redondantes une seule et unique fois sur la page, puis s'y référant à partir de plusieurs colonnes. Notez que lorsque vous utilisez la compression de page, la compression de ligne est également comprise.

Les compressions de ligne et de page peuvent être activées sur une table ou un index ou sur une ou plusieurs partitions pour les tables et les index partitionnés. Vous avez ainsi une flexibilité complète sur le choix des tables, des index et des partitions pour la compression, vous offrant un équilibre adéquat entre le gain de place et l'impact sur le processeur. Vous verrez à la figure 7 une table de ventes partitionnée de différentes manières avec les index alignés.

Figure 7** Tables partitionnées avec différents paramètres de compression **(Cliquer sur l'image pour l'agrandir)

Chaque partition représente un trimestre, octobre-décembre étant le dernier trimestre. Supposons que vous accédez aux deux premières partitions fréquemment, à la troisième partition modérément et à la dernière de manière la plus active. Dans ce cas, une configuration possible consiste à activer la compression de page sur les deux premières partitions pour obtenir un gain de place maximal avec un impact minimal sur les performances de la charge de travail, la compression de ligne sur la troisième partition et aucune compression sur la dernière.

Vous pouvez activer la compression, en ligne ou hors ligne, à l'aide des instructions du langage de définition de données (DDL) Alter Table ou Alter Index. SQL Server fournit également une procédure stockée pour estimer le gain de place. Le gain de place que vous atteignez dépendra de la distribution des données et du schéma de l'objet compressé.

En fonction des résultats des tests de plusieurs bases de données de clients, il est clair que la plupart des clients peuvent réduire la taille de leur base de données de 50 à 65 % et améliorer les performances de leurs requêtes liées aux E/S de manière significative. Cependant, l'estimation de l'impact sur les performances de requêtes liées à un processeur est un peu plus difficile et dépend de la complexité de la requête. Dans SQL Server, le coût de décompression ne se produit qu'au moment de l'accès aux index ou tables. Si le coût UC relatif des opérateurs d'analyse est inférieur au coût UC général de la requête (comme c'est généralement le cas dans le scénario d'entrepôt de données), vous devriez avoir un impact inférieur à 20-30 % sur l'utilisation du processeur.

Affichages indexés alignés sur les partitions

Dans SQL Server 2008, les affichages indexés alignés sur les partitions vous permettent de créer et gérer des agrégats récapitulatifs dans votre entrepôt de données relationnelles plus efficacement et de les utiliser dans les scénarios où vous ne pouviez pas les utiliser efficacement précédemment. Les performances de requête s'en trouvent améliorées. Dans un scénario typique, vous disposez d'une table de faits partitionnée par date. Les affichages indexés (ou les agrégats récapitulatifs) sont définis sur cette table pour faciliter la vitesse des requêtes. Lorsque vous basculez dans une nouvelle partition de tables, les partitions correspondantes aux affichages indexés alignés sur les partitions définis sur la table partitionnée basculent également, et ce, de manière automatique.

Il s'agit d'une amélioration significative par rapport à SQL Server 2005, avec lequel vous deviez déposer tout affichage indexé défini sur une table partitionnée avant d'utiliser le fonctionnement ALTER TABLE SWITCH pour basculer dans une partition ou en sortir. La fonction d'affichages indexés alignés sur les partitions dans SQL Server 2008 vous offre les avantages d'affichages indexés sur les grandes tables partitionnées tout en évitant le coût de reconstruire des agrégats sur la totalité d'une table partitionnée. Ces avantages incluent la maintenance automatique des agrégats et la correspondance d'affichages indexés.

Escalade de verrous au niveau de la partition

SQL Server prend en charge le partitionnement par plage, qui vous permet de partitionner les données pour la gérabilité ou de grouper les données selon leur modèle d'utilisation. Par exemple, les données de ventes peuvent être partitionnées avec une limite mensuelle ou trimestrielle. Vous pouvez mapper une partition à son propre groupe de fichiers, puis mapper le groupe de fichiers à un groupe de fichiers. Ceci offre deux avantages principaux. Tout d'abord, vous pouvez sauvegarder et restaurer une partition en tant qu'unité indépendante. Ensuite, vous pouvez mapper un groupe de fichiers pour ralentir ou accélérer le sous-système E/S en fonction du modèle d'utilisation ou de la charge de requête.

Ce qui est intéressant ici, c'est le modèle d'accès aux données. Les requêtes et le fonctionnement DML ne pourront peut-être accéder à ou manipuler qu'un sous-ensemble de partitions. Par conséquent, si par exemple, vous analysez les données de ventes pour 2004, vous ne devrez accéder qu'aux partitions pertinentes et, donc ne devriez pas être concerné, sauf si les ressources système par les requêtes accèdent simultanément aux données d'autres partitions. Dans SQL Server 2005, l'accès simultané aux données d'autres partitions peut entraîner le verrouillage d'une table pouvant avoir un impact sur l'accès aux autres partitions.

Pour minimiser ce problème, SQL Server 2008 introduit une option au niveau de la table pour contrôler l'escalade de verrous au niveau de la partition ou de la table. Par défaut, l'escalade de verrous est activée au niveau de la table, comme c'est le cas dans SQL Server 2005. Cependant, vous pouvez remplacer la stratégie d'escalade de verrous pour la table. Vous pouvez, par exemple, définir l'escalade de verrous ainsi :

Alter table <mytable> set (LOCK_ESCALATION = AUTO)

Cette commande demande à SQL Server de choisir la granularité d'escalade de verrous idéale pour le schéma de table. Si la table n'est pas partitionnée, l'escalade de verrous est au niveau TABLE. Si la table est partitionnée, alors la granularité d'escalade de verrous est au niveau de la partition. Cette option est également utilisée comme un indice par SQL Server pour désapprouver la granularité de verrouillage au niveau de la table.

Conclusion

Ceci n'est qu'une brève présentation des fonctionnalités améliorées que vous trouverez dans SQL Server 2008 et qui vous aideront à atteindre des performances optimales pour les requêtes d'aide à la décision concernant les entrepôts de données relationnelles. Gardez cependant à l'esprit que bien que le temps de réponse compétitif pour vos requêtes d'aide à la décision est essentiel, il y a d'autres exigences clés non indiquées dans cet article.

Certaines fonctionnalités supplémentaires associées à l'entreposage de données relationnelles comprennent ce qui suit :

• la prise en charge de la syntaxe MERGE dans T-SQL pour mettre à jour, supprimer ou insérer des données (dimension) avec une instruction ou une boucle dans la base de données.
• des performances de journalisation optimisées du moteur SQL Server pour permettre une ETL plus efficace.
• des ensembles de groupes pour faciliter l'écriture de requêtes d'aide à la décision agrégées dans T-SQL.
• la compression de sauvegarde pour réduire les exigences E/S pour les sauvegardes complètes et incrémentielles.
• la gouvernance de ressources pour contrôler l'allocation de ressources système aux différentes charges de travail.

Nous vous encourageons à consulter les informations plus détaillées relatives à toutes ces fonctionnalités passionnantes sur la page Web de SQL Server, disponible à l'adresse microsoft.com/sql.

Nous souhaitons remercier Boris Baryshnikov, Prem Mehra, Peter Zabback et Shin Zhang pour leur expertise technique.

Sunil Agarwal est responsable de programme senior au sein du groupe SQL Server Storage Engine chez Microsoft. Il est responsable de l'accès simultané, des index, de tempdb, des applications métier, de la capacité de prise en charge et de l'importation/exportation en bloc.

Torsten Grabs est responsable de programme senior principal de Core Storage Engine au sein de l'équipe Microsoft SQL Server. Il possède un doctorat en systèmes de base de données et a plus de 10 ans d'expérience avec SQL Server.

Dr. Joachim Hammer est chef de projet au sein du groupe de traitement des requêtes chez Microsoft. Sa spécialité : l'optimisation de requêtes pour les applications d'entrepôt de données à grande échelle mais aussi les requêtes distribuées, l'extraction, transformation et chargement (ETL) et l'intégration d'informations.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.