Utilisation des indicateurs de requête INDEX et FORCESEEK dans des repères de plan

Méthodes conseillées

Nous vous recommandons d'appliquer les méthodes conseillées ci-dessous :

• Utilisez les indicateurs INDEX et FORCESEEK comme indicateurs de requête uniquement dans le contexte d'un repère de plan ou dans des requêtes ad hoc lorsque vous testez des instructions de repère de plan. Pour toutes les autres requêtes ad hoc, spécifiez ces indicateurs comme indicateurs de table.

• Avant d'utiliser l'indicateur FORCESEEK, assurez-vous que les statistiques sur la base de données sont actuelles et exactes.

  Des statistiques à jour permettent à l'optimiseur d'évaluer précisément le coût des différents plans de requête et de choisir un plan de haute qualité. Par conséquent, nous vous recommandons d'affecter la valeur ON (la valeur par défaut) aux options AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS pour chaque base de données utilisateur. Vous pouvez également mettre à jour manuellement les statistiques sur une table ou une vue en utilisant l'instruction UPDATE STATISTICS.

• N'utilisez pas inutilement l'indicateur INDEX en association avec FORCESEEK. Autrement dit, si FORCESEEK seul produit un plan adéquat, le fait d'utiliser également l'indicateur INDEX peut limiter excessivement les choix de l'optimiseur. En outre, un indicateur INDEX provoque l'échec de la requête si vous modifiez le schéma physique de la table pour éliminer l'index spécifié dans l'indicateur. En revanche, tant qu'il existe au moins un index utilisable sur la table sur laquelle l'indicateur FORCESEEK est appliqué, la requête est compilée même si vous modifiez les structures d'index.

• N'utilisez pas l'indicateur INDEX (0) avec l'indicateur FORCESEEK. L'indicateur INDEX (0) force une analyse de la table de base. Lorsqu'il est associé à FORCESEEK, aucun plan n'est trouvé et l'erreur 8622 est retournée.

• N'utilisez pas l'indicateur de requête USE PLAN avec l'indicateur FORCESEEK. Si vous le faites, l'indicateur FORCESEEK est ignoré.

Utilisation des indicateurs INDEX et FORCESEEK avec d'autres indicateurs de table

Les indicateurs INDEX et FORCESEEK peuvent être spécifiés pour une requête sans indicateurs de table existants ou être utilisés pour remplacer un ou plusieurs indicateurs INDEX ou FORCESEEK existants dans la requête. Si une requête mise en correspondance avec un repère de plan possède déjà une clause WITH qui spécifie ces indicateurs de table, les indicateurs spécifiés dans le paramètre @hints du repère de plan remplacent ceux présents dans la requête. Par exemple, si la requête contient l'indicateur de tableWITH INDEX (PK_Employee_EmployeeID) pour la table HumanResources.Employee et que le paramètre @hints dans le repère de plan spécifie OPTION (TABLE HINT ( HumanResources.Employee, INDEX( IX_Employee_ManagerID ) ), l'optimiseur de requête utilisera l'index IX_Employee_ManagerID.

Les indicateurs de table autres que INDEX et FORCESEEK sont interdits comme indicateurs de requête dans le repère de plan, à moins que la requête n'ait déjà une clause WITH qui spécifie l'indicateur de table. Dans ce cas, un indicateur correspondant doit également être spécifié comme indicateur de requête en utilisant TABLE HINT dans la clause OPTION pour conserver la sémantique de la requête. Par exemple, si la requête contient l'indicateur de table NOLOCK, le paramètre @hints du repère de plan doit également contenir l'indicateur NOLOCK, en plus des indicateurs de table INDEX ou FORCESEEK dans la clause OPTION. Voir l'exemple C plus loin dans cette rubrique. Lorsqu'un indicateur de table autre que INDEX ou FORCESEEK est spécifié en utilisant TABLE HINT dans la clause OPTION sans indicateur de requête correspondant, ou vice versa, l'erreur 8702 est déclenchée (indiquant que la clause OPTION peut entraîner la modification de la sémantique de la requête) et la requête échoue.

Utilisation des indicateurs INDEX et FORCESEEK avec d'autres indicateurs de requête

Si une requête mise en correspondance avec un repère de plan possède déjà une clause OPTION qui spécifie des indicateurs de requête, les indicateurs de requête spécifiés dans le paramètre @hints du repère de plan remplacent ceux présents dans la requête. Toutefois, pour qu'un repère de plan corresponde à une requête possédant déjà une clause OPTION, vous devez inclure la clause OPTION de la requête lorsque vous spécifiez le texte de la requête avec laquelle établir la correspondance dans l'instruction sp_create_plan_guide (Transact-SQL). Si vous souhaitez que les indicateurs spécifiés dans le repère de plan soient ajoutés aux indicateurs qui existent déjà sur la requête, au lieu de les remplacer, vous devez spécifier les indicateurs d'origine et les indicateurs supplémentaires dans la clause OPTION du repère de plan.

Exemples

A. Utilisation de FORCESEEK

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

B. Utilisation de plusieurs indicateurs de table

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

C. Spécification d'indicateurs affectant la sémantique

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID) , NOLOCK, FORCESEEK ))';
GO

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

D. Utilisation de TABLE HINT pour remplacer un indicateur de table existant

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

Voir aussi

Référence

Concepts