Indicateurs (Transact-SQL) - Requête

S’applique à : ouiSQL Server (toutes les versions prises en charge) OuiAzure SQL Database

Les indicateurs de requête spécifient que les indicateurs mentionnés sont utilisés dans l’étendue d’une requête. Ils s’appliquent à 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. L’erreur 8622 se produit si un ou plusieurs indicateurs de requête empêchent l’optimiseur de requête de générer un plan valide.

Attention

Comme l’optimiseur de requête SQL Server sélectionne généralement le meilleur plan d’exécution pour une requête, nous recommandons de ne recourir aux indicateurs qu’en dernier ressort, et à condition d’être un développeur ou un administrateur de base de données expérimenté.

S’applique à :

DELETE

INSERT

SELECT

UPDATE

MERGE

Syntaxe

<query_hint> ::=   
{ { HASH | ORDER } GROUP   
  | { CONCAT | HASH | MERGE } UNION   
  | { LOOP | MERGE | HASH } JOIN   
  | EXPAND VIEWS   
  | FAST <integer_value>   
  | FORCE ORDER   
  | { FORCE | DISABLE } EXTERNALPUSHDOWN
  | { FORCE | DISABLE } SCALEOUTEXECUTION
  | IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX  
  | KEEP PLAN   
  | KEEPFIXED PLAN  
  | MAX_GRANT_PERCENT = <numeric_value>  
  | MIN_GRANT_PERCENT = <numeric_value>  
  | MAXDOP <integer_value>   
  | MAXRECURSION <integer_value>   
  | NO_PERFORMANCE_SPOOL   
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = <literal_constant> } [ , ...n ] )  
  | OPTIMIZE FOR UNKNOWN  
  | PARAMETERIZATION { SIMPLE | FORCED }   
  | QUERYTRACEON <integer_value>   
  | RECOMPILE  
  | ROBUST PLAN   
  | USE HINT ( <use_hint_name> [ , ...n ] )
  | USE PLAN N'<xml_plan>'  
  | TABLE HINT ( <exposed_object_name> [ , <table_hint> [ [, ]...n ] ] )  
}  
  
<table_hint> ::=  
{ NOEXPAND [ , INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) ]  
  | INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> )
  | FORCESEEK [ ( <index_value> ( <index_column_name> [,... ] ) ) ]  
  | FORCESCAN  
  | HOLDLOCK   
  | NOLOCK   
  | NOWAIT  
  | PAGLOCK   
  | READCOMMITTED   
  | READCOMMITTEDLOCK   
  | READPAST   
  | READUNCOMMITTED   
  | REPEATABLEREAD   
  | ROWLOCK   
  | SERIALIZABLE   
  | SNAPSHOT  
  | SPATIAL_WINDOW_MAX_CELLS = <integer_value>  
  | TABLOCK   
  | TABLOCKX   
  | UPDLOCK   
  | XLOCK  
}  

<use_hint_name> ::=
{ 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
  | 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
  | 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
  | 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_DEFERRED_COMPILATION_TV'
  | 'DISABLE_INTERLEAVED_EXECUTION_TVF'
  | 'DISABLE_OPTIMIZED_NESTED_LOOP'
  | 'DISABLE_OPTIMIZER_ROWGOAL'
  | 'DISABLE_PARAMETER_SNIFFING'
  | 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
  | 'DISABLE_TSQL_SCALAR_UDF_INLINING'
  | 'DISALLOW_BATCH_MODE'
  | 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
  | 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
  | 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
  | 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
  | 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
  | 'QUERY_PLAN_PROFILE' 
}

Notes

Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 et versions antérieures, consultez Versions antérieures de la documentation.

Arguments

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

{ MERGE | HASH | CONCAT } UNION
Indique que toutes les opérations UNION doivent être exécutées par fusion, hachage ou concaténation d'ensembles UNION. Si plusieurs indicateurs UNION sont spécifiés, l’optimiseur de requête sélectionne la stratégie la moins coûteuse parmi les indicateurs spécifiés.

{ LOOP | MERGE | HASH } JOIN
Indique que toutes les opérations de jointure doivent être 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 vous spécifiez un indicateur de jointure dans la clause FROM de la même requête pour une paire de tables spécifique, il est prioritaire dans la jointure des deux tables. Les indicateurs de requête, toutefois, doivent quand même être respectés. 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).

EXPAND VIEWS
Indique que les vues indexées doivent être développées, et que l’optimiseur de requête ne doit pas prendre en compte les vues indexées en remplacement d’une partie de la requête. Une vue est développée lorsque sa définition remplace son nom 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.

Notes

La vue indexée reste condensée si une référence directe y est faite dans la partie SELECT de la requête, ou si WITH (NOEXPAND) ou WITH (NOEXPAND, INDEX( <index_value> [ , ...n ] ) ) est spécifié. Pour plus d’informations sur l’indicateur de requête NOEXPAND, consultez Utilisation de NOEXPAND.

L'indicateur n’a d’incidence que sur les vues de la partie SELECT des instructions, y compris celles figurant dans des instructions INSERT, UPDATE, MERGE et DELETE.

FAST <integer_value>
Spécifie que la requête est optimisée pour la récupération rapide des <integer_value> premières lignes. Ce résultat est un entier non négatif. Une fois que les premières lignes définies par <integer_value ont été retournées, la requête se poursuit pour retourner 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. FORCE ORDER n’a aucun effet sur un éventuel comportement d’inversion des rôles de la part de l’optimiseur de requête.

Notes

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.

{ FORCE | DISABLE } EXTERNALPUSHDOWN
Force ou désactive la poussée vers le bas (pushdown) du calcul des expressions éligibles dans Hadoop. S’applique uniquement aux requêtes avec PolyBase. Ne s’applique pas au stockage Azure.

{FORCE | DISABLE} SCALEOUTEXECUTION Force ou désactive l’exécution d’un scale-out des requêtes PolyBase qui utilisent des tables externes dans les Clusters Big Data SQL Server 2019. Cet indicateur n’est respecté que par une requête utilisant l’instance principale d’un cluster Big Data SQL. Le scale-out se produit dans le pool de calcul du cluster Big Data.

KEEP PLAN
Force l’optimiseur de requête à abaisser le seuil de recompilation estimé pour une requête. Le seuil de recompilation estimé lance une recompilation automatique de la requête lorsque le nombre estimé de modifications de colonnes indexées a été apporté à une table par exécution de l’une des instructions suivantes :

  • UPDATE
  • Suppression
  • MERGE
  • INSERT

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. KEEPFIXED PLAN permet de garantir qu’une requête n’est recompilée que si le schéma des tables sous-jacentes est modifié ou si sp_recompile s’exécute sur ces tables.

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
S’applique à : SQL Server (à compter de SQL Server 2012 (11.x)).

Empêche la requête d’utiliser un index columnstore non-cluster à mémoire optimisée. Si la requête contient l’indicateur de requête pour éviter l’utilisation de l’index columnstore et un indicateur d’index pour utiliser un index columnstore, les indicateurs sont en conflit et la requête retourne une erreur.

MAX_GRANT_PERCENT = <numeric_value>
S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

Taille maximale d’allocation de mémoire en pourcentage de la limite de mémoire configurée. La requête ne peut pas dépasser cette limite. La limite réelle peut être inférieure si le paramètre de Resource Governor est inférieur à la valeur spécifiée par cet indicateur. Les valeurs valides sont comprises entre 0,0 et 100,0.

MIN_GRANT_PERCENT = <numeric_value>
S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

Taille minimale d’allocation de mémoire en pourcentage de la limite de mémoire configurée. La requête est assurée d’avoir au moins MAX(required memory, min grant), car la mémoire nécessaire est le minimum requis pour pouvoir démarrer une requête. Les valeurs valides sont comprises entre 0,0 et 100,0.

MAXDOP <integer_value>
S’applique à : SQL Server (à compter de SQL Server 2008) et Azure SQL Database.

Remplace l’option de configuration Degré maximal de parallélisme de sp_configure. Remplace également Resource Governor pour la requête spécifiant cette option. 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 Resource Governor, le Moteur de base de données utilise la valeur MAXDOP de Resource Governor, 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 quand vous utilisez l’indicateur de requête MAXDOP. Pour plus d’informations, consultez Configurer l’option de configuration du serveur max degree of parallelism.

Avertissement

Si MAXDOP a la valeur zéro, le serveur choisit le degré maximal de parallélisme.

MAXRECURSION <integer_value>
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 en retournant une erreur.

À 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).

NO_PERFORMANCE_SPOOL
S’applique à : SQL Server (à compter de SQL Server 2016 (13.x)) et Azure SQL Database.

Empêche l’ajout d’un opérateur de spool aux plans de requête (à l’exception des plans où un spool est nécessaire pour garantir la validité de la sémantique de mise à jour). L’opérateur de spool est susceptible de diminuer les performances dans certains scénarios. Par exemple, du fait que le spool utilise tempdb, une contention de tempdb peut se produire quand un grand nombre de requêtes simultanées sont exécutées avec les opérations de spool.

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.

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

constante_littérale
Valeur de constante littérale à assigner à @nom_variable pour l’utiliser avec l’indicateur de requête OPTIMIZE FOR. constante_littérale n’est utilisé que pendant l’optimisation de la requête, et non comme valeur de @nom_variable lors de l’exécution de la requête. constante_littérale peut être de n’importe quel type de données système SQL Server qui peut être exprimé sous forme de constante littérale. Le type de données de constante_littérale doit être implicitement convertible dans le type de données auquel @nom_variable fait référence dans la requête.

OPTIMIZE FOR peut contrecarrer le comportement de détection des paramètres par défaut de l’optimiseur. Utilisez également OPTIMIZE FOR pour créer des repères de plan. Pour plus d’informations, consultez Recompiler une procédure stockée.

OPTIMIZE FOR UNKNOWN
Indique à l’optimiseur de requête d’utiliser la sélectivité moyenne du prédicat sur toutes les valeurs de colonne au lieu d’utiliser la valeur du paramètre de runtime lorsque la requête est compilée et optimisée.

Si vous utilisez OPTIMIZE FOR @variable_name = constante_littérale et OPTIMIZE FOR UNKNOWN dans le même indicateur de requête, l’optimiseur de requête utilise la constante_littérale spécifiée pour une valeur spécifique, et UNKNOWN pour les autres valeurs des variables. 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 doit appliquer à la requête lors de sa compilation.

Important

L’indicateur de requête PARAMETERIZATION peut uniquement être spécifié à l’intérieur d’un repère de plan pour remplacer le paramètre actuel de l’option SET de base de données PARAMETERIZATION. Il n’est pas possible de le spécifier directement dans une requête.
Pour plus d’informations, consultez Spécifier le comportement du paramétrage de requêtes grâce aux repères de plan.

SIMPLE indique à l’optimiseur de requête de tenter un paramétrage simple. FORCED indique à l’optimiseur de requête de tenter un paramétrage forcé. Pour plus d’informations, consultez Paramétrage forcé dans le Guide d’architecture de traitement des requêtes et Paramétrage simple dans le Guide d’architecture de traitement des requêtes.

QUERYTRACEON <integer_value>
Cette option vous permet d’activer un indicateur de trace affectant le plan uniquement pendant la compilation d’une requête unique. Comme d’autres options de niveau requête, vous pouvez l’utiliser avec des repères de plan pour faire correspondre le texte d’une requête en cours d’exécution à partir de n’importe quelle session, et appliquer automatiquement un indicateur de trace affectant le plan lorsque cette requête est en cours de compilation. L’option QUERYTRACEON est prise en charge seulement pour les indicateurs de trace de l’optimiseur de requête. Pour plus d’informations, consultez l’article Indicateurs de trace.

Notes

L’utilisation de cette option ne retourne pas d’erreur ni d’avertissement si un numéro d’indicateur de trace non pris en charge est utilisé. Si l’indicateur de trace spécifié n’est pas celui qui affecte un plan d’exécution de requête, l’option est ignorée en mode silencieux.

Notes

Pour utiliser plusieurs indicateurs de trace dans une requête, spécifiez un indicateur QUERYTRACEON pour chaque numéro d’indicateur de trace.

RECOMPILE
Envoie à Moteur de base de données SQL Server l’instruction de générer un nouveau plan temporaire pour la requête et de l’abandonner juste après la fin d’exécution de la requête. Le plan de requête généré ne remplace pas un plan stocké en cache lorsque la même requête s’exécute sans l’indicateur RECOMPILE. 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 de plans de requête, l’indicateur de requête RECOMPILE utilise les valeurs actuelles des éventuelles variables locales dans la requête. Si la requête se trouve à l’intérieur d’une procédure stockée, les valeurs actuelles sont transmises aux paramètres.

RECOMPILE est utile pour ne pas avoir à créer une procédure stockée. RECOMPILE utilise la clause WITH RECOMPILE lorsqu'il s'agit seulement de recompiler un sous-ensemble de requêtes dans la procédure stockée, et non la totalité de celle-ci. Pour plus d’informations, consultez Recompiler une procédure stockée. RECOMPILE s'avère également utile pour créer des repères de guides.

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. Lors du traitement de la requête, 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. Elles sont parfois 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. ROBUST PLAN permet d’indiquer à l’optimiseur de requête de ne considérer aucun plan de requête susceptible de présenter ce problème.

Si un tel plan n’est pas possible, l’optimiseur de requête retourne une erreur plutôt que de différer 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 que le Moteur de base de données 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 Moteur de base de données rencontre une ligne trop longue, il retourne une erreur d'exécution.

USE HINT ( ' nom_indicateur ' )
S’applique à : SQL Server (depuis SQL Server 2016 (13.x) SP1) et Azure SQL Database.

Fournit au processeur de requêtes un ou plusieurs indicateurs supplémentaires, spécifiés par un nom entre guillemets simples.

Les noms d’indicateur suivants sont pris en charge :

  • 'ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'
    Indique à SQL Server de générer un plan de requête qui utilise l’hypothèse de relation contenant-contenu simple, au lieu de l’hypothèse par défaut de relation contenant-contenu de base pour les jointures, avec le modèle d’estimation de la cardinalité de l’optimiseur de requête fourni dans SQL Server 2014 (12.x) ou ultérieur. Ce nom d’indicateur équivaut à l’indicateur de trace 9476.

  • 'ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'
    Indique à SQL Server de générer un plan qui utilise la sélectivité minimale lors de l’évaluation des prédicats AND des filtres pour la prise en compte de la corrélation. Ce nom d’indicateur équivaut à l’indicateur de trace 4137 avec le modèle d’estimation de la cardinalité fourni dans SQL Server 2012 (11.x) et les versions antérieures, ou à l’indicateur de trace 9471 avec le modèle d’estimation de la cardinalité disponible dans SQL Server 2014 (12.x) ou versions ultérieures.

  • 'DISABLE_BATCH_MODE_ADAPTIVE_JOINS'
    Désactive les jointures adaptatives en mode batch. Pour plus d’informations, consultez Jointures adaptatives en mode batch.
    S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

  • 'DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'
    Désactive les retours d’allocation de mémoire en mode batch. Pour plus d’informations, consultez Retour d’allocation de mémoire en mode batch.
    S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

  • 'DISABLE_DEFERRED_COMPILATION_TV'
    Désactive la compilation différée de variable de table. Pour plus d'informations, consultez Compilation différée de variable de table.
    S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)) et Azure SQL Database.

  • 'DISABLE_INTERLEAVED_EXECUTION_TVF'
    Désactive l’exécution entrelacée pour les fonctions table à instructions multiples. Pour plus d’informations, voir Exécution entrelacée pour les fonctions table à instructions multiples.
    S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)) et Azure SQL Database.

  • 'DISABLE_OPTIMIZED_NESTED_LOOP'
    Indique au processeur de requêtes de ne pas appliquer d’opération de tri (tri par lots) sur les jointures de boucles imbriquées optimisées au moment de la génération d’un plan de requête. Ce nom d’indicateur équivaut à l’indicateur de trace 2340.

  • 'DISABLE_OPTIMIZER_ROWGOAL'
    Indique à SQL Server de générer un plan qui n’utilise pas les modifications de l’objectif des lignes avec des requêtes contenant ces mots clés :

    • Haut de la page
    • OPTION (FAST N)
    • IN
    • EXISTS

    Ce nom d’indicateur équivaut à l’indicateur de trace 4138.

  • 'DISABLE_PARAMETER_SNIFFING'
    Indique à l’optimiseur de requête d’utiliser la distribution moyenne des données lors de la compilation d’une requête comportant un ou plusieurs paramètres. Cette instruction rend le plan de requête indépendant de la valeur du paramètre utilisée initialement lors de la compilation de la requête. Ce nom d’indicateur équivaut à l’indicateur de trace 4136 ou au paramètre de configuration au niveau base de donnéesPARAMETER_SNIFFING = OFF.

  • 'DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'
    Désactive la rétroaction d’allocation de mémoire en mode ligne. Pour plus d’informations, consultez Rétroaction d’allocation de mémoire en mode ligne.
    S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)) et Azure SQL Database.

  • 'DISABLE_TSQL_SCALAR_UDF_INLINING'
    Désactive l’incorporation des fonctions UDF scalaires. Pour plus d’informations, consultez Incorporation des fonctions UDF scalaires.
    S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)).

  • 'DISALLOW_BATCH_MODE'
    Désactive l’exécution en mode batch. Pour plus d’informations, consultez Modes d’exécution.
    S’applique à : SQL Server (à compter de SQL Server 2019 (15.x)) et Azure SQL Database.

  • 'ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS'
    Active automatiquement la génération de statistiques rapides (modification de l’histogramme) pour les colonnes d’index de début où l’estimation de la cardinalité est nécessaire. L’histogramme utilisé pour estimer la cardinalité est ajusté au moment de la compilation des requêtes pour prendre en compte la valeur minimale ou maximale réelle de chaque colonne. Ce nom d’indicateur équivaut à l’indicateur de trace 4139.

  • 'ENABLE_QUERY_OPTIMIZER_HOTFIXES'
    Active les correctifs de l’optimiseur de requête (modifications publiées dans les Service Packs et mises à jour cumulatives de SQL Server). Ce nom d’indicateur équivaut à l’indicateur de trace 4199 ou au paramètre de configuration au niveau base de donnéesQUERY_OPTIMIZER_HOTFIXES = ON.

  • 'FORCE_DEFAULT_CARDINALITY_ESTIMATION'
    Force l’optimiseur de requête à utiliser le modèle d’estimation de la cardinalité qui correspond au niveau de compatibilité de la base de données. Cet indicateur remplace le paramètre de configuration au niveau base de donnéesLEGACY_CARDINALITY_ESTIMATION = ON ou l’indicateur de trace 9481.

  • 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
    Force l’optimiseur de requête à utiliser le modèle Estimation de cardinalité fourni dans SQL Server 2012 (11.x) et les versions antérieures. Ce nom d’indicateur équivaut à l’indicateur de trace 9481 ou au paramètre de configuration au niveau base de donnéesLEGACY_CARDINALITY_ESTIMATION = ON.

  • 'QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n'
    Force le comportement de l’optimiseur de requête au niveau de la requête, comme si celle-ci était compilée avec le niveau de compatibilité de la base de données n, où n est un niveau pris en charge (par exemple 100, 130, etc.). Consultez sys.dm_exec_valid_use_hints pour obtenir la liste des valeurs actuellement prises en charge pour n.
    S’applique à : SQL Server (à partir de SQL Server 2017 (14.x) CU10).

    Notes

    L’indicateur QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n ne remplace pas le paramètre d’estimation de la cardinalité hérité ou par défaut, s’il est forcé par le biais de la configuration de portée de base de données, l’indicateur de trace ou un autre indicateur de requête comme QUERYTRACEON.
    Cet indicateur affecte uniquement le comportement de l’optimiseur de requête. Il n’a aucun effet sur les autres fonctionnalités de SQL Server susceptibles de dépendre du niveau de compatibilité de la base de données, comme la disponibilité de certaines fonctionnalités de la base de données.
    Pour en savoir plus sur cet indicateur, consultez Developer’s Choice: Hinting Query Execution model.

  • 'QUERY_PLAN_PROFILE'
    Permet un profilage léger pour la requête. À la fin d’une requête contenant ce nouvel indicateur, un nouvel événement étendu, query_plan_profile, est déclenché. Cet événement étendu expose les statistiques d’exécution et le plan d’exécution réel XML semblable à l’événement étendu query_post_execution_showplan, mais uniquement pour les requêtes qui contiennent le nouvel indicateur.
    S’applique à : SQL Server (à partir de SQL Server 2016 (13.x) SP2CU3 et SQL Server 2017 (14.x) CU11).

    Notes

    Si vous activez la collecte de l’événement étendu query_post_execution_showplan, cette opération ajoutera l’infrastructure de profilage standard à chaque requête qui est en cours d’exécution sur le serveur et pourra, par conséquent, affecter les performances globales du serveur.
    Si vous activez la collection de l’événement étendu query_thread_profile pour utiliser l’infrastructure à profilage léger à la place, la performance sera beaucoup plus faible, mais les performances globales du serveur seront quand même affectées.
    Si vous activez l’événement étendu query_plan_profile, cela activera uniquement l’infrastructure de profilage léger pour une requête exécutée avec le QUERY_PLAN_PROFILE et, par conséquent, n’affectera pas d’autres charges de travail sur le serveur. Utilisez cet indicateur pour profiler une requête spécifique sans affecter d’autres parties de la charge de travail du serveur. Pour en savoir plus sur le profilage léger, consultez Infrastructure du profilage de requête.

Vous pouvez obtenir la liste de tous les noms d’indicateur USE HINT pris en charge en effectuant une requête sur la vue de gestion dynamique sys.dm_exec_valid_use_hints.

Conseil

Les noms d’indicateur respectent la casse.

Important

Certains indicateurs USE HINT peuvent être en conflit avec des indicateurs de trace activés au niveau global ou session, ou avec des paramètres de configuration au niveau base de données. Dans ce cas, l’indicateur de niveau requête (USE HINT) est toujours prioritaire. En présence d’un conflit entre l’indicateur USE HINT et un autre indicateur de requête ou un indicateur de trace activé au niveau requête (par exemple, par QUERYTRACEON), SQL Server génère une erreur quand vous tentez d’exécuter la requête.

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 . USE PLAN ne peut pas être spécifié avec les instructions INSERT, UPDATE, MERGE ou DELETE.

TABLE HINT ( <exposed_object_name> [ , <table_hint> [ [ , ]...n ] ] ) Applique l’indicateur de table spécifié à la table ou à la vue correspondant à exposed_object_name. Nous vous 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 :

  • Quand un alias est utilisé pour la table ou la vue dans la clause FROM de la requête, nom_objet_exposé est cet alias.

  • Si aucun alias n’est utilisé, nom_objet_exposé est la correspondance à l’exact de la table ou de la vue à laquelle il est fait référence dans la clause FROM. Par exemple, si la référence à la table ou à la vue consiste en un nom en deux parties, nom_objet_exposé correspond au même nom en deux parties.

Si nom_objet_exposé est spécifié sans indicateur de table, tous les index indiqués dans la requête dans le cadre d’un indicateur de table de l’objet sont ignorés. L’optimiseur de requête détermine ensuite l’utilisation de l’index. 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> ) ] | INDEX ( <index_value> [ ,...n ] ) | INDEX = ( <index_value> ) | FORCESEEK [ ( <index_value> ( <index_column_name> [ , ... ] )) ] | FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | SNAPSHOT | SPATIAL_WINDOW_MAX_CELLS = <integer_value> | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }
Indicateur de table à appliquer à la table ou la 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, FORCESCAN 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.

Attention

Le fait de spécifier FORCESEEK avec des paramètres limite davantage le nombre de plans qui peuvent être considérés par l'optimiseur de requête que le fait de spécifier FORCESEEK sans paramètres. Cela peut provoquer une erreur « Impossible de générer le plan » dans davantage de cas. Dans une version ultérieure, il se peut que des modifications internes de l’optimiseur de requête autorisent la prise en considération de davantage de plans.

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, il peut se trouver dans la requête de premier niveau ou dans une sous-requête. Toutefois, la valeur spécifiée pour <exposed_object_nem> dans la clause TABLE HINT doit correspondre exactement au nom exposé dans la requête ou la sous-requête.

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

Nous vous recommandons d’utiliser l’indicateur de table INDEX, FORCESCAN ou FORCESEEK comme indicateur de requête uniquement dans le contexte d’un repère de plan. Les repères de plan sont utiles lorsqu’il n’est pas possible de modifier la requête d'origine, par exemple s’il s'agit d'une application tierce. L'indicateur de requête spécifié dans le repère de plan est ajouté à la requête avant sa compilation et son optimisation. Pour les requêtes ad hoc, utilisez la clause TABLE HINT uniquement lors du test des instructions de repère de plan. Pour toutes les autres requêtes ad hoc, nous recommandons de spécifier ces indicateurs uniquement comme indicateurs de table.

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

  • Tables
  • Les 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

Il est possible de spécifier des indicateurs de table INDEX, FORCESCAN et FORCESEEK comme indicateurs de requête pour une requête ne disposant pas d’indicateurs de table. Vous pouvez également les utiliser pour remplacer respectivement des indicateurs INDEX, FORCESCAN et FORCESEEK existants dans la requête.

Les indicateurs de table autres que INDEX, FORCESCAN 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. Dans ce cas, il faut également spécifier un indicateur correspondant comme indicateur de requête. Utilisez pour cela TABLE HINT dans la clause OPTION. Cette spécification préserve la sémantique de la requête. Par exemple, si la requête contient l’indicateur de table NOLOCK, la clause OPTION dans le paramètre @hints du repère de plan doit également contenir l’indicateur NOLOCK. Voir l'exemple K.

Exemples

R. Utilisation de MERGE JOIN

L’exemple suivant spécifie que MERGE JOIN exécute l’opération JOIN dans la requête. L'exemple utilise la base de données AdventureWorks2012.

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

L’exemple suivant indique à l’optimiseur de requête d’utiliser la valeur 'Seattle' pour @city_name et d’utiliser la sélectivité moyenne du prédicat sur toutes les valeurs de colonne pour @postal_code lors de l’optimisation de la requête. L'exemple utilise la base de données AdventureWorks2012.

CREATE PROCEDURE dbo.RetrievePersonAddress
@city_name NVARCHAR(30),  
 @postal_code NVARCHAR(15)
AS
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

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. L'exemple utilise la base de données AdventureWorks2012.

--Creates an infinite loop  
WITH cte (CustomerID, PersonID, StoreID) AS  
(  
    SELECT CustomerID, PersonID, StoreID  
    FROM Sales.Customer  
    WHERE PersonID IS NOT NULL  
  UNION ALL  
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID  
    FROM cte   
    JOIN  Sales.Customer AS e   
        ON cte.PersonID = e.CustomerID  
)  
--Uses MAXRECURSION to limit the recursive levels to 2  
SELECT CustomerID, PersonID, StoreID  
FROM cte  
OPTION (MAXRECURSION 2);  
GO  

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

D. Utilisation de MERGE UNION

L’exemple suivant utilise l’indicateur de requête MERGE UNION. L'exemple utilise la base de données AdventureWorks2012.

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

L’exemple suivant utilise les indicateurs de requête HASH GROUP et FAST. L'exemple utilise la base de données AdventureWorks2012.

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. L'exemple utilise la base de données AdventureWorks2012.

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 de INDEX

Les exemples suivants utilisent l'indicateur INDEX. Le premier exemple spécifie un index unique. Le deuxième exemple spécifie plusieurs index pour une référence de table individuelle. Dans les deux exemples, étant donné que l'indicateur INDEX est appliqué à une table qui utilise un alias, la clause TABLE HINT doit également spécifier le même alias que le nom d'objet exposé. L'exemple utilise la base de données AdventureWorks2012.

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

L'exemple suivant utilise l'indicateur de table FORCESEEK. La clause TABLE HINT doit également spécifier le même nom en deux parties que le nom d’objet exposé. Indiquez ce nom lorsque vous appliquez l’indicateur INDEX à une table qui utilise un nom en deux parties. L'exemple utilise la base de données AdventureWorks2012.

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

L'exemple suivant applique l'indicateur INDEX à une table et l'indicateur FORCESEEK à une autre. L'exemple utilise la base de données AdventureWorks2012.

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 substituer un indicateur de table existant

L’exemple suivant montre comment se servir de l’indicateur TABLE HINT. Vous pouvez l’utiliser sans spécifier d’indicateur pour remplacer le comportement de l’indicateur de table INDEX spécifié dans la clause FROM de la requête. L'exemple utilise la base de données AdventureWorks2012.

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

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. En parallèle de l'indicateur NOLOCK, spécifiez les indicateurs INDEX et FORCESEEK pour remplacer l'indicateur INDEX sans effet sur la sémantique dans la requête lors de la compilation et de l’optimisation de l'instruction. L'exemple utilise la base de données AdventureWorks2012.

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 index autre que l'index spécifié dans l'indicateur de table. Autorisez l’optimiseur à choisir en spécifiant l’indicateur NOLOCK dans la clause OPTIONS. En effet, cet indicateur affecte la sémantique. Ensuite, indiquez le mot clé TABLE HINT avec seulement une référence de table, sans indicateur INDEX. L'exemple utilise la base de données AdventureWorks2012.

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  

L. Utilisation d’indicateurs USE HINT

L’exemple suivant utilise les indicateurs de requête RECOMPILE et USE HINT. L'exemple utilise la base de données AdventureWorks2012.

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (RECOMPILE, USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES', 'DISABLE_PARAMETER_SNIFFING')); 
GO  

M. Utilisation de QUERYTRACEON HINT

L’exemple suivant utilise les indicateurs de requête QUERYTRACEON. L'exemple utilise la base de données AdventureWorks2012. Vous pouvez activer tous les correctifs affectant le plan contrôlés par l’indicateur de trace 4199 pour une requête particulière avec la requête suivante :

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (QUERYTRACEON 4199);

Vous pouvez également utiliser plusieurs indicateurs de trace comme dans la requête suivante :

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION  (QUERYTRACEON 4199, QUERYTRACEON 4137);

Voir aussi

Indicateurs (Transact-SQL)
sp_create_plan_guide (Transact-SQL)
sp_control_plan_guide (Transact-SQL)
Indicateurs de trace
Conventions syntaxiques de Transact-SQL