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

Vous pouvez spécifier les indicateurs de table INDEX et FORCESEEK en tant qu'indicateurs de requête. Lorsqu'ils sont spécifiés en tant qu'indicateurs de requête, ces indicateurs se comportent comme une table inline ou un indicateur de vue.

L'indicateur INDEX force l'optimiseur de requête à utiliser uniquement les index spécifiés pour accéder aux données dans la table ou la vue référencée dans la requête. L'indicateur FORCESEEK force l'optimiseur à utiliser uniquement une opération de recherche d'index pour accéder aux données dans la table ou la vue référencée. Ces indicateurs peuvent être utilisés dans la clause OPTION d'un repère de plan pour influencer l'optimisation d'une requête. Lorsqu'une requête correspond à un repère de plan, la clause OPTION spécifiée dans le repère de plan est ajoutée à la requête avant qu'elle ne soit compilée et optimisée. Pour plus d'informations sur les repères de plan, consultez Description des repères de plan.

Attention
Les repères de plan utilisant les indicateurs de requête à mauvais escient peuvent entraîner des problèmes de compilation, d'exécution ou de performances. Les repères de plan doivent être utilisés uniquement par des administrateurs de base de données et des développeurs expérimentés.

Lorsqu'ils sont spécifiés comme indicateurs de requête, les indicateurs de table INDEX et FORCESEEK sont valides pour les objets suivants :

• Tables

• Vues

• Vues indexées

• Expressions de table communes (L'indicateur doit être spécifié dans l'instruction SELECT dont le jeu de résultats remplit l'expression de table commune.)

• Vues de gestion dynamique

• Sous-requêtes nommées

Les indicateurs de table ne peuvent pas être spécifiés pour les fonctions table, les variables de table ou les instructions OPENROWSET.

Pour spécifier un indicateur d'index pour une vue indexée, l'indicateur NOEXPAND doit également être spécifié dans la clause OPTION, sinon l'indicateur d'index est ignoré. Pour plus d'informations, consultez Résolution d'index sur les vues.

Pour plus d'informations sur la syntaxe utilisée pour spécifier les indicateurs INDEX et FORCESEEK comme indicateurs de requête, consultez Indicateurs de requête (Transact-SQL).

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

L'exemple suivant utilise l'indicateur FORCESEEK dans le paramètre @hints du repère de plan. Cette option force l'optimiseur à utiliser une opération de recherche d'index pour accéder aux données dans la table HumanResources.Employee. Notez que cela peut contraindre l'optimiseur à utiliser un autre index que celui spécifié dans l'indicateur de table.

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

L'exemple suivant applique l'indicateur INDEX à une table et l'indicateur FORCESEEK à une autre.

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

L'exemple suivant contient deux indicateurs de table dans la requête : NOLOCK, qui affecte la sémantique, et INDEX, qui n'affecte pas la sémantique. Pour préserver la sémantique de la requête, l'indicateur NOLOCK est spécifié dans la clause OPTIONS du repère de plan. Outre l'indicateur NOLOCK, les indicateurs INDEX et FORCESEEK sont spécifiés et remplacent l'indicateur INDEX n'affectant pas la sémantique dans la requête lorsque l'instruction est compilée et optimisée.

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

L'exemple suivant indique une autre méthode pour préserver la sémantique de la requête et permettre à l'optimiseur de choisir un autre index que celui spécifié dans l'indicateur de table. Il suffit de spécifier l'indicateur NOLOCK dans la clause OPTIONS (car il affecte la sémantique) et le mot clé TABLE HINT avec uniquement une référence de table et aucun indicateur INDEX.

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

L'exemple suivant montre comment utiliser TABLE HINT sans spécifier d'indicateur INDEX pour remplacer le comportement de l'indicateur de table INDEX spécifié dans la clause FROM de la requête. Cette méthode permet à l'optimiseur de choisir un autre index que celui spécifié dans l'indicateur de table.

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

Indicateurs de requête (Transact-SQL)

Indicateurs de table (Transact-SQL)

sp_create_plan_guide (Transact-SQL)

sp_control_plan_guide (Transact-SQL)

Concepts

Utilisation de l'indicateur de table FORCESEEK