Partager via

  • Article

Améliorations du traitement des requêtes sur les tables et les index partitionnés

SQL Server 2008 améliore les performances du traitement des requêtes sur les tables partitionnées pour de nombreux plans parallèles, modifie la façon dont les plans parallèles et en série sont représentés, et améliore les informations de partitionnement fournies dans les plans d'exécution de compilation et au moment de l'exécution. Cette rubrique décrit ces améliorations, fournit des indications sur la façon d'interpréter les plans d'exécution de requêtes de tables et d'index partitionnés, et fournit des recommandations pour améliorer les performances des requêtes sur les objets partitionnés.

Notes

Les tables et les index partitionnés sont uniquement pris en charge dans les éditions Enterprise, Developer et Evaluation de SQL Server.

Nouvelle opération de recherche sensible aux partitions

Dans SQL Server 2008, la représentation interne d'une table partitionnée est modifiée afin que la table apparaisse au processeur de requêtes sous forme d'un index multicolonne avec PartitionID comme colonne principale. PartitionID est une colonne calculée cachée utilisée en interne pour représenter l'ID de la partition contenant une ligne spécifique. Supposons, par exemple, que la table T définie comme T(a, b, c) est partitionnée sur la colonne a et possède un index cluster sur la colonne b. Dans SQL Server 2008, cette table partitionnée est traitée en interne comme une table non partitionnée, avec le schéma T(PartitionID, a, b, c) et un index cluster sur la clé composite (PartitionID, b). Cela permet à l'optimiseur de requête d'effectuer des opérations de recherche basées sur PartitionID sur n'importe quel table ou index partitionné.

L'élimination de partition est maintenant réalisée dans cette opération de recherche.

De plus, l'optimiseur de requête est étendu de telle sorte qu'une opération de recherche ou d'analyse avec une condition puisse être réalisée sur PartitionID (comme colonne principale logique) et éventuellement d'autres colonnes de clé d'index, puis une recherche de second niveau, avec une condition différente, peut être réalisée sur une ou plusieurs colonnes supplémentaires, pour chaque valeur distincte répondant à la qualification de l'opération de recherche de premier niveau. Autrement dit, cette opération, appelée analyse par saut, permet à l'optimiseur de requête d'effectuer une opération de recherche ou d'analyse basée sur une condition pour déterminer les partitions à accéder et une opération de recherche d'index de second niveau au sein de cet opérateur pour retourner les lignes de ces partitions qui répondent à une condition différente. Examinez, par exemple, la requête suivante.

SELECT * FROM T WHERE a < 10 and b = 2;

Dans cet exemple, supposons que la table T définie comme T(a, b, c) est partitionnée sur la colonne a et possède un index cluster sur la colonne b. Les limites de partition pour la table T sont définies par la fonction de partition suivante :

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (3, 7, 10);

Pour résoudre la requête, le processeur de requêtes effectue une opération de recherche de premier niveau pour rechercher chaque partition contenant des lignes répondant à la condition T.a < 10. Cela identifie les partitions à accéder. Dans chaque partition identifiée, le processeur effectue ensuite une recherche de second niveau dans l'index cluster sur la colonne b pour rechercher les lignes qui répondent aux conditions T.b = 2 et T.a < 10.

L'illustration suivante est une représentation logique de l'opération d'analyse par saut. Elle montre la table T avec des données dans les colonnes a et b. Les partitions sont numérotées de 1 à 4 et les limites de partition sont indiquées par des lignes verticales en pointillé. Une opération de recherche de premier niveau dans les partitions (non représentée dans l'illustration) a déterminé que les partitions 1, 2 et 3 répondent à la condition de recherche impliquée par le partitionnement défini pour la table et le prédicat sur la colonne a. À savoir, T.a < 10. Le chemin d'accès parcouru par la partie de la recherche de second niveau de l'opération d'analyse par saut est illustré par la ligne courbée. Fondamentalement, l'opération d'analyse par saut recherche dans chacune de ces partitions les lignes qui répondent à la condition b = 2. Le coût total de l'opération d'analyse par saut est le même que celui de trois recherches d'index séparées.

Montre l'opération d'analyse par saut.

Affichage d'informations de partitionnement dans les plans d'exécution de requêtes

Les plans d'exécution de requêtes sur les tables et les index partitionnés peuvent être examinés en utilisant les instructions SET Transact-SQL SET SHOWPLAN_XML ou SET STATISTICS XML, ou en utilisant la sortie du plan d'exécution graphique dans SQL Server Management Studio. Par exemple, vous pouvez afficher le plan d'exécution de compilation en cliquant sur Afficher le plan d'exécution estimé dans la barre d'outils de l'éditeur de requête et le plan au moment de l'exécution en cliquant sur Inclure le plan d'exécution réel.

À l'aide de ces outils, vous pouvez déterminer les informations suivantes :

  • les opérations telles que les analyses, les recherches, les insertions, les mises à jour, les fusions et les suppressions qui accèdent aux tables ou aux index partitionnés ;

  • les partitions auxquelles accèdent la requête (par exemple, le nombre total de partitions ayant fait l'objet d'un accès et les plages de partitions contiguës qui font l'objet d'un accès sont disponibles dans les plans au moment de l'exécution) ;

  • lorsque l'opération d'analyse par saut est utilisée dans une opération de recherche ou d'analyse pour récupérer les données d'une ou de plusieurs partitions.

Pour plus d'informations sur l'affichage des plans d'exécution, consultez Rubriques sur les procédures concernant les plans d'exécution.

Améliorations apportées aux informations de partition

SQL Server 2008 fournit des informations de partitionnement améliorées pour les plans d'exécution de compilation et au moment de l'exécution. Les plans d'exécution fournissent désormais les informations suivantes :

  • Un attribut Partitioned facultatif qui indique qu'un opérateur, tel qu'une recherche, une analyse, une insertion, une mise à jour, une fusion ou une suppression, est effectué sur une table partitionnée.

  • Un nouvel élément SeekPredicateNew avec un sous-élément SeekKeys qui inclut PartitionID comme la colonne de clé d'index principale et des conditions de filtrage qui spécifient les recherches de plage sur PartitionID. La présence de deux sous-éléments SeekKeys indique qu'une opération d'analyse par saut sur PartitionID est utilisée.

  • Des informations de résumé qui indiquent le nombre total de partitions ayant fait l'objet d'un accès. Ces informations sont uniquement disponibles dans les plans au moment de l'exécution.

Pour démontrer comment ces informations sont affichées dans la sortie du plan d'exécution graphique et dans la sortie du plan d'exécution XML, considérez la requête suivante sur la table partitionnée fact_sales. Cette requête met à jour les données dans deux partitions.

UPDATE fact_sales

SET quantity = quantity * 2

WHERE date_id BETWEEN 20080802 AND 20080902;

L'illustration suivante montre les propriétés de l'opérateur Clustered Index Seek dans le plan d'exécution de compilation pour cette requête. Pour examiner la définition de la table fact_sales et la définition de la partition, consultez la section « Exemple » dans cette rubrique.

Informations de partitions dans la sortie Showplan.

Attributs partitionnés

Lorsqu'un opérateur tel que Index Seek est exécuté sur une table ou un index partitionné, l'attribut Partitioned apparaît dans le plan de compilation et au moment de l'exécution et a pour valeur True (1). L'attribut ne s'affiche pas lorsqu'il a pour valeur False (0).

L'attribut Partitioned peut apparaître dans les opérateurs physiques et logiques suivants :

  • Table Scan

  • Index Scan

  • Index Seek

  • Insert

  • Update

  • Delete

  • Merge

Comme indiqué dans l'illustration précédente, cet attribut est affiché dans les propriétés de l'opérateur dans lequel il est défini. Dans la sortie du plan d'exécution XML, cet attribut apparaît comme Partitioned="1" dans le nœud RelOp de l'opérateur dans lequel il est défini.

Nouveau prédicat de recherche

Dans la sortie du plan d'exécution XML, l'élément SeekPredicateNew apparaît dans l'opérateur dans lequel il est défini. Il peut contenir jusqu'à deux occurrences du sous-élément SeekKeys. Le premier élément SeekKeys spécifie l'opération de recherche de premier niveau au niveau de l'ID de partition de l'index logique. Autrement dit, cette recherche détermine les partitions qui doivent être faire l'objet d'un accès pour satisfaire aux conditions de la requête. Le deuxième élément SeekKeys spécifie la partie de la recherche de second niveau de l'opération d'analyse par saut qui se produit dans chaque partition identifiée dans la recherche de premier niveau.

Informations de résumé sur les partitions

Dans les plans au moment de l'exécution, les informations de résumé sur les partitions fournissent le nombre des partitions ayant fait l'objet d'un accès et l'identité des partitions ayant réellement fait l'objet d'un accès. Vous pouvez utiliser ces informations pour vérifier que la requête accède aux bonnes partitions et que toutes les autres partitions sont ignorées.

Les informations suivantes sont fournies : Actual Partition Count et Partitions Accessed.

Actual Partition Count est le nombre total de partitions auxquelles la requête a accédé.

Partitions Accessed, dans la sortie du plan d'exécution XML, correspond aux informations de résumé sur les partitions qui apparaissent dans le nouvel élément RuntimePartitionSummary dans le nœud RelOp de l'opérateur dans lequel il est défini. L'exemple suivant affiche le contenu de l'élément RuntimePartitionSummary, indiquant que deux partitions au total font l'objet d'un accès (partitions 2 et 3).

<RunTimePartitionSummary>

    <PartitionsAccessed PartitionCount="2" >

        <PartitionRange Start="2" End="3" />

    </PartitionsAccessed>

</RunTimePartitionSummary>

Affichage d'informations sur les partitions en utilisant d'autres méthodes de plan d'exécution de requêtes

Les méthodes de plan d'exécution de requêtes SHOWPLAN_ALL, SHOWPLAN_TEXT et STATISTICS PROFILE ne signalent pas les informations sur les partitions décrites dans cette rubrique, avec toutefois l'exception suivante. Dans le cadre du prédicat SEEK, les partitions à accéder sont identifiées par un prédicat de plage sur la colonne calculée qui représente l'ID de partition. L'exemple suivant affiche le prédicat SEEK pour un opérateur Clustered Index Seek. Les partitions 2 et 3 font l'objet d'un accès et l'opérateur de recherche filtre les lignes qui répondent à la condition date_id BETWEEN 20080802 AND 20080902.

|--Clustered Index Seek(OBJECT:([db_sales_test].[dbo].[fact_sales].[ci]),

        SEEK:([PtnId1000] >= (2) AND [PtnId1000] <= (3)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] >= (20080802)

                AND [db_sales_test].[dbo].[fact_sales].[date_id] <= (20080902))

                ORDERED FORWARD)

Interprétation des plans d'exécution pour les segments de mémoire partitionnés

Dans SQL Server 2008, un segment de mémoire partitionné est traité comme un index logique sur l'ID de partition. Une élimination de partition sur un segment de mémoire partitionné est représentée dans un plan d'exécution en tant qu'opérateur Table Scan avec un prédicat de recherche sur l'ID de partition. L'exemple suivant affiche les informations du plan d'exécution de requêtes fournies :

|-- Table Scan (OBJECT: ([db].[dbo].[T]), SEEK: ([PtnId1001]=[Expr1011]) ORDERED FORWARD)

Interprétation des plans d'exécution pour les jointures en colocation

La colocation de jointure peut se produire lorsque deux tables sont partitionnées à l'aide de la même fonction de partitionnement ou d'une fonction de partitionnement équivalente et que les colonnes de partitionnement des deux côtés de la jointure sont spécifiées dans la condition de jointure de la requête. L'optimiseur de requête peut générer un plan où les partitions de chaque table avec des ID de partition identiques sont jointes séparément. Les jointures en colocation peuvent être plus rapides que les autres jointures car elles peuvent nécessiter moins de mémoire et occasionner un temps de traitement inférieur. L'optimiseur choisit un plan en colocation ou non en fonction des estimations de coût.

Dans un plan en colocation, la jointure Nested Loops lit une ou plusieurs partitions de table ou d'index jointes en interne. Les nombres dans les opérateurs Constant Scan représentent les numéros de partition.

Lorsque des plans parallèles pour des jointures en colocation sont générés pour des tables ou des index partitionnés, un opérateur Parallelism apparaît entre les opérateurs de jointure Constant Scan et Nested Loops. Dans ce cas, chacun des threads côté extérieur de la jointure lit et opère sur une partition distincte.

L'illustration suivante montre un plan de requête parallèle pour une jointure en colocation.

Plan d'exécution de jointures en colocation

Stratégie d'exécution de requête parallèle pour les objets partitionnés

Le processeur de requêtes utilise une stratégie d'exécution parallèle pour les requêtes qui sélectionnent parmi des objets partitionnés. Dans le cadre de la stratégie d'exécution, le processeur de requêtes détermine les partitions de table requises pour la requête et la proportion de threads à allouer à chaque partition. Dans la plupart des cas, le processeur de requêtes alloue un nombre de threads égal ou presque égal à chaque partition, puis il exécute la requête en parallèle sur les partitions. Les paragraphes suivants expliquent l'allocation des threads de manière détaillée.

Si le nombre de threads est inférieure au nombre de partitions, le processeur de requêtes assigne chaque thread à une partition différente, en laissant initialement une ou plusieurs partitions sans thread assigné. Lorsqu'un thread a fini de s'exécuter sur une partition, le processeur de requêtes l'assigne à la partition suivante jusqu'à ce qu'un thread unique ait été assigné à chaque partition. Il s'agit du seul cas dans lequel le processeur de requêtes réaffecte des threads à d'autres partitions.

Montre le thread réaffecté une fois terminé

Si le nombre de threads est égal au nombre de partitions, le processeur de requêtes assigne un thread à chaque partition. Lorsqu'un thread se termine, il n'est pas réaffecté à une autre partition.

Montre un thread alloué à chaque partition

Si le nombre de threads est supérieur au nombre de partitions, le processeur de requêtes alloue une quantité égale de threads à chaque partition. Si le nombre de threads n'est pas un multiple exact du nombre de partitions, le processeur de requêtes alloue un thread supplémentaire à certaines partitions afin d'utiliser tous les threads disponibles. Notez que s'il n'y a qu'une seule partition, tous les threads sont assignés à cette partition. Dans le schéma ci-dessous, il y a quatre partitions et 14 threads. Trois threads sont assignés à chaque partition et deux partitions ont un thread supplémentaire, pour un total de 14 affectations de thread. Lorsqu'un thread se termine, il n'est pas réassigné à une autre partition.

Montre plusieurs threads alloués aux partitions

Bien que les exemples ci-dessus suggèrent une méthode simple pour allouer des threads, la stratégie réelle est plus complexe et prend en considération d'autres variables durant l'exécution de la requête. Par exemple, si la table est partitionnée et a un index cluster sur la colonne A et qu'une requête a la clause de prédicat WHERE A IN (13, 17, 25),, le processeur de requêtes alloue un ou plusieurs threads à chacune de ces trois valeurs de recherche (A=13, A=17 et A=25) au lieu de chaque partition de table. Il est nécessaire d'exécuter la requête uniquement dans les partitions qui contiennent ces valeurs, et si tous ces prédicats d'accès se trouvent être dans la même partition de table, tous les threads sont assignés à la même partition de table.

Pour prendre un autre exemple, supposez que la table a quatre partitions sur la colonne A avec des points de limite (10, 20, 30), un index sur la colonne B, et que la requête a une clause de prédicat WHERE B IN (50, 100, 150). Les partitions de table étant basées sur les valeurs de A, les valeurs de B peuvent se produire dans chacune des partitions de table. Par conséquent, le processeur de requêtes recherche chacune des trois valeurs de B (50, 100, 150) dans chacune des quatre partitions de table. Le processeur de requêtes assignera des threads proportionnellement afin de pouvoir exécuter chacune de ces 12 analyses de requête en parallèle.

Partitions de table basées sur la colonne A

Recherches pour la colonne B dans chaque partition de table

Partition de table 1 : A < 10

B=50, B=100, B=150

Partition de table 2 : A >= 10 AND A < 20

B=50, B=100, B=150

Partition de table 3 : A >= 20 AND A < 30

B=50, B=100, B=150

Partition de table 4 : A >= 30

B=50, B=100, B=150

Recommandations

Pour améliorer les performances des requêtes qui accèdent à une grande quantité de données à partir de tables et d'index partitionnés volumineux, nous vous recommandons d'appliquer les méthodes conseillées suivantes :

  • Agrégez par bandes chaque partition sur plusieurs disques.

  • Dans la mesure du possible, utilisez un serveur avec suffisamment de mémoire principale pour prendre en charge les partitions faisant l'objet d'un accès fréquent ou toutes les partitions afin de réduire le coût des E/S.

  • Si les données que vous interrogez ne tiennent pas en mémoire, compressez les tables et les index. Cela réduira le coût des E/S.

  • Utilisez un serveur avec des processeurs rapides et autant de noyaux de processeur que^possible selon vos moyens pour tirer parti des capacités de traitement de requête parallèle.

  • Assurez-vous que le serveur possède une bande passante de contrôleur d'E/S suffisante.

  • Créez un index cluster sur chaque grande table partitionnée pour tirer parti des optimisations d'analyse d'arbre B (B-tree).

  • Appliquez les recommandations mentionnées dans le livre blanc « Loading Bulk Data into a Partitioned Table » (en anglais) lors du chargement en masse des données dans des tables partitionnées.

Exemple

L'exemple suivant crée une base de données de test contenant une table unique avec sept partitions. Utilisez les outils décrits précédemment lors de l'exécution des requêtes dans cet exemple pour afficher des informations de partitionnement pour le plan de compilation et le plan au moment de l'exécution.

Notes

Cet exemple insère plus d'un million de lignes dans la table. En fonction de votre matériel, l'exécution de cet exemple peut prendre plusieurs minutes. Avant d'exécuter cet exemple, vérifiez que l'espace disque dont vous disposez est supérieur à 1,5 Go.

USE master;
GO
IF DB_ID (N'db_sales_test') IS NOT NULL
    DROP DATABASE db_sales_test;
GO
CREATE DATABASE db_sales_test;
GO
USE db_sales_test;
GO
CREATE PARTITION FUNCTION [pf_range_fact](int) AS RANGE RIGHT FOR VALUES 
(20080801, 20080901, 20081001, 20081101, 20081201, 20090101);
GO
CREATE PARTITION SCHEME [ps_fact_sales] AS PARTITION [pf_range_fact] 
ALL TO ([PRIMARY]);
GO
CREATE TABLE fact_sales(date_id int, product_id int, store_id int, 
    quantity int, unit_price numeric(7,2), other_data char(1000))
ON ps_fact_sales(date_id);
GO
CREATE CLUSTERED INDEX ci ON fact_sales(date_id);
GO
PRINT 'Loading...';
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i<1000000)
BEGIN
    INSERT INTO fact_sales VALUES(20080800 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
GO
DECLARE @i int;
SET @i = 1;
WHILE (@i<10000)
BEGIN
    INSERT INTO fact_sales VALUES(20080900 + (@i%30) + 1, @i%10000, @i%200, RAND() * 25, (@i%3) + 1, '');
    SET @i += 1;
END;
PRINT 'Done.';
GO
-- Two-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080802 AND 20080902
GROUP BY date_id ;
GO
SET STATISTICS XML OFF;
GO
-- Single-partition query.
SET STATISTICS XML ON;
GO
SELECT date_id, SUM(quantity*unit_price) AS total_price
FROM fact_sales
WHERE date_id BETWEEN 20080801 AND 20080831
GROUP BY date_id;
GO
SET STATISTICS XML OFF;
GO