Indicateur de requête (Transact-SQL)

Mis à jour : 15 septembre 2007

Précise que l'indicateur de requête indiqué doit être utilisé dans toute la requête. L'indicateur de requête agit sur tous les opérateurs de l'instruction. Si une clause UNION se trouve dans la requête principale, seule la dernière requête impliquant une opération UNION peut avoir la clause OPTION. Les indicateurs de requête sont spécifiés dans la clause OPTION. Si un ou plusieurs indicateurs de requête empêchent l'optimiseur de requête de générer un plan valide, l'erreur 8622 est déclenchée.

Important :
L'optimiseur de requête de SQL Server 2005 sélectionne généralement le meilleur plan d'exécution pour une requête. Par conséquent, il est recommandé de n'utiliser les indicateurs, y compris <query_hint>, qu'en dernier recours et ce, par un développeur ou un administrateur de base de données expérimenté.

S'applique à :

DELETE

INSERT

SELECT

UPDATE

Conventions de syntaxe Transact-SQL

Syntaxe

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] ) 
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'
} 

Arguments

• { HASH |ORDER } GROUP
  Indique que les agrégations décrites dans la clause GROUP BY, DISTINCT ou COMPUTE de la requête doivent utiliser le hachage ou le tri.

• {MERGE | HASH | CONCAT} UNION
  Indique que toutes les opérations UNION sont effectuées par fusion, hachage ou concaténation d'ensembles UNION. Si plusieurs options UNION sont spécifiées, l'optimiseur sélectionne la stratégie la moins coûteuse parmi les indicateurs spécifiés.

  Remarque :
  Si un indicateur <join_hint> est également spécifié pour une paire spécifique de tables jointes dans la clause FROM, celui-ci a la priorité sur tout autre indicateur <join_hint> spécifié dans la clause OPTION.

• { LOOP | MERGE | HASH } JOIN
  Spécifie que toutes les opérations de jointure sont effectuées par LOOP JOIN, MERGE JOIN ou HASH JOIN dans toute la requête. Si plusieurs indicateurs de jointure sont spécifiés, l'optimiseur sélectionne la stratégie la moins coûteuse parmi celles qui sont autorisées.

  Si, dans la même requête, un indicateur de jointure est également spécifié pour une paire de tables particulière, il a la priorité sur la jointure des deux tables, même s'il reste à honorer les indicateurs de requête. Ainsi, l'indicateur de jointure de la paire de tables peut seulement restreindre la sélection des méthodes de jointure autorisées dans l'indicateur de requête. Pour plus d'informations, consultez Indicateurs (Transact-SQL).

• FAST number_rows
  Spécifie que la requête est optimisée pour une extraction rapide de la première valeur number_rows.. Il s'agit d'un entier non négatif. Une fois cette première valeur renvoyée, la requête se poursuit afin de générer un jeu de résultats complet.

• FORCE ORDER
  Spécifie que l'ordre de jointure spécifié dans la syntaxe de la requête est conservé au cours de l'optimisation de la requête.

  Remarque :
  L'utilisation de FORCE ORDER n'a aucun effet sur une éventuelle inversion des rôles de la part de l'optimiseur de requête. Pour plus d'informations, consultez Comprendre les jointures de hachage.

  Pour plus d'informations sur la façon dont l'optimiseur de requête de SQL Server applique l'indicateur FORCE ORDER lorsqu'une requête contient une vue, consultez Résolution de vues.

• MAXDOP number
  Remplace l'option de configuration Degré maximal de parallélisme de sp_configure pour la requête qui la spécifie. L'indicateur de requête MAXDOP peut dépasser la valeur configurée avec sp_configure. Toutes les règles sémantiques utilisées avec l'option de configuration Degré maximal de parallélisme sont applicables lorsque vous utilisez l'indicateur de requête MAXDOP. Pour plus d'informations, consultez Option max degree of parallelism.

• OPTIMIZE FOR
  Indique à l'optimiseur de requête d'attribuer à une variable locale une valeur déterminée lors de la compilation et de l'optimisation de la requête. Cette valeur n'est utilisée que pendant l'optimisation de la requête, et non pas lors de son exécution. OPTIMIZE FOR peut contrecarrer le comportement de détection de paramètres de l'optimiseur ou être utilisé lors de la création de repères de plan. Pour plus d'informations, consultez Recompilation de procédures stockées et Optimisation des requêtes dans les applications déployées à l'aide des repères de plan.

• @variable_name
  Nom d'une variable locale utilisée dans une requête, à laquelle une valeur peut être attribuée pour être utilisée avec l'indicateur de requête OPTIMIZE FOR.

• literal_constant
  Valeur de constante littérale à attribuer à @variable_name pour une utilisation avec l'indicateur de requête OPTIMIZE FOR. literal_constant n'est utilisé qu'au moment de l'optimisation de la requête, et non pas en tant que valeur de @variable_name pendant l'exécution de la requête. literal_constant peut être de n'importe quel type de données système SQL Server pouvant être exprimé sous forme d'une constante littérale. Le type de données literal_constant doit être implicitement convertible au type de données que @variable_name référence dans la requête.

• ,…n
  Indique que plusieurs noms de variable (@variable_name) peuvent se voir attribuer une constante littérale (literal_constant) pour une utilisation avec l'indicateur de requête OPTIMIZE FOR.

• PARAMETERIZATION { SIMPLE | FORCED }
  Spécifie les règles de paramétrage que l'optimiseur de requête SQL Server applique à la requête lorsqu'elle est compilée.

  Important :
  L'indicateur de requête PARAMETERIZATION ne peut être spécifié qu'à l'intérieur d'un repère de plan. Il ne peut pas être spécifié directement dans une requête.

  SIMPLE indique à l'optimiseur de requête de tenter le processus Paramétrage simple. FORCED indique à l'optimiseur de requête de tenter le processus Paramétrage forcé. L'indicateur de requête PARAMETERIZATION permet de remplacer le paramétrage actuel de l'option PARAMETERIZATION database SET à l'intérieur d'un repère de plan. Pour plus d'informations, consultez Indication du comportement du paramétrage de requêtes grâce aux repères de plan.

• RECOMPILE
  Indique au moteur de base de données SQL Server 2005 d'ignorer le plan généré pour la requête à l'issue de son exécution, forçant ainsi l'optimiseur de requête à recompiler un plan de requête lors de la prochaine exécution de cette même requête. Si RECOMPILE n'est pas spécifié, le moteur de base de données met en cache les plans de requête et les réutilise. Lors de la compilation des plans de requête, l'indicateur de requête RECOMPILE utilise les valeurs actuelles des variables locales de la requête, qui sont transmises aux paramètres si la requête se trouve à l'intérieur d'une procédure stockée.

  L'indicateur de requête RECOMPILE s'avère fort utile en cela qu'il vous évite de créer une procédure stockée contenant la clause WITH RECOMPILE lorsqu'il s'agit de recompiler uniquement un sous-ensemble de requêtes à l'intérieur de la procédure stockée et non pas l'ensemble de la procédure stockée. Pour plus d'informations, consultez Recompilation de procédures stockées. RECOMPILE s'avère également utile pour créer des repères de guides. Pour plus d'informations, consultez Optimisation des requêtes dans les applications déployées à l'aide des repères de plan.

• ROBUST PLAN
  Force l'optimiseur de requête à essayer un plan capable de prendre en charge la taille maximale potentielle des lignes, éventuellement aux dépens des performances. Lorsque la requête est traitée, les tables et les opérateurs intermédiaires peuvent avoir à stocker et traiter des lignes plus grandes que n'importe quelle ligne d'entrée. Parfois, les lignes peuvent être si grandes que l'opérateur particulier ne peut pas les traiter. Dans ce cas, le moteur de base de données génère une erreur lors de l'exécution de la requête. A l'aide de ROBUST PLAN, vous donnez l'instruction à l'optimiseur de requêtes de ne considérer aucun plan de requête qui pourrait avoir ce problème.

  Si un tel plan n'est pas possible, l'optimiseur de requête retourne une erreur au lieu de reporter la détection de l'erreur au moment de l'exécution de la requête. Les lignes peuvent contenir des colonnes de longueur variable. Le moteur de base de données permet de définir des lignes d'une taille maximale potentielle qu'il n'est pas en mesure de traiter. En règle générale, en dépit de la taille maximale potentielle, une application stocke des lignes dont la taille réelle est comprise dans les limites gérées par le moteur de base de données. Si le moteur de base de données trouve une ligne trop longue, il retourne une erreur d'exécution.

• KEEP PLAN
  Force l'optimiseur de requêtes à abaisser le seuil de recompilation estimé pour une requête. Le seuil de recompilation estimé correspond au point auquel la requête est automatiquement recompilée lorsque le nombre estimé de modifications de colonnes indexées a été apporté à une table en exécutant des instructions UPDATE, DELETE ou INSERT. La spécification de KEEP PLAN garantit qu'une requête ne sera pas recompilée aussi fréquemment que cela lorsque plusieurs mises à jour sont effectuées dans une table.

• KEEPFIXED PLAN
  Force l'optimiseur de requête à ne pas recompiler une requête en raison de modifications enregistrées au niveau des statistiques. KEEPFIXED PLAN garantit qu'une requête est recompilée seulement si le schéma des tables sous-jacentes est modifié ou si la procédure sp_recompile est exécutée sur ces tables.

• EXPAND VIEWS
  Spécifie que les vues indexées sont développées et que l'optimiseur de requêtes ne considèrera pas une vue indexée en tant que substitut d'une partie de la requête. Une vue est développée lorsque son nom est remplacé par sa définition dans le texte de la requête.

  Cet indicateur de requête interdit virtuellement l'utilisation directe de vues indexées et d'index sur des vues indexées dans le plan de requête.

  La vue indexée n'est pas développée seulement si la vue est directement référencée dans la partie SELECT de la requête et si WITH (NOEXPAND) ou WITH (NOEXPAND, INDEX( index_val [ ,...n ] ) ) est spécifié. Pour plus d'informations sur l'indicateur de requête WITH (NOEXPAND), consultez FROM (Transact-SQL).

  Seules les vues dans la partie SELECT des instructions, y compris celles figurant dans les instructions INSERT, UPDATE et DELETE, sont affectées par l'indicateur.

• MAXRECURSION number
  Indique le nombre maximal de récurrences autorisées pour cette requête. number est un entier non négatif compris entre 0 et 32 767. Lorsque 0 est spécifié, aucune limite n'est appliquée. Si cette option n'est pas spécifiée, la limite par défaut du serveur est 100.

  Lorsque la limite par défaut ou spécifiée de MAXRECURSION est atteinte au cours de l'exécution d'une requête, cette requête se termine et une erreur est renvoyée.

  À cause de cette erreur, tous les effets de l'instruction sont annulés. S'il s'agit d'une instruction SELECT, les résultats renvoyés sont partiels ou aucun résultat n'est renvoyé. Il se peut que parmi les résultats partiels éventuellement renvoyés ne figurent pas toutes les lignes des niveaux de récursivité supérieurs au niveau de récursivité maximal spécifié.

  Pour plus d'informations, consultez WITH common_table_expression (Transact-SQL).

• USE PLAN N**'xml_plan'**
  Force l'optimiseur de requête à utiliser un plan de requête existant pour une requête spécifiée par 'xml_plan'. Pour plus d'informations, consultez Définition de plans de requêtes à l'aide de l'application forcée d'un plan. USE PLAN ne peut pas être spécifié avec des instructions INSERT, UPDATE ou DELETE.

Notes

Il n'est pas possible de spécifier des indicateurs de requête dans une instruction INSERT sauf si celle-ci contient une clause SELECT.

Les indicateurs de requête ne peuvent être spécifiés que dans une requête de niveau supérieur et non pas dans des sous-requêtes.

Exemples

A. Utilisation de MERGE JOIN

Dans l'exemple suivant, l'opération JOIN spécifiée dans la requête est exécutée par MERGE JOIN.

USE AdventureWorks;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca
ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

B. Utilisation de OPTIMIZE FOR

Dans l'exemple suivant, l'optimiseur de requête a pour instruction d'utiliser la valeur 'Seattle' pour la variable locale @city_name lors de l'optimisation de la requête.

DECLARE @city_name nvarchar(30)
SET @city_name = 'Ascheim'
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle') );
GO

C. Utilisation de MAXRECURSION

MAXRECURSION peut être utilisé pour empêcher une expression de table commune récursive mal rédigée d'entrer dans une boucle infinie. L'exemple suivant créée intentionnellement une boucle infinie et utilise l'indicateur MAXRECURSION pour limiter le nombre de niveaux de récursivité à deux.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Une fois que l'erreur de codage est corrigée, MAXRECURSION n'est plus nécessaire.

D. Utilisation de UNION

L'exemple suivant utilise l'indicateur de requête MERGE UNION.

USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO

E. Utilisation de HASH GROUP et de FAST

L'exemple suivant utilise les indicateurs de requête HASH GROUP et FAST.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10) ;
GO

F. Utilisation de MAXDOP

L'exemple suivant utilise l'indicateur de requête MAXDOP.

USE AdventureWorks ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

Voir aussi

Référence

Indicateurs (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications