257
8.2 Gestion avancée des plans d’exécution
FAST nombre_de_lignes
Précise que la requête doit être optimisée pour renvoyer d’abord un nombre spécifique de lignes au client, avant de poursuivre. Cette clause peut être utile pour un
affichage rapide sur une première page de pagination. Elle permet, comme la clause
TOP, de profiter de la fonctionnalité d’objectif de lignes (row goal), qui peut modifier
le plan d’exécution en conséquence. C’est une bonne chose pour le retour du nombre de lignes indiqué, mais potentiellement au détriment des performances de la
requête entière. Il s’agit donc d’une option à double tranchant, qui peut faire empirer les choses 1 . Il est préférable d’utiliser la clause TOP, qui garantit le retour du nombre de lignes indiqué.
FORCE ORDER
Spécifie que l’ordre des tables dans la déclaration de jointure doit être conservé.
Cette option est très rarement utile, mais si vous avez un soupçon de mauvaise génération de plan, vous pouvez essayer différents ordres d’apparition des tables dans
votre clause FROM, avec cette option, pour voir quelle est la combinaison gagnante.
Une fois de plus, un changement structurel de tables, et la modification de la stratégie d’indexation, peut changer le plan optimal, qui ne pourra plus alors être automatiquement choisi par l’optimiseur.
MAXDOP nombre_de_processeurs
Vous permet d’indiquer un nombre de processeurs maximum impliqué dans une
parallélisation de la requête. Rappelons que la parallélisation maximum peut être
définie au niveau du serveur par l’option « maximum degree of parallelism ». Cet
indicateur vous permet d’attribuer une autre valeur à une requête spécifique, ce qui
peut se révéler utile soit pour désactiver le parallélisme sur une requête coûteuse
(MAXDOP 1), soit pour l’activer sur une requête alors qu’il est désactivé au niveau du
serveur. Rappelons également que cette option ne signifie pas que SQL Server va
nécessairement paralléliser la requête. On lui en donne la possibilité, et il fait son
choix selon le coût de la requête (selon l’option de serveur « cost threshold for
parallelism ») et la charge actuelle des processeurs de la machine.
OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )
Indique à l’optimiseur de générer un plan optimisé pour une valeur de paramètre
donnée, ce qui est très utile pour déjouer les pièges du parameter sniffing (voir section
9.2.1). Il vous suffit d’indiquer une valeur distribuée de façon représentative dans
votre colonne, pour éviter la compilation avec des plans extrêmes. SQL Server 2008
ajoute plus de souplesse à cet indicateur :
• OPTIMIZE FOR UNKNOWN : tous les paramètres sont considérés avec une valeur
de distribution moyenne de la colonne.
• OPTIMIZE FOR ( @variable_name = UNKNOWN) : une valeur de distribution
moyenne est considérée pour ce paramètre.
1. Voir http://blogs.msdn.com/queryoptteam/archive/2006/03/30/564912.aspx
8.2 Gestion avancée des plans d’exécution
FAST nombre_de_lignes
Précise que la requête doit être optimisée pour renvoyer d’abord un nombre spécifique de lignes au client, avant de poursuivre. Cette clause peut être utile pour un
affichage rapide sur une première page de pagination. Elle permet, comme la clause
TOP, de profiter de la fonctionnalité d’objectif de lignes (row goal), qui peut modifier
le plan d’exécution en conséquence. C’est une bonne chose pour le retour du nombre de lignes indiqué, mais potentiellement au détriment des performances de la
requête entière. Il s’agit donc d’une option à double tranchant, qui peut faire empirer les choses 1 . Il est préférable d’utiliser la clause TOP, qui garantit le retour du nombre de lignes indiqué.
FORCE ORDER
Spécifie que l’ordre des tables dans la déclaration de jointure doit être conservé.
Cette option est très rarement utile, mais si vous avez un soupçon de mauvaise génération de plan, vous pouvez essayer différents ordres d’apparition des tables dans
votre clause FROM, avec cette option, pour voir quelle est la combinaison gagnante.
Une fois de plus, un changement structurel de tables, et la modification de la stratégie d’indexation, peut changer le plan optimal, qui ne pourra plus alors être automatiquement choisi par l’optimiseur.
MAXDOP nombre_de_processeurs
Vous permet d’indiquer un nombre de processeurs maximum impliqué dans une
parallélisation de la requête. Rappelons que la parallélisation maximum peut être
définie au niveau du serveur par l’option « maximum degree of parallelism ». Cet
indicateur vous permet d’attribuer une autre valeur à une requête spécifique, ce qui
peut se révéler utile soit pour désactiver le parallélisme sur une requête coûteuse
(MAXDOP 1), soit pour l’activer sur une requête alors qu’il est désactivé au niveau du
serveur. Rappelons également que cette option ne signifie pas que SQL Server va
nécessairement paralléliser la requête. On lui en donne la possibilité, et il fait son
choix selon le coût de la requête (selon l’option de serveur « cost threshold for
parallelism ») et la charge actuelle des processeurs de la machine.
OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )
Indique à l’optimiseur de générer un plan optimisé pour une valeur de paramètre
donnée, ce qui est très utile pour déjouer les pièges du parameter sniffing (voir section
9.2.1). Il vous suffit d’indiquer une valeur distribuée de façon représentative dans
votre colonne, pour éviter la compilation avec des plans extrêmes. SQL Server 2008
ajoute plus de souplesse à cet indicateur :
• OPTIMIZE FOR UNKNOWN : tous les paramètres sont considérés avec une valeur
de distribution moyenne de la colonne.
• OPTIMIZE FOR ( @variable_name = UNKNOWN) : une valeur de distribution
moyenne est considérée pour ce paramètre.
1. Voir http://blogs.msdn.com/queryoptteam/archive/2006/03/30/564912.aspx