Partitioned Tables and Indexes in SQL Server 2005

Tables et index partitionnés dans SQL Server 2005
Kimberly L. Tripp
Fondatrice, SQLskills.com
Janvier 2005
Concerne :
SQL Server 2005
Résumé : Dans SQL Server 2005, les fonctions de partitionnement des tables et index offrent flexibilité et
performances et simplifient la création et la maintenance de telles tables. Découvrez les nouvelles fonctions de
partitionnement des tables, et découvrez pourquoi, quand et comment concevoir, implémenter et gérer des
tables partitionnées à l'aide de SQL Server 2005. (41 pages imprimées)
À propos de cet article Les fonctions et plans décrits dans le présent document reposent sur les dernières
versions préliminaires de SQL Server 2005. Ils ne constituent pas les spécifications de ce produit, et sont sujets
à modifications. Il n'existe aucune garantie, implicite ou autre, que ces fonctions soient intégrées à la version
finale du produit.
Pour certaines fonctions, ce document part du principe que le lecteur connaît bien les fonctions et services de
SQL Server 2000. Pour plus d'informations, visitez le site Web de SQL Server ou reportez-vous au Kit de
ressources SQL Server 2000.
Il ne s'agit pas d'une spécification de produit.
Téléchargez l'exemple de code associé, SQL2005PartitioningScripts.exe.
Sommaire
Pourquoi partitionner ?
Historique du partitionnement
Partitionnement d'objets dans les versions antérieures à SQL Server 7.0
Vues partitionnées dans SQL Server 7.0
Vues partitionnées dans SQL Server 2000
Vues partitionnées dans SQL Server 2005
Définitions et terminologie
Partitions par spécification de plages de valeurs
Définition de la clé de partitionnement
Partitionnement d'index
Conditions spéciales pour les partitions : fractionnement, fusion et aiguillage
Procédure de création de tables partitionnées
Déterminer si des objets doivent être partitionnés

Déterminer la clé de partitionnement et le nombre de partitions
Déterminer si plusieurs groupes de fichiers doivent être utilisés
Créer des groupes de fichiers
Créer la fonction de partition d'une partition par spécification de plages de valeurs
Créer le schéma de partition
Créer la table partitionnée
Créer les index : partitionnés ou non ?
Synthèse : études de cas
Partitionnement par spécification de plages de valeurs : données de vente
Jointure de tables partitionnées
Scénario de fenêtre dynamique
Partitionnement par spécification de listes : données régionales
Résumé
Scripts utilisés dans cet article
Pourquoi partitionner ?
Qu'est-ce que les partitions et pourquoi les utiliser ? La réponse est simple : pour améliorer la capacité à
monter en charge et le mode de gestion des tables volumineuses et des tables à modèles d'accès variables.
Généralement, vous créez des tables pour stocker des informations concernant une entité, comme les clients ou
les ventes, et chaque table possède des attributs décrivant cette entité uniquement. Même s'il est plus facile de
concevoir et de comprendre une table unique pour chaque entité, ces tables ne sont pas forcément optimisées
pour les performances, la montée en charge et le mode de gestion, en particulier lorsque la volumétrie de la
table augmente.
Qu'est-ce qui caractérise une table volumineuse ? Alors que la taille d'une base de données très volumineuse
(VLDB) est exprimée en téraoctets, l'expression « table volumineuse » ne fait pas forcément référence à la
taille des tables individuelles dans la base de données. Une table peut être considérée comme volumineuse si
les activités des utilisateurs ou les opérations de maintenance ont un impact restrictif sur la disponibilité. Par
exemple, la table des ventes est considérée comme volumineuse si les performances sont fortement
détériorées ou si la table est inaccessible pendant deux heures tous les jours, toutes les semaines ou même
tous les mois en raison d'opérations de maintenance. Dans certains cas, un arrêt périodique est acceptable,
mais il peut souvent être évité ou réduit grâce à la mise en œuvre d'une meilleure conception et d'un
partitionnement plus approprié. Même si l'expression « base de données très volumineuse (VLDB) » ne
s'applique qu'à une base de données, pour ce qui concerne le partitionnement, il est plus important d'examiner
la taille des tables.
Outre la taille, une table à modèles d'accès variables peut être source d'inquiétude en matière de performances
et de disponibilité lorsque différents ensembles de lignes dans la table sont associés à différents modèles
d'utilisation. Même si les modèles d'utilisation ne sont pas toujours variables (et cela ne constitue pas une

exigence pour le partitionnement), lorsque les structures d'utilisation varient, le partitionnement peut permettre
de bénéficier de gains supplémentaires en termes de gestion, de performances et de disponibilité. De même,
pour utiliser l'exemple d'une table de ventes, les données du mois courant peuvent être accessibles en lecture-
écriture, tandis que celles du mois précédent (souvent la partie la plus importante de la table) sont accessibles
en lecture seule. Dans ce cas, lorsque l'utilisation des données varie ou lorsque les charges de maintenance
sont considérables du fait de l'entrée et de la sortie des données dans la table, la capacité de la table à
répondre aux demandes des utilisateurs peut en pâtir. Cet état de fait limite la disponibilité et la montée en
charge du serveur.
De plus, lorsque des ensembles volumineux de données sont utilisés de différentes manières, des opérations de
maintenance fréquentes sont effectuées sur les données statiques. Ceci peut s'avérer coûteux engendrer des
problèmes de performances , des blocages des sauvegardes (problèmes d'espace, de temps et d'exploitation)
et avoir un impact négatif sur la montée en charge du serveur.
En quoi le partitionnement peut-il être utile ? Lorsque les tables et les index deviennent très volumineux, le
partitionnement peut permettre de partitionner les données en sections plus petites et plus faciles à gérer. Cet
article traite du partitionnement horizontal, dans lequel des groupes de lignes importants sont stockés en
plusieurs partitions distinctes. La définition de l'ensemble partitionné est personnalisée, définie et gérée en
fonction de vos besoins. Microsoft SQL Server 2005 vous permet de partitionner les tables en fonction de
modèles d'utilisation des données spécifiques à l'aide de plages de valeurs ou de listes définies. SQL
Server 2005 propose également de nombreuses options pour la gestion à long terme de tables et d'index
partitionnés grâce à l'ajout de fonctions conçues autour de la nouvelle structure de table et d'index.
De plus, s'il existe une table volumineuse sur un système comportant plusieurs processeurs, le partitionnement
de la table permet l'amélioration des performances grâce aux opérations parallèles. Les performances des
opérations de grande envergure sur des volumétries extrêmement volumineuses (par exemple, des millions de
lignes) bénéficient de la réalisation de ces opérations en parallèle sur des sous-ensembles plus petits. Les
agrégations disponibles dans les versions précédentes constituent un exemple de gains de performances au
niveau des partitions. Ainsi, au lieu d'agréger une table volumineuse unique, SQL Server peut fonctionner
indépendamment sur des partitions, puis agréger les agrégations. Dans SQL Server 2005, les requêtes qui
joignent des volumétries de données importantes peuvent bénéficier directement du partitionnement. SQL
Server 2000 prend en charge les opérations de jointure parallèles sur des sous-ensembles, mais implique
encore la création de sous-ensembles à la volée. Dans SQL Server 2005, les tables associées (comme les tables
Order et OrderDetails), qui sont partitionnées à l'aide de la même clé de partitionnement et de la même
fonction de partition sont dites alignées. Lorsque l'optimiseur détecte que deux tables partitionnées et alignées
sont jointes, SQL Server 2005 peut joindre les données qui se trouvent dans les mêmes partitions, puis
combiner les résultats. Cela permet à SQL Server 2005 d'utiliser avec plus d'efficacité des ordinateurs multi-
processeurs.
Historique du partitionnement

Le concept de partitionnement n'est pas nouveau dans SQL Server. En fait, chaque version du produit autorise
déjà des formes de partitionnement. Cependant, sans fonctions conçues spécifiquement pour permettre de
créer et de gérer un schéma de partition, le partitionnement a souvent été une opération lourde et sous-
utilisée. De plus, les utilisateurs et les développeurs ne comprenant pas le schéma (en raison d'une conception
plus complexe de la base de données), les avantages étaient réduits. Toutefois, en raison de gains de
performances considérables inhérents au concept, SQL Server 7.0 a commencé à améliorer la fonction en
permettant des formes de partitionnement par le biais des vues partitionnées. SQL Server 2005 offre désormais
les plus importantes avancées en matière de partitionnement d’ensemble de données volumineux par le biais
des tables partitionnées.
Partitionnement d'objets dans les versions antérieures à SQL Server 7.0
Dans SQL Server 6.5 et les versions antérieures, le partitionnement devait faire partie de la conception et être
intégré à l'intégralité du code d'accès aux données et aux pratiques de transmission de requêtes. En créant
plusieurs tables, puis en gérant l'accès aux tables appropriées grâce aux procédures stockées, aux vues ou aux
applications client, il était souvent possible d'améliorer les performances de certaines opérations, mais cela se
traduisait par une plus grande complexité à la conception. Les utilisateurs et les développeurs devaient
connaître (et référencer correctement) les tables appropriées. Chaque partition était créée et gérée
distinctement et des vues étaient utilisées pour simplifier l'accès. Cependant, cette solution n’était pas très
productive en termes de performances. Lorsqu'il existait une vue unifiée pour simplifier l'accès des utilisateurs
et des applications, le processeur de requêtes devait accéder à chaque table sous-jacente afin de déterminer les
données nécessaires à l'ensemble de résultats. Si seul un nombre réduit de sous-ensembles des tables sous-
jacentes était nécessaire, chaque utilisateur et développeur devait connaître le schéma des données afin de ne
référencer que les tables appropriées.
Vues partitionnées dans SQL Server 7.0
Les défis mis en évidence par la création manuelle de partitions dans les versions antérieures à SQL Server 7.0
portaient principalement sur les performances. Alors que les vues simplifiaient la conception d'applications,
l'accès des utilisateurs et la rédaction de requêtes, elles n'offraient pas de gains en matière de performances.
Avec la publication de SQL Server 7.0, les vues ont été combinées à des contraintes pour permettre à
l'optimiseur de requêtes de supprimer les tables inappropriées du plan de requêtes (c'est-à-dire, de supprimer
certaines partitions) et de réduire ainsi considérablement le coût global du plan lorsqu'une vue unifiée accédait
à plusieurs tables.
Observez la vue yearlySales de la figure 1. Au lieu d'avoir plusieurs ventes dans une table volumineuse unique,
vous pouvez définir douze tables individuelles (SalesJanuary2003, SalesFebruary2003, etc.), puis des vues
de chaque trimestre ainsi qu'une vue de l'année entière, YearlySales.

Figure 1 - Vues partitionnées dans SQL Server 7.0/2000.
Les utilisateurs qui accèdent à la vue YearlySales avec la requête ci-dessous sont dirigés uniquement vers la
table SalesJanuary2003.
SELECT ys.*
FROM dbo.YearlySales AS ys
WHERE ys.SalesDate = '20030113'
Tant que les contraintes sont approuvées et que les requêtes dans la vue utilisent une clause WHERE pour
restreindre les résultats en fonction de la clé de partition (la colonne dans laquelle la contrainte est définie),
SQL Server accède uniquement à la table de base de données nécessaire. Les contraintes approuvées sont des
contraintes dans lesquelles SQL Server peut garantir que toutes les données correspondent aux propriétés
définies par la contrainte. Lorsque la contrainte est créée, le comportement par défaut consiste à créer la
contrainte WITH CHECK. Ce paramètre entraîne l'utilisation d'un verrou de structure dans la table afin que les
données puissent être vérifiées en les comparant à la contrainte. Une fois que la vérification valide les données
existantes, la contrainte est ajoutée. Une fois que le verrou de structure est supprimé, les insertions, mises à
jour et suppressions qui suivent doivent respecter la contrainte en vigueur. En utilisant cette procédure pour
créer des contraintes approuvées, les développeurs peuvent réduire considérablement la complexité de leur
code d’accès à l'aide de vues sans avoir à accéder directement à la table qui les intéresse (ni même à connaître
celle-ci). Avec les contraintes approuvées, SQL Server améliore les performances en supprimant les tables
inutiles du plan d'exécution.
Remarque Une contrainte peut devenir « désapprouvée » de différentes manières. Par exemple, si une
insertion en bloc est effectuée sans indiquer l'argument CHECK_CONSTRAINTS ou si une contrainte est créée
avec l'élément NOCHECK. Si une contrainte est désapprouvée, le processeur de requêtes recommence à
analyser toutes les tables de base de données puisqu'il n'a pas de moyen de vérifier que les données
demandées se trouvent en fait dans la table correcte.