Partager via

  • Article

Optimisation des requêtes qui ont accès à des colonnes datetime corrélées

L'option SET de base de données DATE_CORRELATION_OPTIMIZATION améliore les performances des requêtes qui effectuent une équi-jointure entre deux tables dont les colonnes date ou datetime sont corrélées et spécifient une restriction de date dans le prédicat des requêtes.

Les tables dont les valeurs de colonne date ou datetime sont corrélées et qui peuvent bénéficier de l'activation de l'option DATE_CORRELATION_OPTIMIZATION font généralement partie d'une relation un-à-plusieurs et sont essentiellement utilisées à des fins d'aide à la décision, de création de rapports ou d'entreposage de données.

Ainsi, dans l'exemple de base de données AdventureWorks2008R2, la colonne OrderDate de la table Purchasing.PurchaseOrderHeader et la colonne DueDate de la table Purchasing.PurchaseOrderDetail sont corrélées. Les valeurs de date de PurchaseOrderDetail.DueDate ont tendance à suivre de près celles de PurchaseOrderHeader.OrderDate.

Lorsque l'option de base de données DATE_CORRELATION_OPTIMIZATION a pour valeur ON, SQL Server gère des statistiques de corrélation sur toute paire de tables de la base de données détenant des colonnes date ou datetime et liées par une contrainte FOREIGN KEY définie sur une colonne. Par défaut, cette option a pour valeur OFF.

SQL Server utilise ces statistiques de corrélation avec la restriction de date spécifiée dans le prédicat de requête pour déduire la possibilité d'ajouter des restrictions à la requête sans modifier l'ensemble de résultats. L'optimiseur de requête utilise ces conditions inférées lorsqu'il choisit un plan de requête. Il peut en résulter un plan de requête plus rapide, car les restrictions ajoutées permettent à SQL Server de lire moins de données lorsqu'il traite la requête. Les performances sont également améliorées lorsque des index cluster sont définis sur les deux tables et que leur colonne date ou datetime faisant l'objet d'une gestion de statistiques de corrélation est la première ou l'unique clé de l'index cluster.

Par exemple, supposons que vous prépariez la base de données AdventureWorks2008R2 pour gérer les informations de corrélation de Purchasing.PurchaseOrderDetail et Purchasing.PurchaseOrderHeader en exécutant le script Transact-SQL suivant :

USE AdventureWorks2008R2;
GO
-- Create a unique index to take the place of the existing 
-- primary key constraint
CREATE UNIQUE NONCLUSTERED INDEX
IX_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID 
ON Purchasing.PurchaseOrderDetail(PurchaseOrderID,PurchaseOrderDetailID);
GO
-- Drop existing clustered index by dropping constraint
ALTER TABLE Purchasing.PurchaseOrderDetail
DROP CONSTRAINT PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID;
GO
-- Create new clustered index on DueDate
CREATE CLUSTERED INDEX IX_PurchaseOrderDetail_DueDate
ON Purchasing.PurchaseOrderDetail(DueDate);
GO
--Enable DATE_CORRELATION_OPTIMIZATION database option
ALTER DATABASE AdventureWorks2008R2
   SET DATE_CORRELATION_OPTIMIZATION ON;
GO

Maintenant, supposons que vous exécutiez la requête suivante :

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
INNER JOIN Purchasing.PurchaseOrderDetail AS d
    ON h.PurchaseOrderID = d.PurchaseOrderID
WHERE h.OrderDate BETWEEN '20060101' AND '20060201';

Les valeurs de PurchaseOrderDetail.DueDate retournées par cette requête peuvent généralement être comprises dans une période de 14 jours, par exemple, des valeurs de PurchaseOrderHeader.OrderDate. Par conséquent, SQL Server est en mesure d'en déduire que la requête précédente peut être mieux exprimée à l'aide d'une requête comparable à la suivante :

SELECT *
FROM Purchasing.PurchaseOrderHeader AS h,
    Purchasing.PurchaseOrderDetail AS d
WHERE h.PurchaseOrderID = d.PurchaseOrderID
AND h.OrderDate BETWEEN '1/1/06' AND '2/1/06'
AND d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14;

La forme exacte de la condition ajoutée, spécifiée dans la deuxième clause AND, dépend de la requête initiale et des valeurs des données de la base de données. Après avoir ajouté une condition implicite, l'optimiseur l'utilise pour construire un plan d'exécution. Dans cet exemple, il existe un index cluster sur PurchaseOrderDetail.DueDate, si bien que cet index peut être utilisé pour récupérer les lignes qui satisfont à la syntaxe d.DueDate BETWEEN CAST ('20060101' AS datetime) + 14 AND CAST ('20060201' AS datetime) + 14. Si les données de Purchasing.PurchaseOrderDetail portent sur plusieurs années, cette requête peut considérablement réduire le temps d'exécution par rapport à la requête initiale (d'un facteur n).

Avant d'exécuter un plan de requête avec une condition qui est inférée en raison de l'activation de DATE_CORRELATION_OPTIMIZATION, SQL Server vérifie que la requête générera la réponse adéquate, en fonction du contenu actuel de la base de données.

Contraintes liées à l'utilisation de l'option de base de données DATE_CORRELATION_OPTIMIZATION

Toutes les conditions suivantes doivent être réunies pour que les deux tables bénéficient de l'activation de l'option de base de données DATE_CORRELATION_OPTIMIZATION :

  • Les options SET de base de données doivent être définies de la façon suivante : ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL et QUOTED IDENTIFIER doivent avoir pour valeur ON. NUMERIC_ROUNDABORT doit avoir pour valeur OFF.

  • Les tables doivent être liées par une relation de clé étrangère définie sur une colonne.

  • Les tables doivent posséder des colonnes datetime paramétrées sur NOT NULL.

  • Au moins une des colonnes datetime doit être la colonne clé d'un index cluster (il doit s'agir de la première clé si la clé d'index est composite) ou doit être la colonne de partitionnement (dans le cas d'une table partitionnée).

  • Les deux tables doivent être détenues par le même utilisateur.

Tenez compte des points suivants lorsque vous attribuez la valeur ON à l'option de base de données DATE_CORRELATION_OPTIMIZATION :

  • SQL Server gère les informations de corrélation sous la forme de statistiques. Ces statistiques sont mises à jour par SQL Server pendant les opérations INSERT, UPDATE et DELETE sur les tables appropriées, ce qui peut affecter les performances de ces opérations. N'activez pas DATE_CORRELATION_OPTIMIZATION dans les environnements de base de données gourmands en mises à jour.

  • Si l'une des colonnes datetime dont les statistiques de corrélation sont gérées n'est pas la première ou l'unique clé d'un index cluster, pensez à y créer un index cluster. Cette opération permet généralement d'obtenir de meilleures performances de la part des types de requêtes couvertes par des statistiques de corrélation. Si un index cluster existe déjà sur les colonnes de clé primaire, vous pouvez modifier une table de manière à ce que l'index cluster et la clé primaire utilisent différents jeux de colonnes.

  • L'activation de l'option DATE_CORRELATION_OPTIMIZATION ne présente aucun avantage dans les situations suivantes :

    • Aucune paire de tables ne satisfait aux critères indiqués précédemment en matière de gestion de statistiques de corrélation.

    • Certaines paires de tables satisfont aux critères de gestion de statistiques de corrélation, mais aucune restriction de date n'est spécifiée dans le prédicat des requêtes qui joignent ces tables.

Pour définir l'option de base de données DATE_CORRELATION_OPTIMIZATION

Utilisation des statistiques de corrélation

Pour toutes les paires de tables correspondantes éligibles, des statistiques de corrélation sont automatiquement créées sous la forme de vues indexées lorsque vous attribuez la valeur ON à l'option de base de données DATE_CORRELATION_OPTIMIZATION. Si l'optimiseur de requête SQL Server peut tirer parti de la corrélation entre des paires de colonnes datetime, il utilise ces statistiques de corrélation dans son plan de requête. Les statistiques de corrélation sont également incluses dans la logique des instructions INSERT, UPDATE et DELETE où elles sont affectées. Les noms des statistiques de corrélation présentent la forme suivante : 

MPStats_Sys<constraint_object_id><GUID><FK_constraint_name>

<FK_constraint_name> représente le nom de la contrainte FOREIGN KEY dans l'affichage catalogue sys.objects sur lequel est basée la correspondance datetime. <constraint_object_id> est une représentation hexadécimale sur 8 chiffres de l'objectid de la contrainte FOREIGN KEY.

Notes

SQL Server raccourcit la partie FK_constraint_ du nom des statistiques de corrélation s'il dépasse la limite de la longueur des identificateurs.

Lorsque vous exécutez une requête à l'aide de SET SHOWPLAN XML, tout nœud de filtre dérivé des statistiques de corrélation comprend l'attribut suivant :

DateCorrelationOptimization="true"

Par exemple, un nœud <Predicate> influencé par les statistiques de corrélation présente l'aspect suivant :

<Predicate DateCorrelationOptimization="true">

Cet attribut est inclus avec tout nœud de filtre généré complètement à partir des statistiques de corrélation ou à partir de la combinaison d'un prédicat influencé par les statistiques de corrélation et d'un autre prédicat.

En règle générale, lorsque l'option de base de données DATE_CORRELATION_OPTIMIZATION a pour valeur ON, SQL Server crée des statistiques de corrélation pour toutes les paires éligibles de colonnes datetime . SQL Server crée des statistiques de corrélation supplémentaires lorsque vous effectuez les opérations suivantes :

  • Vous créez des contraintes FOREIGN KEY par le biais d'instructions CREATE TABLE ou ALTER TABLE qui satisfont aux conditions d'optimisation de la corrélation des données datetime.

  • Vous créez un index cluster sur une colonne datetime pouvant être corrélée avec la colonne datetime d'une autre table.

    Notes

    Aucune statistique de corrélation n'est créée lorsque des index cluster sont créés à l'aide de l'option ONLINE = ON. Toutefois, une fois la construction d'index validée, des statistiques de corrélation qui dépendent de l'index peuvent être créées suite à un événement dans une autre transaction, telle que la création d'une contrainte FOREIGN KEY.

  • Vous modifiez l'acceptation de valeurs NULL ou le type de données d'une colonne afin qu'elle puisse être corrélée avec la colonne datetime d'une autre table.

Évitez de faire référence aux statistiques de corrélation directement dans les applications, car SQL Server peut décider de supprimer ces statistiques à tout moment. Vous pouvez décider de supprimer des statistiques de corrélation spécifiques si vous estimez que le coût de leur gestion affecte les performances. La valeur par défaut des autorisations DROP sur les statistiques de corrélation correspond aux membres du rôle de serveur fixe sysadmin, aux membres des rôles de base de données fixes db_owner et db_ddladmin et au propriétaire de la paire de tables sur laquelle les statistiques de corrélation sont définies. Ces autorisations ne sont pas transférables.

Les statistiques de corrélation sont supprimées dans les situations suivantes :

  • Lorsque vous attribuez la valeur OFF à l'option de base de données DATE_CORRELATION_OPTIMIZATION, toutes les statistiques de corrélation créées par SQL Server sont supprimées.

  • Les statistiques de corrélation dont la gestion exige un espace de stockage excessif ou qui ne sont pas supposées être bénéfiques sont supprimées.

  • Lorsque vous supprimez une contrainte FOREIGN KEY à l'aide de l'instruction DROP TABLE ou ALTER TABLE, toutes les statistiques de corrélation associées à cette contrainte sont supprimées.

  • Lorsque, à la suite d'une opération, les tables impliquées dans la mise en corrélation n'appartiennent plus au même utilisateur, les statistiques de corrélation correspondantes sont supprimées.

  • Lorsque vous exécutez une instruction ALTER TABLE…SWITCH et que des statistiques de corrélation sont définies sur la table source ou cible, ces statistiques sont supprimées.

  • Lorsque vous créez un index cluster sur une colonne datetime et que les statistiques de corrélation sont basées sur une autre colonne datetime de la même table, les statistiques de corrélation sont supprimées. SQL Server peut créer de nouvelles statistiques de corrélation basées sur l'index cluster nouvellement créé, si celui-ci est éligible.

  • Lorsque vous supprimez un index cluster dont la première clé d'index est une colonne datetime, toutes les statistiques de corrélation associées sont supprimées si la même table possède une autre colonne datetime sur laquelle de nouvelles statistiques de corrélation peuvent être créées.

  • Lorsque vous exécutez l'instruction ALTER TABLE pour modifier le type de données ou l'acceptation de valeurs NULL d'une colonne participant aux statistiques de corrélation, ces statistiques sont supprimées.

Les statistiques de corrélation sont créées ou supprimées dans le cadre de la transaction à l'origine de leur création ou suppression. Cette transaction n'est ni en ligne ni asynchrone.

Dans un scénario de paramétrage simple basé sur un serveur, lorsque vous utilisez l'Assistant Paramétrage du moteur de base de données pour paramétrer directement le serveur de production, il évalue les coûts et les avantages des statistiques de corrélation. Toutefois, lorsque vous utilisez l'Assistant Paramétrage du moteur de base de données dans un scénario de serveur de test de production, il ne considère pas les statistiques de corrélation comme des objets système internes. Par conséquent, lorsqu'il analyse le paramétrage des index, l'Assistant Paramétrage du moteur de base de données n'utilise pas les statistiques de corrélation dans l'optimisation des requêtes. Dans un scénario de test de production, vous pouvez ignorer les recommandations formulées par l'Assistant Paramétrage du moteur de base de données au sujet des vues indexées qui contiennent des statistiques de corrélation car il connaît leurs coûts mais par leurs avantages. Dans les deux scénarios, l'Assistant Paramétrage du moteur de base de données peut ne pas recommander la sélection de certains index tels que les index cluster des colonnes datetime ; la non-sélection de certains index pourrait être avantageuse lorsque l'option DATE_CORRELATION_OPTIMIZATION est activée.

Interrogation des métadonnées relatives aux statistiques de corrélation

Pour afficher le paramètre de l'option de base de données DATE_CORRELATION_OPTIMIZATION, sélectionnez la colonne is_date_correlation_on de l'affichage catalogue sys.databases.

Pour déterminer si une vue est basée sur des statistiques de corrélation, sélectionnez la colonne is_date_correlation_view de l'affichage catalogue sys.views.