Indicateurs de requête (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 { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'| TABLE HINT ( exposed_object_name  [ , <table_hint> [ [, ]...n ] ] )<table_hint> ::=
[ NOEXPAND ] { 
    INDEX (index_value [ ,...n ] ) | INDEX = (index_value)
  | FASTFIRSTROW 
  | FORCESEEK
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

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 indicateurs UNION sont spécifiées, l'optimiseur sélectionne la stratégie la moins coûteuse parmi les indicateurs spécifiés.

• { 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é dans la clause FROM 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 de jointure (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 number_rows retourné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. 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.

  [!REMARQUE]

  Dans une instruction MERGE, il convient d'accéder à la table source avant la table cible comme ordre de jointure par défaut, à moins que la clause WHEN SOURCE NOT MATCHED ne soit spécifiée. La spécification de FORCE ORDER préserve ce comportement par défaut.

  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 max degree of parallelism de sp_configure et le gouverneur de ressources pour la requête qui la spécifie. L'indicateur de requête MAXDOP peut dépasser la valeur configurée avec sp_configure. Si MAXDOP dépasse la valeur configurée avec le gouverneur de ressources, le moteur de base de données utilise la valeur MAXDOP du gouverneur de ressources, décrite dans ALTER WORKLOAD GROUP (Transact-SQL). Toutes les règles sémantiques utilisées avec l'option de configuration max degree of parallelism sont applicables lorsque vous utilisez l'indicateur de requête MAXDOP. Pour plus d'informations, consultez Option Degré maximum de parallélisme.

  Attention
  Si MAXDOP est défini avec la valeur zéro, le serveur choisit le degré maximal de parallélisme. Le serveur limite DOP à 64 en interne.

• OPTIMIZE FOR ( @variable\_name { UNKNOWN | = literal_constant } [ , ...n ] )
  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.

  • @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.

  • UNKNOWN
    Spécifie que l'optimiseur de requête utilise des données statistiques à la place de la valeur initiale pour déterminer la valeur d'une variable locale pendant l'optimisation de requête.

  • 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.

  OPTIMIZE FOR peut contrecarrer le comportement de détection de paramètres par défaut 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.

• OPTIMIZE FOR UNKNOWN
  Indique à l'optimiseur de requête d'utiliser des données statistiques au lieu des valeurs initiales pour toutes les variables locales lorsque la requête est compilée et optimisée, y compris les paramètres créés avec un paramétrage forcé. Pour plus d'informations sur le paramétrage forcé, consultez Paramétrage forcé.

  Si OPTIMIZE FOR @variable\_name = literal_constant et OPTIMIZE FOR UNKNOWN sont utilisés dans le même indicateur de requête, l'optimiseur de requête utilise le literal_constant spécifié pour une valeur spécifique et UNKNOWN pour les valeurs variables restantes. Les valeurs ne sont utilisées que pendant l'optimisation de la requête, et non pas lors de son exécution.

• 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 de paramétrage simple. FORCED indique à l'optimiseur de tenter le processus de 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 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. À l'aide de ROBUST PLAN, vous indiquez à l'optimiseur de requête 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ête à 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, MERGE ou INSERT. La spécification de KEEP PLAN permet de garantir qu'une requête n'est pas recompilée aussi fréquemment 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. La spécification de KEEPFIXED PLAN permet de garantir 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ête ne considère 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_value [ ,...n ] ) ) est spécifié. Pour plus d'informations sur l'indicateur de requête WITH (NOEXPAND), consultez FROM.

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

• MAXRECURSION number
  Spécifie le nombre maximal de récursivités autorisé 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 retourné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 retournés sont partiels ou aucun résultat n'est retourné. Il se peut que parmi les résultats partiels éventuellement retourné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, MERGE ou DELETE.

• TABLE HINT (exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
  Applique l'indicateur de table spécifié à la table ou vue qui correspond à exposed_object_name. Nous recommandons d'utiliser un indicateur de table comme indicateur de requête uniquement dans le contexte d'un repère de plan.

  exposed_object_name peut être l'une des références suivantes :

  • Lorsqu'un alias est utilisé pour la table ou la vue dans la clause FROM de la requête, exposed_object_name est l'alias.

  • Lorsqu'aucun alias n'est utilisé, exposed_object_name est la correspondance exacte de la table ou vue référencée dans la clause FROM. Par exemple, si la table ou la vue est référencée à l'aide d'un nom en deux parties, exposed_object_name est le même nom en deux parties.

  Lorsque exposed_object_name est spécifié sans préciser également un indicateur de table, tous les index spécifiés dans la requête dans le cadre d'un indicateur de table pour l'objet sont ignorés et l'utilisation des index est déterminée par l'optimiseur de requête. Vous pouvez utiliser cette technique pour éliminer l'effet d'un indicateur de table INDEX lorsque vous ne pouvez pas modifier la requête d'origine. Voir l'exemple J.

• <table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FASTFIRSTROW | FORCESEEK | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
  Indicateur de table à appliquer à la table ou vue qui correspond à exposed_object_name en tant qu'indicateur de requête. Pour obtenir une description de ces indicateurs, consultez Indicateurs de table (Transact-SQL).

  Les indicateurs de table autres que INDEX et FORCESEEK sont interdits comme indicateurs de requête, à moins que la requête n'ait déjà une clause WITH qui spécifie l'indicateur de table. Pour plus d'informations, consultez la section « Remarques ».

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. Lorsqu'un indicateur de table est spécifié comme indicateur de requête, l'indicateur peut être spécifié dans la requête de niveau supérieur ou dans une sous-requête ; toutefois, la valeur spécifiée pour exposed_object_name dans la clause TABLE HINT doit correspondre exactement au nom exposé dans la requête ou sous-requête.

Spécification d'indicateurs de table comme indicateurs de requête

Exemples

A. Utilisation de 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 d'OPTIMIZE FOR

USE AdventureWorks;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

C. Utilisation de MAXRECURSION

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

D. Utilisation de MERGE UNION

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

E. Utilisation de HASH GROUP et de 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

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

G. Utilisation d'INDEX

USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              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, INDEX (IX_Employee_ManagerID)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              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, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';
GO

H. 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

I. 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

J. 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

K. Spécification d'indicateurs de table 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

Voir aussi

Référence

Concepts