Consignes spéciales pour les index partitionnés
Bien que les index partitionnés puissent être mis en œuvre indépendamment de leurs tables de base, il est généralement préférable de créer une table partitionnée, puis d'y ajouter un index. Dans ce cas, SQL Server partitionne automatiquement l'index en utilisant le même schéma et les mêmes colonnes de partitionnement que la table. De ce fait, l'index est partitionné comme la table. On dit alors qu'il est aligné avec la table.
SQL Server n'aligne pas l'index avec la table si vous spécifiez un autre schéma de partitionnement ou un autre groupe de fichiers sur lequel placer l'index au moment de sa création.
L'alignement d'un index avec une table partitionnée est très important si vous prévoyez une extension avec des partitions supplémentaires ou s'il doit être impliqué dans de fréquentes commutations de partitionnement. Pour plus d'informations, consultez Création de partitions pour gérer les sous-ensembles de données. Lorsqu'une table et ses index sont alignés, SQL Server peut commuter rapidement et efficacement les partitions tout en préservant leur structure aussi bien dans la table que dans les index.
[!REMARQUE]
Un index n'a pas besoin de participer à la même fonction de partition nommée pour être aligné avec sa table de base. Toutefois, la fonction de partition de l'index et celle de la table de base doivent être identique de trois points de vue : 1) les arguments des fonctions de partition ont le même type de données, 2) elles définissent le même nombre de partitions et 3) elles définissent les mêmes valeurs limites pour les partitions.
L'onglet Options de paramétrage de l'Assistant Paramétrage du moteur de base de données fournit un paramètre Partitionnement aligné pour spécifier que les nouveaux index recommandés soient alignés avec leurs tables de base. Le paramètre Conserver le partitionnement aligné peut être utilisé pour obtenir le même résultat et il peut aussi s'utiliser pour supprimer des index existants non alignés. Pour plus d'informations, consultez Assistant Paramétrage du moteur de base de données (onglet Options de paramétrage). En général, l'Assistant Paramétrage du moteur de base de données peut s'utiliser pour recommander des index relatifs aux performances et il peut s'agir d'un mélange d'index alignés et non alignés. Pour plus d'informations, consultez Vue d'ensemble de l'Assistant Paramétrage du moteur de base de données.
Il peut être utile de concevoir un index partitionné indépendant (non aligné) de la table de base dans les cas suivants :
la table de base n'a pas été partitionnée ;
la clé d'index est unique et elle ne doit pas contenir la colonne de partitionnement de la table ;
vous souhaitez que la table de base soit impliquée dans des jointures communes à plusieurs tables en utilisant différentes colonnes de jointure.
[!REMARQUE]
Pour activer la commutation de partitionnement, tous les index de la table doivent être alignés.
Pensez aux informations des sections suivantes lorsque vous créez un index partitionné.
Partitionnement d'index uniques
Lors du partitionnement d'un index unique (cluster ou non-cluster), la colonne de partitionnement doit être choisie parmi celles utilisées dans la clé de l'index unique.
[!REMARQUE]
Cette restriction permet à SQL Server d'étudier une seule partition pour vérifier qu'aucun doublon d'une nouvelle valeur de clé n'existe déjà dans la table.
S'il n'est pas possible d'inclure la colonne de partitionnement dans la clé unique, vous devez utiliser un déclencheur DML à la place pour appliquer l'unicité.
Partitionnement des index cluster
Lors du partitionnement d'un index cluster, la clé de cluster doit contenir la colonne de partitionnement. Lors du partitionnement d'un index cluster non unique, alors que la colonne de partitionnement n'est pas spécifiée explicitement dans la clé de cluster, SQL Server ajoute cette colonne par défaut à la liste des clés d'index cluster. Si l'index cluster est unique, vous devez spécifier explicitement que la clé d'index cluster contient la colonne de partitionnement.
Partitionnement d'index non-cluster
Lors du partitionnement d'un index non-cluster unique, la clé d'index doit contenir la colonne de partitionnement. Lors du partitionnement d'un index non-cluster non unique, SQL Server ajoute la colonne de partitionnement par défaut comme colonne non-clé (incluse) de l'index pour garantir que l'index est aligné avec la table de base. SQL Server n'ajoute pas la colonne de partitionnement à l'index si elle y figure déjà.
Limitations de mémoire et index partitionnés
Les limitations de mémoire peuvent affecter les performances de SQL Server ou sa capacité à créer un index partitionné. C'est le cas notamment lorsque l'index n'est pas aligné avec sa table de base ou son index cluster, si un index cluster a été appliqué à la table.
Lorsque SQL Server effectue un tri pour créer des index partitionnés, il commence par créer une table de tri pour chaque partition. Ensuite, il produit les tables de tri soit dans le groupe de fichiers de chaque partition, soit dans tempdb, si l'option d'index SORT_IN_TEMPDB est spécifiée.
La création de chaque table de tri nécessite une quantité minimale de mémoire. Lorsque vous créez un index partitionné qui est aligné avec sa table de base, les tables de tri sont créées une par une, ce qui utilise moins de mémoire. Toutefois, lorsque vous créez un index partitionné non aligné, les tables de tri sont produites en même temps.
De ce fait, il doit y avoir assez de mémoire pour gérer ces tri simultanés. Plus il y a de partitions, plus il faut de mémoire. La taille minimale pour chaque table de tri, pour chaque partition, est de 40 pages, à raison de 8 kilo-octets par page. Par exemple, un index partitionné non aligné avec 100 partitions nécessite une quantité de mémoire suffisante pour trier en série 4 000 (40 * 100) pages à la fois. Si cette mémoire est disponible, l'opération de création réussit, mais les performances risquent d'en pâtir. Sinon, la création échoue. À l'inverse, un index partitionné aligné avec 100 partitions n'a besoin que de la mémoire suffisante pour trier 40 pages, parce que les tris ne sont pas effectués en même temps.
Pour les deux types d'index, alignés et non alignés, la mémoire requise peut être beaucoup plus importante si SQL Server applique divers degrés de parallélisme à l'opération de création sur un ordinateur multiprocesseur. En effet, plus il y a de degrés de parallélisme, plus il faut de mémoire. Par exemple, si SQL Server définit les degrés de parallélisme avec la valeur 4, un index partitionné non aligné avec 100 partitions a besoin d'une quantité de mémoire suffisante pour quatre processeurs pour trier 4 000 pages à la fois, ou 16 000 pages. Si l'index partitionné est aligné, la mémoire requise est moins importante puisqu'il en faut pour quatre processeurs triant 40 pages ou 160 (4 * 40) pages. Vous pouvez utiliser l'option d'index MAXDOP pour réduire manuellement les degrés de parallélisme. Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.
Pour plus d'informations sur la manière dont SQL Server effectue les opérations de tri lorsque vous créez des index, consultez tempdb et création d'index.