SQL Server Performances – CHAPITRE 3 – Estimateur de Cardinalité, Intelligent Query Processing (IQP), Query Store, drapeaux de trace…

Voici une série d’article comportant 10 chapitres destinés à tout ceux qui veulent obtenir de meilleurs performances avec SQL Server… C’est à l’occasion des JO de paris, que j’ai eu l’idée de cette série d’articles sur les performances… Ou plus exactement comment remédier aux mauvaises performances de vos bases de données avec SQL Server, lorsque c’est le cas…

Chapitres précédents :

SQL Server Performances – CHAPITRE 2 – Faut-il paramétrer, régler, tuner son instance ?
SQL Server Performances – CHAPITRE 1 – Et la VM dans tout ça ?

Nous allons maintenant voir comment contraindre l’optimiseur pour améliorer encore les performances des requêtes sans avoir besoin de (trop) les récrire… Nous parlerons alors des réglages au niveau des instances via les drapeaux de trace, réglages au niveau des bases via la commande ALTER DATABASE SCOPED CONFIGURATION et des requêtes avec les indicateurs, et nous parlerons des outils que sont l’Intelligent Query Processing (IQP) et le Query Store (QS) pour améliorer l’exécution des requêtes…

Mais avant tout nous allons répondre à une question que tout le monde se pose…

0 – Quel est le rôle de l’optimiseur ?

Lorsque le développeur écrit une requête SQL, c’est à dire du code qui effectue une demande, ce code est envoyé au SGBD Relationnel qui transcrit la demande en différentes étapes de résolution afin de renvoyer les données censées résulter de la demande.

  • Si le code est une extraction de données (commande SELECT), le résultat renvoyé est un jeu de données (dataset) et non, comme on le croit trop souvent une table, ceci afin qu’un langage applicatif client puisse récupérer ces informations et les utiliser itérativement.
  • Si le code est une mise à jour (INSERT, UPDATE, DELETE, MERGE, TRUNCATE…) ou encore une modification de la structure de la base (CREATE, ALTER, DROP…), le code renvoyé est un message qui peut être l’information de réussite ou une exception lorsqu’une erreur se produit.

Dans tous les cas, le code SQL n’est jamais exécuté. Ce code est transformé par l’optimiseur en une série d’actions unitaires liées les unes aux autres dans une organisation arborescente dont la racine est le résultat et les feuilles les accès aux données pour ce qui concerne le SELECT. En effet le langage SQL est un langage de requête, c’est à dire une demande et non un code d’exécution. Ce qui sera exécuté résulte de la transformation de la requête en une série de blocs de code élémentaires constitués de modules de code écrit en langage C dans le cas de Microsoft SQL Server.

NOTA : pour les autres commandes, le processus est identique, mais à des fins de simplification nous ne nous intéresserons qu’au SELECT.

Lors de l’envoie d’une requête, les principales phases, avant exécution, sont les suivantes :

  • Phase de vérification
    • analyse syntaxique (parser) : vérifie que la requête est interprétable au niveau du code SQL, sinon rejet;
    • identification des objets en cause (métadonnées) : recherche si les tables, colonnes, fonctions… décrite dans le code de la requête existent dans la base, sinon, rejet;
    • vérification des privilèges (métadonnées) : recherche si l’utilisateur qui a lancé la requête possède les autorisations d’exécution nécessaire sur tous les objets, sinon rejet.
  • Phase d’optimisation :

Figure 1 – Arbre relationnel d’une requête SQL

    • simplification heuristique : consiste à simplifier à l’aide de règles strictement mathématiques (substitutions, réduction, factorisation…);
    • simplification sémantique : consiste à tirer partie de la structure de la base et de ses contraintes pour alléger l’arbre algébrique (suppression de branches redondantes, de calcul inutiles…);
    • optimisation statistiques (basées sur les histogrammes de distribution des données) afin d’évaluer le nombre de lignes à manipuler à chaque étape dans le but de choisir les algorithmes les plus adaptés;
    • optimisation avancées (transformations, réordonnancent…) : règles sophistiquées dont certaines sont le secret des grand éditeurs (Microsoft SQL Server, Oracle Database, IBM DB2…).

Une fois toutes ces phases passées, le résultat est transmis au module d’exécution, qui, lui même, va effectuer plusieurs étapes afin de renvoyer un résultat (gestion de la transaction, pose de verrous, mise en cache des données nécessaire à la requête et qui ne le sont pas encore, accès aux données en cache, obtention des ressources nécessaires à l’exécution de la requête, traitement de la requête, vérification des contraintes, constitution d’un espace de stockage du jeu de données, alimentation du jeu de données…)

Une bonne explication du fonctionnement d’un optimiseur théorique est donné dans l’article en ligne « 7. Evaluation et optimisation » de Philippe Rigaux (CNAM).

La figure 1 présente un arbre algébrique de résolution de la requête avant optimisation. La requête SQL correspondante est fournie par l’exemple 1 suivant :

Exemple 1 – requête avec trois tables,
deux jointures et un prédicat à deux membres

Une fois l’optimisation achevée, le moteur de requête exécute les différentes étapes du plan d’exécution :

Figure 2 – Plan d’exécution de la requête ci-avant (Microsoft SQL Server / Sentry plan explorer)

Dans SQL Server, les plans d’exécution des requêtes sont mis en cache et réutilisés. Lorsqu’un plan est utilisé plusieurs fois, il est paramétré, c’est à dire que les valeurs des arguments sont remplacés par des variables (voir Chapitre 2 § 3.5). Pour les plans d’exécution rarement utilisés il convient d’en minimiser le cache  (voir Chapitre 2 § 1.15).

Un petit secret…

Pour être efficace un optimiseur doit calculer en moins de temps possible un plan optimal. Mais comme le calcul d’optimisation prend pas mal de temps, des règles de simplification, sorte de court-circuits pour éviter trop d’hypothèses, sont préprogrammées dans l’optimiseur. Il serait effectivement illogique que le calcul d’un plan d’exécution prennent plus de temps que l’exécution de la requête avec un plan trivial…

Vous pouvez voir l’usage e ces règles à l’aide de la DMV suivante :

Néanmoins, cette DMV n’est pas documentée ni garantie (portabilité inconnue). Actuellement 439 règles sont implantées dans l’optimiseur.

Quelques règles triviales :

SELonJN : transforme les jointures effectuées dans le WHERE par un produit cartésien suivi d’un filtre en véritables jointures (autant écrire correctement ses requêtes, l’optimiseur gagne du temps et peu aboutir à un meilleur plan…)
JoinCommute : étudie la commutativité des jointures de type INNER et FULL OUTER : (A JOIN B) JOIN C <=> A JOIN (B JOIN C)
GbAggBeforeJoin : étudie la possibilité de réaliser une opération d’aggrégation après la jointure.
JNto… : transforme une jointure logique en opération physique. …NL avec boucles imbriquées (Nested Loop). …HS en hachage (HaSh). …SM en tri fusion (Sort Merge)
GBAggTo… : transforme une opération logique d’aggrégation en opération physique. …Strm (Stream aggregate), …HS (HaSh aggregate).
GetToScan, GetToTrivialScan, GetIdxToRng : transforme la projection en balayage ou lecture d’un intervalle dans un index (Rng).
CollapseIdenticalScalarSubquery : factorise des sous-requêtes identiques.

Vous pouvez vous amuser à interdire certaines règles à l’optimiseur, via l’indicateur de requête QUERYRULEOFF… Évidemment non documenté !

À titre informatif, voici un exemple d’utilisation de cette DMV non documentée :

 

1 – Estimateur de Cardinalité

C’est l’un des modules les plus important de l’optimiseur. Son rôle est de calculer une estimation du nombre de lignes rapportées dans chaque étape du plan, afin de :

  • organiser séquentiellement les différentes étapes du plan d’exécution pour avoir le moins de ressources à utiliser;
  • choisir à chaque étape, l’algorithme le plus adapté en fonction de différentes paramètres dont, le nombre de lignes.

Par exemple une jointure, dans MS SQL Server, peut utiliser cinq algorithmes différents :

  • les boucles imbriquées
  • la fusion
  • le hachage
  • l’union
  • la jointure adaptative

Les boucles imbriquées ne sont utilisées que si l’un des jeu de données n’a que très peu de lignes à joindre. Dans le cas inverse, l’optimiseur va étudier la jointure par tri-fusion ou le hachage. Si son estimation est trop « vague », il peut utiliser la jointure adaptative, qui décidera laquelle des méthodes sera utilisée en définitive au cours de l’exécution de la requête… La jointure d’union n’est utilisée que dans quelques cas particuliers tels que les opérations ensemblistes (UNION, INTERSECT, EXCEPT) ou la semi anti-jointure.

Mais pour cela il faut estimer le nombre de ligne, c’est à dire la cardinalité prévisionnelle à chaque étape du plan d’exécution de la requête est c’est le rôle du module appelé « estimateur de cardinalité » de l’optimiseur.
Bien que le système collecte de nombreuses données de distribution des valeurs des colonnes stockées sous la forme d’histogrammes, il ne s’agit que d’estimations qu’il faut ensuite corréler à d’autres pour obtenir une estimation globale a peu près viable… C’est là toute la difficulté du calcul de cardinalité, surtout lorsqu’il y a de nombreux prédicats de filtrage et que certains son peu cherchables…

Figure 3 – exemple de distribution statistique sous forme d’histogramme utilisée par l’optimiseur

Pour la petite histoire, c’est SQL Server dans les années 80 qui a inventé l’optimisation statistiques du temps ou ce SGBD Relationnel était la propriété de Sybase. Microsoft a une première fois amélioré le calcul d’estimation des cardinalités avec la version 7. Puis une nouvelle fois avec la version 2014…

L’estimation du nombre de ligne se fonde sur ces statistiques par rapport au prédicat de filtrage qui peut figurer dans les clauses suivantes :

  • WHERE
  • HAVING
  • JOIN (plus précisément dans le « ON »)
  • ORDER BY (plus précisément dans la partie « OFFSET / FETCH »)

Les membres du prédicat pouvant être cherchable ou non. Sont dit cherchable, les argument qui utilisent les opérateurs de comparaison suivants :

  • =
  • >, >=
  • <, <=
  • BETWEEN
  • LIKE ‘chaine%’

Ces derniers conduiront à une évaluation statistiques relativement précise.

Ne sont pas cherchable, les opérateurs de comparaison suivants :

  • <>
  • IN
  • LIKE ‘%chaine’
  • et tout prédicat de la liste précédent inversé par la négation « NOT »

Ces dernier conduiront à une estimation statistique relativement imprécise.

Mais comme il est rare qu’il n’y ait qu’un seul membre dans un prédicat de recherche, les statistiques doivent être corrélées entre elles avec les règles suivantes :

  • en présence du « et » (AND) le nombre de ligne estimée est située entre zéro et le minimum des deux statistiques;
  • en présence du « ou » (OR) le nombre de ligne estimée est située entre le maximum des deux statistiques et la somme des deux statistiques.

À titre d’exemple, la requête suivante :

Exemple 2 – recherche de personnes d’après leurs nom et prénom

Présente les estimations de cardinalité suivante :

Figure 4 – Estimation statistique de distribution des noms

Figure 5 – Estimation statistique de distribution des prénoms

Soit 2055 nom pour DURAND et 15764 pour Jean. Il y aura au plus 2055 lignes si tous les DURAND se prénomment Jean et si aucun DURAND n’a comme prénom Jean, le résultat sera vide. Mais que dit l’optimiseur ?

Figure 6 – Estimation corrélées pour Jean DURAND

L’estimateur de cardinalité à calculé que le nombre de lignes de la table pour lesquelles nous auront des Jean DURAND est de 25,8414. Pour information, le nombre exact de lignes retournées sera de 27, la réalité se trouve donc très proche de l’estimation, ce qui est excellent.

En modifiant la requête comme suit :

Exemple 3 – recherche de personnes d’après leurs nom ou prénom

…en présence du OR, l’estimateur de cardinalité indique que nombre de lignes de la table pour lesquelles nous auront des Jean ou des DURAND est de 17793,2.

Figure 7 – Estimation corrélée avec OR

Pour information, le nombre exact de lignes retournées sera de 17792, la réalité se trouve donc encore une fois très proche de l’estimation…

Évidemment cette précision se perd à l’augmentation du nombre de table dans la requête, du nombre de membre de prédicat et de la cherchabilité de chacun d’eux… Si ce sujet vous intéresse, lisez l’article de Microsoft « Cardinality Estimation (SQL Server) » et s’il vous passionne, lisez ce papier d’état de l’art de la recherche « On an Information Theoretic Approach to Cardinality Estimation« …

1.1 – Changement du mode de calcul.. et ses conséquences

C’est à l’occasion de la version 2014 que Microsoft à procédé à un changement majeur dans le mode de calcul de l’estimateur de cardinalité. Notez que Microsoft communique assez peu sur le fonctionnement intime de l’optimiseur et pour une raison évidente : la R&D coute cher… Pourquoi la livrer gratuitement à la concurrence et aux pilleurs que sont MySQL et PostGreSQL ?

Ce changement a eu pour conséquence :

  • un calcul des statistiques de distribution différent;
  • une méthode d’évaluation de la cardinalité  modifiée.

Autrement dit, avant 2014 l’estimation de la cardinalité donne des résultats discordants par rapport aux versions plus moderne. Dans une majorité de cas elle est meilleure mais dans une faible minorité elle est si divergente qu’elle peut conduire à une lenteur catastrophique de l’exécution de la requête dans certaines conditions…
En effet, le phénomène ne se produit que dans la mesure ou une base de données est ancienne et vient d’être restaurée dans une instance 2014 ou supérieure et qu’on l’a mise en compatibilité 2014 ou supérieure, ce qui entraine automatiquement l’utilisation du nouvel estimateur de cardinalité. L’explication, la plupart du temps tient à des index qui ont été posés pour aider l’ancienne version de l’estimateur de cardinalité et s’avèrent trompeur pour le nouveau…

1.2 – Comment mieux utiliser l’estimateur de cardinalité ?

Conscient du problème, Microsoft donne la possibilité d’agir à différents niveaux : au niveau de l’instance, la base, comme au niveau de la requête à l’aide :

  • du mode de rétrocompatibilité (voir chapitre 2 §3.6);
  • du choix de l’utilisation du nouvel ou de l’ancien estimateur de cardinalité (TF 2301 et 9481, LEGACY_CARDINALITY_ESTIMATION…).

Les réglages possibles sont donc les suivants :

  • Laisser votre base de données dans une rétrocompatibilité antérieure à 2014 (dans ce cas, préférez 2012…) ce qui ne vous empêche pas de bénéficier au besoin du nouvel estimateur de cardinalité en utilisant le drapeau de trace 2301 au niveau de l’instance ou le paramètre LEGACY_CARDINALITY_ESTIMATION à OFF au niveau de la base (ALTER DATABASE SCOPED CONFIGURATION – voir CH2 §3)
  • Mettre votre base de données en version 2014 ou supérieure (dans ce cas préférez la synchroniser avec la version de l’instance), ce qui ne vous empêche pas de bénéficier au besoin de l’ancien estimateur de cardinalité en utilisant le drapeau de trace 9481 au niveau de l’instance ou le paramètre LEGACY_CARDINALITY_ESTIMATION à ON de la base .
  • Utiliser le drapeau de trace 2301 ou 9481 ponctuellement pour une requête à l’aide de la clause OPTION comme suit :

  • Ou encore utiliser l’indicateur  FORCE_LEGACY_CARDINALITY_ESTIMATION ponctuellement sur une requête comme suit :

Au besoin, ces modifications peuvent être apportées à la volée via les guides de plan. Voir §4 dans le présent chapitre.

1.3 – Correctifs de l’optimiseur

Microsoft est en permanence à la recherche des meilleurs performance et bénéficie des demandes en « hotline » de ses nombreux utilisateurs de par le monde.  Dès qu’un problème de performance est signalé et qu’il peut être corrigé, les équipes technique du développement de SQL Server procèdent à sa correction et intègre la modification dans le prochain CU (Cumulative Updates) afin que tous en bénéficie.

Cependant, ces correctifs d’optimisation ne sont intentionnellement pas activés par défaut lorsque vous appliquez un CU ni lorsque vous migrez votre instance ou vos bases vers une version plus récente. La raison en est que, dans de rares cas, des changements de plan d’exécution des requêtes pourraient entraîner des régressions de performances… Cela reste possible pour des environnements hautement optimisés où les requêtes applicatives critiques ont été fortement tunées et utilisent par conséquent des plans d’exécution spécifiques que tout changement dans la logique d’optimisation pourrait induire d’indésirables régressions de requête.

Dans la pratique, on s’aperçoit que la majorité des environnements SQL Server ne sont pas hautement optimisés et que les requêtes utilisent des plans d’exécution ordinaires. Par conséquent, les requêtes ne bénéficient potentiellement pas des avantages des différents correctifs d’optimisation introduits par les CU ou le changement d’instance. Si vous êtes dans ce cas, vous pouvez activer les correctifs d’optimisation de requête au niveau de chaque base de données ou au niveau de l’instance.  Si, après cette activation, vous constatez des régressions, vous pouvez revenir en arrière, ou corriger cela au niveau de certaines requêtes.

En particulier, un sérieux problème a été recontré dans l’estimation des cardinalités pour certines requêtes utilisant une semi anti jointure. Voir la question à ce sujet dans StackExchange et surtout la réponse détaille de Paul White à ce sujet.

Par exemple, dans le CU 5 de la version 2022, l’entrée de correctif n°2399843 concerne un problème de performance qui ne sera pas pris en compte tant que vous n’avez pas activé les correctifs de l’optimiseur.

Il y a deux moyens pour activer les correctifs de l’optimiseur :

  • l’utilisation du drapeau de trace 4199 (seul moyen jusqu’à la version 2014);
  • l’activation pour une base de données via la commande ALTER DATABASE SCOPED CONFIGURATION du paramètre QUERY_OPTIMIZER_HOTFIXES;

Vous pouvez…

  • activer le drapeau de trace 4199 au niveau de l’instance en l’ajoutant à la ligne de commande du service SQL Server – voir §5 dans le présent chapitre;
  • activer le drapeau de trace 4199 pour la session avec la commande DBCC TRACEON (4199) – voir §5 dans le présent chapitre;
  • vous pouvez l’appliquer à une requête en ajoutant la clause OPTION comme suit :

  • utiliser la commande :

…pour l’activer au niveau de la base courante

1.4 – Recalcul des statistiques et campagne d’indexation

Un usage important lorsque vous restaurez une base d’une ancienne version antérieure à 2014 ou bien que vous activez un mode de compatibilité supérieur ou égale à 2014 consiste à recalculer toutes les statistiques en mode « FULLSCAN ». En effet le modèle de calcul des statistiques a aussi évolué pour être en phase avec le nouvel estimateur de cardinalité. Bien entendu en cas de rétrogradation de l’estimateur des cardinalité vous devez faire de même.

La syntaxe (simplifié) de la commande pour mettre à jour les statistiques de l’optimiseur sur une table ou une vue indexée en mode FULLSCAN est la suivante :

Ceci ne suffit pas toujours à régler tous les problèmes. Comme comme nous l’avons déjà mentionné, les index précédemment créés peuvent être trop « adhérent » à l’ancienne version de l’estimateur de cardinalité. Il convient donc de mener une campagne d’indexation à terme en supprimant les index inutilisés et en créant les nouveaux index demandés en les mutualisant.

2 – Intelligent Query Processing (IQP)

À partir de la version 2017 est apparu de nouveaux algorithmes d’optimisation qui ont été introduits progressivement dans les dernières versions de SQL Server. Cet ensemble d’algorithme que Microsoft à commencé de travailler en 2015, constitue ce que l’on appelle le « traitement de requêtes intelligent » en anglais Intelligent Query Processing (IQP).

Figure 8 – Intelligent Query Processing (IQP)

Le tableau ci dessous présente l’ensemble de ces concepts avec :

  • la version dans laquelle cette fonctionnalité est apparue
  • dans quelle édition cette fonctionnalités est active
  • si la fonctionnalité nécessite l’activation du Query Store
FonctionnalitéVersionEditionExige QS
Jointure adaptatives (mode batch)2017Enterprisenon
Comptage approximatif distinct2019Toutesnon
Calcul de "centile" approximatif2022Toutesnon
Mode batch sur RowStore2019Enterprisenon
Rétro-ajustement d'estimation des cardinalités2022Enterpriseoui
Rétro-ajustement du degré de parallélisme2022Enterpriseoui
Rétro-ajustement d'allocation mémoire en mode batch2017Enterprisenon
Rétro-ajustement d'allocation mémoire en mode row2019Enterprisenon
Rétro-ajustement d'allocation mémoire pour le calcul de centile2022Enterpriseoui
Persistance du rétro-ajustement d'allocation2022Enterpriseoui
Exécution entrelacée pour les fonctions table multi-instruction2017Toutesnon
Forçage de plan optimisé2022Toutesoui
Enlignement des fonctions (UDF) scalaires2019Toutesnon
Compilation différée des variables table2019Toutesnon
Optimisation de la sensibilité des paramètres du plan de requête2022Toutesnon (*)

Fonctionnalités de l'IQP

 

2.1 – Détails des fonctionnalités

Jointure adaptatives (mode batch) : permet de choisir dynamiquement en cours d’exécution de la requête l’algorithme de jointure entre le hachage et les boucles imbriquées en fonction d’un seuil analysé juste avant l’étape de jointure.

Comptage approximatif distinct : utilise l’algorithme de Flajolet-Martin (LogLog) dans sa version HyperLogLog pour estimer très rapidement et avec une empreinte mémoire très réduite, un comptage distinct (APPROX_COUNT_DISTINCT), avec une erreur moyenne de l’ordre de 2% (n’est utile que dans le cas de très grands jeux de données, c’est à dire au minimum plusieurs dizaines de millions de lignes).

Calcul de « centile » approximatif : permet un calcul rapide mais approximatif des centiles (APPROX_PERCENTILE_DISC, APPROX_PERCENTILE_CONT) pour un grand jeu de données avec des limites d’erreur acceptables basées sur le classement permettant une prise de décision rapide.

Mode batch sur RowStore : sur des index classique de type B-Tree, le mode batch de scruter plus rapidement les données par blocs plutôt que ligne par ligne comme il le, fait naturellement sur les index columnstore. Le mode batch sur rowstore prend en charge tous les opérateurs existants compatibles avec ce mode.

Rétro-ajustement : cette famille d’optimisation consiste à analyser l’historique des plans d’exécutions des requêtes passées pour affiner l’utilisation des ressources. Lorsqu’un plan de requête est calculé, le système estime la quantité de ressources à allouer pour son exécution. Lorsque ces quantités s’avère trop justes ou trop grande, une nouvelle estimation plus resserrée est faite, pour que, lors des prochaines exécutions :

  • ne pas perdre du temps à acquérir des ressources supplémentaires;
  • ne pas prendre trop de ressources au détriment des autres utilisateurs.

Certaines de ces fonctionnalités nécessite l’utilisation du Query Store (voir le tableau ci-avant).

Rétro-ajustement d’estimation des cardinalités : réajuste les calculs de cardinalité en analysant l’historique d’exécution

Rétro-ajustement du degré de parallélisme : réajuste le nombre de thread a utiliser en parallèle pour une même requête en analysant l’historique d’exécution

Rétro-ajustement d’allocation mémoire en mode batch : réajuste la mémoire utilisé pour l’exécution de la requête lorsque le mode « batch » est utilisé en analysant l’historique d’exécution

Rétro-ajustement d’allocation mémoire en mode row : réajuste la mémoire utilisé pour l’exécution de la requête lorsque le mode « row » est utilisé en analysant l’historique d’exécution

Rétro-ajustement d’allocation mémoire pour le calcul de centile : réajuste la mémoire utilisé pour l’exécution de la requête lorsqu’un calcul de centile a lieu en analysant l’historique d’exécution

Persistance du rétro-ajustement d’allocation : active la persistance du réajustement de la mémoire utilisé pour l’exécution de la requête dans les différents cas précités en analysant l’historique d’exécution

Exécution entrelacée pour les fonctions table multi-instruction : par défaut les fonction tables multi instructions ne peuvent avoir une estimation de cardinalité puisqu’étant dynamique, ce ne sera qu’après exécution de la fonction que le nombre de ligne sera connu (par défaut elles sont estimée à 1 ou 100 suivant le version de SQL Server). Dans un tel cas, cette optimisation permet de récupérer cette cardinalité après exécution pour réajuster le plan de requête dynamiquement.

Forçage de plan optimisé : force les plans optimisés à être plus rapidement repris afin d’éviter un nouveau calcul du plan d’exécution de la requête.

Enlignement des fonctions (UDF) scalaires : tente d’éliminer l’appel à la fonction qui ne peut que faire l’objet d’un traitement ligne à ligne et incorpore le code Transact SQL à l’intérieur du code de la requête afin que cette dernière puisse être traitée de manière ensembliste et non itérative.

Compilation différée des variables table : par défaut les variables table ne peuvent avoir une estimation de cardinalité puisqu’étant dynamique, ce ne sera qu’après remplissage de la variable que le nombre de ligne sera connu. Dans un tel cas, cette optimisation permet de récupérer cette cardinalité après remplissage pour réajuster le plan de requête dynamiquement.

Optimisation de la sensibilité des paramètres du plan de requête : un plan d’exécution de requête paramétré n’est généralement pas optimal pour toutes les valeurs des paramètres entrants. Cette optimisation consiste à activer automatiquement plusieurs plans dans le cache avec les estimations qui font diverger les plans d’exécution en fonction des valeurs de paramètre d’exécution fournies, lors de l’envoie de la requête au moteur.

2.2 – Activation et désactivation

Par défaut, dès que vous mettez votre base en mode de compatibilité 2017, 2019 ou 2022, ou que vous créez une nouvelle base dans l’un des instance des versions précitées, les nouveaux algorithmes de l’Intelligent Query Processing sont activé.

La désactivation comme la réactivation des différentes fonctionnalités d’IQP se fait via la commande :

On peut s’étonner de ne rien vous pour activer ou désactiver les calculs approximatif ou le comptage distinct ou de « centile », mais c’est que ces fonctionnalités sont strictement liées à l’usage des fonctions :

  • APPROX_COUNT_DISTINCT;
  • APPROX_PERCENTILE_DISC;
  • APPROX_PERCENTILE_CONT.

Mais il ne tient qu’a vous de ne pas les utiliser et de les remplacer par :

  • COUNT(DISTINCT … )
  • PERCENTILE_DISC( … ) WITHIN GROUP ( … ) OVER ( … )
  • PERCENTILE_CONT( … ) WITHIN GROUP ( … ) OVER ( … )

 

3 – Magasin de requête (Query Store)

C’est certainement un des joyaux actuels de SQL Server, et en particulier dans la version 2022… Mais quel en est son principe ?

Avant cet outil, SQL Server permettait de capturer via les vues de gestion :

  • sys.dm_exec_query_stats
  • sys.dm_exec_procedure_stats
  • sys.dm_exec_trigger_stats
  • sys.dm_exec_function_stats

…de nombreuses statistiques d’exécution des requêtes, procédures, déclencheurs et fonctions utilisateur (UDF) d’une instance. Mais ces vues possédaient trois inconvénients :

  • d’être global à l’instance;
  • se vider en cas d’arrêt de l’instance;
  • obliger à agréger, analyser et interpréter les résultats pour en déduire les améliorations à mettre en œuvre.

Query Store (en français « Magasin des Requêtes ») permet de faire la conservation et la synthèse de toutes ces données, et de corriger de manière manuelle ou automatique…

Le Query Store agit un peu à la manière d’un « enregistreur de vol » ou d’une « boîte noire » sur un avion. Mais il fournit quelques services supplémentaires…  Les vues de gestion ci-avant présentées permettent de capturer les requêtes et routines, des statistiques d’exécution diverses, les plans d’exécution, etc., mais Query Store va plus loin en stockant un historique de toutes ces données, par rapport à une base de données spécifique et en précalculant certaines informations afin d’aider ou, directement, de corriger l’exécution des requêtes les plus lentes. Ces informations aident à identifier très rapidement les problèmes de performances causés par les modifications du plan de requête ou en identifiant des différences significatives de performances, même après le redémarrage ou la mise à niveau de SQL Server, car toutes les données capturées par le Query Store sont stockées sur le disque dans des tables système de la base même.

Parmi les utilisations les plus classiques du Query Store on trouve :

  • la recherche des requêtes les plus coûteuses en termes de CPU, d’E/S, de mémoire, etc.;
  • l’obtention de l’historique complet de l’exécution des mêmes requêtes;
  • la lecture des métriques concernant les régressions de requêtes (un nouveau plan d’exécution généré par le moteur de requête qui s’avère pire que l’ancien);
  • le forçage d’un meilleur plan suite à une régression de performances;
  • des statistiques sur la fréquence d’utilisation d’une même dans une plage de temps donnée;

Figure 9 – architecture interne de fonctionnement du Query Store (Magasin des requêtes)

  • Query Store est une fonctionnalité de niveau « base de données », ce qui signifie qu’il doit être activé sur chaque base SQL que l’on veut étudier au niveau des performance d’exécution;
  • Query Store peut être mis en œuvre via l’interface graphique de SSMS (SQL Server management Studio) ou via des commandes Transact SQL;
  • Query Store présente de nombreux rapports basés sur les vues internes afin de faciliter la prise de décision;
  • Query Store est disponible sur toutes les éditions de SQL Server. Sur les bases de données Azure, et depuis la version 2022, dans les bases nouvellement créées, Query Store est activée par défaut;
  • Un privilège minimal est nécessaire à appliquer aux utilisateurs SQL pour visionner les données du Query Store (VIEW DATABASE STATE).:

3.1 – Mise en place du Query Store (magasin des requêtes)

3.1.1 – Avec l’interface graphique

Soyez sûr d’avoir la toute dernière version de SSMS (SQL Server Management Studio) avant de commencer à paramétrer le Query Store. Au moment ou j’écris cela, SSMS en est à la version 20.2.

  • Dans l’explorateur d’objet, activez le menu contextuel en cliquant droit et choisissez l’entrée « Propriétés »;
  • Dans la boîte de dialogue des propriétés, cliquez sur l’onglet « Magasin des requêtes »;
  • Activez le Query Store en définissant le « Mode d’opération » (groupe « Général ») à « Lecture/écriture ».

Figure 10 – Activation du Query Store dans SSMS

Vous pouvez paramétrer les options dans les différentes classes de groupes.

Finalisez en appuyant sur le bouton OK, ou choisissez de voir le script puis de l’exécuter.

3.1.2- Par Transact SQL

Syntaxe des commandes de paramétrage du Query Store :

Syntaxe BNF des commande de paramétrage du Query Store

Les paramètres sont les suivants :

OFF, ON : permet d’activer ou désactiver le Query Store. L’option FORCED du OFF Entraîne l’arrêt du Magasin des requêtes le plus rapidement possible. Avec OFF, toutes les tâches en cours d’exécution du Magasin de Requêtes sont arrêtées. En outre les données collectées par le Magasin des requêtes reste en l’état. Il sera de nouveau purgé lorsqu’il sera réactivé ou purgé.

CLEAR : purge les données du magasin de requête. L’option ALL en supprime le paramétrage.

OPERATION_MODE :

  • READ_WRITE : permet de lire et d’écrire dans le magasin de requêtes (valeur à défaut)
  • READ_ONLY : ne permet que la lecture des informations déjà stockées (statifie les données collectées)

NOTA : le magasin des requêtes passe en READ_ONLY en cas de saturation de l’espace de stockage.

CLEANUP_POLICY : définie la manière dont les données collectées sont purgées. À défaut 30.

DATA_FLUSH_INTERVAL_SECONDS : fréquence de scrutation en seconde pour la collecte. Par défaut 900 (15 minutes).

MAX_STORAGE_SIZE_MB : limite de l’espace de stockage de la collecte. À défaut 100 Mo en version 2016 et 2017 et maintenant 1000 Mo (1 Go) depuis la version 2019.

INTERVAL_LENGTH_MINUTES : fréquence de calcul d’agrégation des statistiques collectées. À défaut 60 minutes.

SIZE_BASED_CLEANUP_MODE : avec AUTO (à défaut) lorsque la taille du stockage des données collectées atteint 90 %, les requêtes les moins couteuses et les plus anciennes sont remplacées par les nouvelles entrées.

QUERY_CAPTURE_MODE : gère la mode de capture des requêtes. En mode ALL (à défaut pour les versions 2016 et 2017) toutes les requêtes sont capturées. En AUTO (à défaut pour 2019 et 2022) seules les plus consommatrices. Avec CUSTOM (introduit avec la version 2019) un paramétrage est nécessaire (voir l’option QUERY_CAPTURE_POLICY). NONE cesse la capture des nouvelles requêtes.

MAX_PLANS_PER_QUERY : limite le nombre de plan d’exécution différents conservés pour une même requête. À défaut 200.

WAIT_STATS_CAPTURE_MODE : sur ON (valeur à défaut), les statistiques des atentes des requêtes sont ajoutées.

QUERY_CAPTURE_POLICY : permet de finement filtrer les requêtes à conserver dans la collecte à partir de la version 2019. Les paramétrages de cette capture sont les suivants :

  • STALE_CAPTURE_POLICY_THRESHOLD : période d’évaluation limite de capture d’une requête. À défaut est de 1 jour. Limites entre 1 heure et sept jours.
  • EXECUTION_COUNT : nombre minimale d’exécution d’une même requête pendant la période d’évaluation (STALE_CAPTURE_POLICY_THRESHOLD) pour qu’elle soit collectée
  • TOTAL_COMPILE_CPU_TIME_MS : temps minimal de calcul du plan d’exécution d’une même requête pendant la période d’évaluation (STALE_CAPTURE_POLICY_THRESHOLD) pour qu’elle soit collectée
  • TOTAL_EXECUTION_CPU_TIME_MS : temps minimal d’exécution intrinsèque d’une même requête pendant la période d’évaluation (STALE_CAPTURE_POLICY_THRESHOLD) pour qu’elle soit collectée.

À titre d’exemple, voici un paramétrage valable pour SQL Server 2019 et 2022 :

Exemple 4 – paramétrage du Query Store

NOTA, l’impact de la surveillance du Query Store assez négligeable est de l’ordre de 3 à 5 % de ressources selon Microsoft. Vous pouvez mettre en place le Query Store sur la base msdb, mais ni sur master, ni sur tempdb.

3.2 – Exploitation

Après quelques jours de collecte (que j’estime à au moins 8, mais de préférence 30), vous pouvez déjà commencer à analyser les requêtes. Pour cela, dans la base dans laquelle vous avez activé le Query Store, ouvrez la branche « Magasin des requêtes » dans l’arborescence de l’explorateur d’objets de SSMS. Cela vous présente 7 écrans de synthèse…

Figure 11 – Arborescence de l’explorateur SSMS (SQL Server) – Niveau Magasin des Requêtes (Query Store)

L’écran d’analyse des requêtes régressées montre 3 parties.

Figure 12 – SQL Server – Magasin des requêtes – Écrans des requêtes régressées

En haut à gauche les temps de réponse des 25 premières requêtes régressées. Dans le bandeau du haut, on peut changer la métrique de classement. Dans l’ordre de lecture des cinq icônes de ce même bandeau on trouve :

  • une icône permettant de rafraichir l’affichage;
  • une icône permettant de suivre plus précisément la requête (ouverture d’une nouvelle fenêtre);
  • une icône permettant d’afficher le texte de la requête;
  • et les trois derniers icônes qui permettent de changer l’affichage (mode graphique / grille).

Figure 13 – SQL Server – Query Store – Temps d’exécution des différentes requêtes (histogramme)

Chaque fois que vous cliquez sur l’un des bâtons de l’histogramme les deux autres parties changent pour vous présenter :

En haut à droite le temps d’exécution des multiples plans de requête de cette même requête regroupée par couleur :

Figure 14 – SQL Server – Query Store : temps d’exécution des différents plans d’une même requête

  • La première icône du bandeau sert à rafraichir;
  • La seconde sert à forcer un plan que vous avez pointé dans le graphique;
  • La troisième sert à annuler un forçage préalablement mis en place;
  • La quatrième icône sert à comparer deux plans différents de cette même requête;
  • La cinquième icône sert à afficher les métriques des différents plans dans une grille;
  • La sixième icône sert à revenir à l’affichage graphique des différents plans d’exécution des requêtes.

En bas, figure le plan d’exécution de la requête :

Figure 15 – SQL Server – Query Store : plan de requête graphique

Vous pouvez alors choisir de forcer un plan d’exécution afin que chacune des requêtes similaires profite du meilleur plan. Chaque plan est identifié avec une couleur différente et peut se voir dans le graphique plusieurs fois.

Figure 16 – SQL Server – Query Store – Forçage d’un plan d’exécution d’une requête

Dans la figure ci avant, la requête 658 a été forcée avec le plan de version 2958. Le temps de cette exécution est en moyenne de 20 ms sur 3 exécutions contre 980 ms pour 30 exécutions. On espère donc un gain global de 29,34 secondes et une amélioration de 50 fois le temps de réponse…

D’autres écrans présentent :

  • la consommation globale des ressources pour la base;
  • Les requêtes consommant le plus de ressources;
  • La liste des requêtes ayant des plans forcés;
  • Les requête présentant de forte variations dans les mêmes plans;
  • Les statistiques d’attente et les requêtes concernées;
  • Les requêtes qui font l’objet d’un suivi.

3.3 – Corrections automatique

Pour corriger automatiquement au meilleur plan d’exécution les requêtes traquées dans le Query Store, il convient de lancer la commande :

Dès lors le SQL Server force automatiquement les recommandations applicables si le gain en UC estimé est supérieur à 10 secondes ou si le nombre d’erreurs dans le plan est supérieur au nombre d’erreurs dans le plan recommandé et vérifie en outre que le plan forcé est préférable au plan actuel.

L’automatic tuning fournit en sus une DMV (sys.dm_db_tuning_recommendations) qui permet d’interpréter les éléments analysés afin d’en corriger certains aspects. Pour l’utiliser, Microsoft donne cette requête :

Exemple 5 – interprétation des recommandations du Query Store

3.4 – Métadonnées du Query Store

Les vues suivantes sont utiles pour analyser soi-même les métriques de Query Store :

Exemple :

Exemple 6 – récupération des analyses du Query Store

Mais il en existe d’autres, notamment pour savoir quel est le paramétrage mis en place au niveau des bases :

3.4 – Procédures stockées de paramétrage du Query Store

Enfin les procédures stockées suivantes permettent de paramétrer et gérer l’utilisation du Query Store :

NOTA : par défaut, les procédures stockées compilées en mode natif ne sont pas collectées par le Query Store car cela pèse sur les performances de ces dernières. Néanmoins, Microsoft fournit la procédure stockée sys. sp_xtp_control_query_exec_stats pour activer cette collecte.

 

4 – Indicateurs de requête

Les indicateurs de requête sont des « tags » que l’on peut ajouter au texte SQL de la requête et qui impose à l’optimiseur une manière de faire. S’agissant d’instructions impératives, le moteur n’aura plus d’autre choix. La mise en place de tels indicateurs est donc risquée car une même requête peut utiliser plusieurs plans d’exécution en fonction des différences de cardinalités caculées d’après les valeurs des arguments. Avec ces indicateurs le plan devient de plus en plus rigide et n’est pas remis en cause en cas de changement de la valeur des arguments, ce qui peut provoquer des régressions parfois spectaulaires…

Il est donc conseillé de ne pas les utiliser et de ne les mettre en place qu’en dernier recours, d’autant que de version en version (cas de la migration induisant un changement de stratégie de l’optimiseur) le remède peut s’avérer pire que le mal… !

Il existe trois catégories d’indicateurs :

  • les indicateurs de jointure : impose un algorithme pour forcer la jointure;
  • les indicateurs de table : spécifique à l’usage des tables dans la requête;
  • les indicateurs globaux : modifiant le comportement global de la requête.

Certains indicateurs sont combinables.

4.1 – Indicateurs de jointure

Ils servent à forcer l’algorithme de jointure pour une des jointures de la requête ou globalement. Ce sont les mots clé :

  • LOOP : utilise l’algorithme de boucles imbriquées;
  • MERGE : utilise l’algorithme de tri fusion;
  • HASH : utilise l’algorithme de hachage.
  • REMOTE : impose que la jointure s’effectue dans l’instance ou se situe la table de droite pour une jointure interserveur (serveurs liés).

… qui se place entre le type de jointure (INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER) et le mot clé JOIN ou bien dans la clause OPTION globale à la requête.

L’exemple suivant montre l’utilisation d’un indicateur de jointure au niveau de la clause FROM :

Exemple 7 – forçage d’une jointure avec l’indicateur LOOP

Figure 17 – Plan forcé avec un indicateur de jointure

La figure 17 montre le plan d’exécution forcé de la requête de l’exemple 7. On note que seule la jointure finale fait l’objet de l’algorithme des boucles imbriquées, les autres jointures ont été décidées par l’optimiseur. En effet, les deux « tables » à joindre sont en fait des vues constituées d’autres tables…

L’exemple suivant montre l’utilisation d’un indicateur de jointure global introduit par la clause OPTION :

Exemple 8 – forçage globale des jointures avec l’indicateur LOOP

Figure 18 – Plan forcé avec un indicateur de requête

La figure 18 montre le plan d’exécution forcé de la requête de l’exemple 8. On note que toutes les jointures font l’objet de l’algorithme des boucles imbriquées contrairement à l’exemple 7.

Pour information, le coût de requête estimé par l’optimiseur sans ces indicateurs est de 0,5. Le plan utilisant l’indicateur de table a un coût de 2,1 et celui ayant un indicateur global est de 0,93…

4.2 – indicateurs de table

Pour forcer le verrouillage :

  • NOLOCK : ignore les verrous (équivalent du READUNCOMMITTED pour la table, voir ci-après)
  • ROWLOCK : force un verrouillage de ligne
  • PAGLOCK : force un verrouillage de page
  • TABLOCK : force un verrouillage de table
  • TABLOCKX : force un verrouillage exclusif de table
  • UPDLOCK : force un verrouillage de modification et le maintient jusqu’en fin de transaction
  • XLOCK : force un verrouillage exclusif et le maintient jusqu’en fin de transaction
  • HOLDLOCK : maintient le verrou jusqu’en fin de transaction (équivalent du SERIALIZABLE pour la table, voir ci-après)

ATTENTION : l’utilisation du NOLOCK peut conduire à des résultats faux, soit en ignorant certaines lignes soit en les prenant en compte plusieurs fois. Lire à ce sujet « Les Danger du NOLOCK »

Certains indicateurs peuvent être combinés. Dans le cas des vues, ils sont propagées aux tables sous-jacentes sauf dans le cas des colonnes calculées pour les tables référencées par les expressions des calculs.

Exemple 9 – Requêtes utilisant des indicateurs de verrouillage
au niveau de la table

Pour forcer un niveau d’isolation :

  • READUNCOMMITTED : lecture sale (équivalent du NOLOCK pour la table, voir ci-avant)
  • READCOMMITTED : lecture consistante
  • READCOMMITTEDLOCK : lecture consistante avec forçage du verrouillage pessimiste
  • READPAST : lit les lignes non verrouillées de manière exclusives
  • REPEATABLEREAD : lecture répétable
  • SERIALIZABLE : lecture sérailisées
  • SNAPSHOT : lecture dans une copie (uniquement pour les tables « in memory »)

Dans la mesure ou ces indicateurs représente un niveau d’isolation des transaction, veuillez vous reporter au chapitre 8 consacré aux routines et transactions). De même pour le tag SNSPSHOT, se référer au chapitre 9 qui parle des tables « in memory ».

Exemple 10 – Requêtes utilisant des indicateurs de niveau d’isolation
appliqués à la table

Divers :

  • NOEXPAND : indique que le code SQL de définition d’une vue indexée ne soit pas intégré à la requête finale (voir chapitre 6 pour plus de détails);
  • NOWAIT : interdit toute attente de lbération d’un verrou;
  • INDEX : force l’utilisation d’index. Deux syntaxes :

    • INDEX ( <liste_index> )
    • INDEX = (nom_index)
  • FORCESEEK : force l’utilisation d’un index en mode recherche. Syntaxes :
    • FORCESEEK
    • FORCESEEK, INDEX (nom_index)
    • FORCESEEK, (nom_index ( <liste_colonnes> ))
  • FORCESCAN : force un balayage (scan) de table ou d’index;
  • SPATIAL_WINDOW_MAX_CELLS : limite le nombre de cellules à scruter pour une table ayant une colonne contenant des données spatiales.

Exemple 11 – Requête dont on a forcé les méthodes d’accès aux données

Notez que, pour l’exemple 11, nous avons rajouté un index à la table système « dbo.MSdbms_datatype_mapping » de la base msdb afin d’illustrer nos propos.

QUERYRULEOFF est un idicateur de requête non documenté (à ne jamais utiliser en production) pour interdire l’usage de certains règles d’optimisation (voir §0 du présent chapitre). Notez que les commandes DBCC RULEOFF et DBCC RULEON permettent de faire la même chose au niveau session ou global, c’est à dire bien pire ! Vérifiez donc si un petit plaisantin, n’a pas désactivé certaions règles à l’aide des commandes DBCC SHOWONRULES et DBCC SHOWOFFRULES.

4.3 – Indicateurs globaux

Ces indicateurs doivent être utilisé dans la clause OPTION de la requête qui doit figurer en toute fin (après le ORDER BY s’il y en a un) et dans un couple de parentèses qui permet d’y placer une liste. Nous nous contenterons d’indiquer les principaux que nous avons répartis en six catégories :

Algorithmique :

  • { HASH | ORDER } GROUP : impose que les opérations de groupement (clause GROUP BY) soient faite par hachage ou par tri
  • { CONCAT | HASH | MERGE } UNION : impose que les opérations ensemblistes UNION opérées par concaténation, hachage ou fusion
  • { LOOP | MERGE | HASH } JOIN : impose que les opérations de jointures soient réalisées par des boucles imbriquées, la fusion ou le hachage

Exemple 12 – Forçage des algorithmes utilisés
par les opérateur UNION et GROUP BY

Gestion du plan :

  • DISABLE_OPTIMIZED_PLAN_FORCING : désactive l’optimisation forcée du plan;
  • EXPAND VIEWS : force a intégrer le code SQL d’une vue indexée dans le code de la requête au lieu de se servir des index de la vue;
  • KEEP PLAN : empêche une trop fréquente recompilation du plan de requête en présence de tables temporaires;
  • KEEPFIXED PLAN : interdit la recompilation des requêtes suite à des différences dans les statistiques, sauf en cas de restructuration des tables;
  • ROBUST PLAN : force l’optimiseur à tenter de calculer un plan dont la taille des lignes dépasse la taille maximale et lève une exception;
  • RECOMPILE : force la création d’un nouveau plan et son execution. Ce nouveau plan ne sera pas mis en cache;
  • FORCE ORDER : indique que le plan doit séquencer l’accès aux des tables dans l’ordre d’écriture de la requête.

Exemple 13 – Forçage de recompilation et d’ordre des tables

Notez que le plan ainsi forcé est estimé deux fois plus couteux que sans ces indicateurs.

Paramétrisation :

  • OPTIMIZE FOR : permet de forcer l’optimiseur à calculer un plan prenant en compte des valeurs particulières. Différentes syntaxes :
    • OPTIMIZE FOR UNKNOWN : oblige l’optimiseur à utiliser la dispersion moyenne (all_density, voir § ???) pour l’estimation de cardinalité plutôt qu’une valeur précise.
    • OPTIMIZE FOR ( <liste_paramètres> ) : oblige l’optimiseur à utiliser la dispersion moyenne ou une valeur précise pour l’estimation de cardinalité des paramètres de la liste;
  • PARAMETERIZATION { SIMPLE | FORCED } : active la paramétrisation simple ou forcée de la requête (voir Chapitre 2 §3.5)

Exemple 14 – Forçage de l’optimisation

Contraindre l’exécution :

FAST n : permet de préciser que les n premières lignes doivent être envoyées le plus rapidement possible et que le reste sera acheminé ultérieurement
NO_PERFORMANCE_SPOOL : indique de ne pas d’utiliser des opérations de « spool » (objet etmporaire pour stockage de données intermédiaires dans la base tempdb)

Ressources :

  • MAX_GRANT_PERCENT = r : limite haute en % de l’utilisation de la mémoire pour l’exécution de la requête
  • MIN_GRANT_PERCENT = r : limite basse en % de l’utilisation de la mémoire pour l’exécution de la requête
  • MAXDOP n : limite les opérations parallélisée à utiliser au plus n threads
  • MAXRECURSION n : limite les appels réentrant des requêtes récursives (par défaut 100, maximum 32767)

Exemple 15 – Limitation de la récursion à 12345 appels réentrants

Généraux :

  • QUERYTRACEON t : permet d’activer le drapeau de trace t pour l’exécution de la requête (voir ci-après §5);
  • USE HINT ( <liste_noms_indicateurs> ) : permet d’activer des indicateurs de requête supplémentaire (voir la liste Microsoft référencée);
  • USE PLAN N’<plan_xml> : permet de forcer un plan de requête préalablement récupéré sous forme XML et modifié;
  • TABLE HINT ( alias [ , <liste_indicateur_table> ) : surcharge un indicateur de table déjà présent dans la requête.

4.4 – Guides de plan

Un guide de plan (appelé dans l’inteface de SSMS « repère de plan ») permet de forcer un plan d’exécution de requête à la volée. Cette fonctionnalité n’est disponible que dans la version Enterprise.

L’intérêt du guide de plan est de permettre de corriger le plan d’exécution de la requête lorsque le code SQL des requêtes n’est pas accessible, ce qui est le cas lorsque celles-ci sont intégrées au programme d’une application tierce.

Pour pouvoir utiliser ce principe il vous faut identifier les requêtes lentes que l’indexation n’a pas pu corriger et récupérer le plan d’exécution de la requête. Vous pouvez alors utiliser la procédure stockée système « sp_create_plan_guide » qui prend en compte soit :

  • OBJECT : une routine Transact SQL telle que procédure stockée, fonctions our déclencheur ;
  • SQL : une requête SQL paramétrisée ;
  • TEMPLATE : requête SQL ad hoc;

Exemples 16 – guide de plan pour une requête paramétrique

Une interface existe dans SSMS au niveau de chaque base, dans l’entrée de menu « Programmabilité / Repère de plan.

Cette voie d’optimisation est peu recommandée :

  • fixer un plan le rend statique alors que l’optimiseur est hautement dynamique;
  • Le changement de version induit de nouvelles optimisations qui seront ignorées;
  • Query Store (le magasin de requête) rend un service plus étendu.

4.5 – Documentation complémentaire :

5 – Les drapeaux de trace du moteur

Les drapeaux de trace (ou indicateur de trace, souvent appelés aussi « flag ») sont des paramètres binaires qui modifient le comportement du moteur SQL pour des besoins particulier.

Les commandes pour activer, désactiver et voir l’état des drapeaux de trace sont les suivantes :

DBCC TRACEON active la liste des drapeaux et TRACEOFF la désactive. -1 sert à imposer une trace globale si elle ne l’est pas par défaut. TRACESTATUS permet de voir la liste des traces : toutes ou globale (-1).

Ne sont documentés ici que les drapeaux de trace pouvant avoir une influence sur les performances. La lettre G entre paranthèse en suffixe de la valeur de l’indicateur signifie que son scope est global (commune à toutes les bases, toutes les sessions). La lettre S que la trace est appliquée à la session. La lettre Q que la trace peut être utilisée dans la clause OPTION d’une requête introduite par le mot clé QUERYTRACEON. Le letre D signifie que le réglage peut être apporté au niveau d’une base via le paramétrage ALTER DATABASE … SCOPED CONFIGURATION … (voir le § 6).

5.1 – Liste des drapeaux concernant les performances

NOTA : nous n’avons pas noté les drapeaux de trace des versions antérieures à la 2016. Pour rappel, la version 2019 n’aura plus de support standard au 1er mars 2025. Il est néanmoins possible de souscrire un abonnement pour extension en support étendu (Extended Security Update) limité à 3 ans. Enfin, certains drapeaux ne sont disponible que dans des versions récentes. Veuillez consulter l’aide en ligne de Microsoft à ce sujet.

174 (G) : augmente le nombre de seau de hachage du cache de plan de 40000 à 160000 sur les systèmes 64 bits ce qui permet de mettre en cache 640000 plans. À réserver à de très grosses configurations.

272 (G, D) : désactive la mise en cache des valeurs d’autoincrément IDENTITY. Ceci permet d’éviter les « trous » dans les valeurs d’une colonne d’identité si l’instance redémarre de façon inattendue ou bascule vers un serveur secondaire. Voir §6 le paramètre IDENTITY_CACHE au niveau du scope de la base.

610 (G, S) : active une journalisation minimale pour l’insertion de données pour optimiser les performances avec certains scénarios de chargement.

634 (G) : désactive la compression des données d’index columnstore effectuée en arrière-plan. La compression columnstore améliore les performances des requêtes d’accès aux données mais consomme également des ressources système. Si activé, il faudra alors exécuter régulièrement les commandes ALTER INDEX…REORGANIZE ou REBUILD pour rétablir une compression correcte.

834 (G) : permet l’allocation de pages large pour le cache. (voir chapitre 2, §4.3).

876 (G) : permet d’utiliser des allocations de pages large pour les index columnstore. À tester soigneusement avant déploiement en production.

888 (G) : évite la dégradation des performances sur les appareils utilisant de la PMEM (mémoire persistante) combiné au pool de mémoires tampons hybride quand la PMEM s’exécutent avec un faible niveau de mémoire.

1211 (G, S) : désactive l’escalade de verrou en fonction de la sollicitation de la mémoire ou du nombre de verrous.
1224 (G, S) : désactive l’escalade de verrous en fonction du nombre de verrous. 1211 est prioritaire sur 1224. Possibilité similaire avec ALTER TABLE … LOCK_ESCALATION.
1229 (G) : désactive le partitionnement de verrous, quel que soit le nombre de processeurs (activé par défaut si 16 oeurs ou plus).

1800 (G) : permet l’optimisation d’accès aux disques dont les tailles de clusters différent lorqu’ils sont utilisés par les fichiers des journaux de transactions dans le cadre de réplicas principal et secondaires dans les environnements de haute disponibilité par Log Shipping ou ALwaysOn. Ne doit être activé que sur les instances de SQL Server dont le journal des transactions sur disque est formaté avec une taille de cluster de 512 octets.

2301 (G, S, Q) : active les optimisations d’aide à la décision avancées spécifiques aux requêtes d’aide à la décision lorsque sont utilisé des jeux de données volumineux.

2312 (G, S, Q, D) : impose le modèle d’estimation de cardinalité de l’optimiseur de requête le plus récent (à partir de la version 2014) quel que soit le niveau de compatibilité de la base de données. Voire son contraire l’indicateur 9481. Au niveau de la requête : USE HINT ‘FORCE_DEFAULT_CARDINALITY_ESTIMATION’. Au niveau de la base, voir les configurations liées au scope de la base (§6 du présent chapitre). À rapprocher du TF 9481.

2335 (G, S, Q) : force SQL a générer des plans d’exécution de requête optimisés même si la mémoire nécessaire est bloquée en dessous de la limite fixée. À tester soigneusement avant mise en production.

2338 (G, S, Q, D) : impose à SQL Server d’utiliser un plan fin pour les UPDATE (ce qu’il fait dans une grande majorité de cas), c’est à dire que la mise à jour de la clé d’un index clustered est répercuté simultanément dans tous les index non clustered. Inutile si les clés primaires sont de type IDENTITY.

2340 (G, S, Q) : impose à SQL Server d’effectuer un tri préalable pour les opérations de jointures par boucles imbriquées. À tester soigneusement avant déploiement en production. Au niveau de la requête USE HINT ‘DISABLE_OPTIMIZED_NESTED_LOOP’.

2389 (G, S, Q) : active la génération de statistiques rapides de l’optimiseur pour des clés croissantes (modification de l’histogrammedont  la cardinalité sera ajusté au moment de la compilation de la requête). À tester soigneusement avant déploiement en production.
2390 (G, S, Q) : active la génération de statistiques rapides de l’optimiseur pour des clés croissantes ou inconnues (modification de l’histogrammedont  la cardinalité sera ajusté au moment de la compilation de la requête). À tester soigneusement avant déploiement en production.

2422 (G) : oblige le moteur de requête à annuler une demande lorsque la durée maximale définie par REQUEST_MAX_CPU_TIME_SEC au niveau du gouverneur de ressource est dépassée.

2451 (G, D) : active l’équivalent du dernier plan d’exécution réel pour alimnter la DMV sys.dm_exec_query_plan_stats. Voir dans ce chapitre § 6 LAST_QUERY_PLAN_STATS.

2453 (G, S, Q) : force une recompilation quand un nombre suffisant de lignes a changé dans une variable table. À tester soigneusement avant déploiement en production. Proche de la compilation différée des variables table du Query Store.

2467 (G) : impose une stratégie particulière d’allocation de thread de travail parallèle, selon le nœud NUMA auquel le moins de threads sont alloués. Le degré de requête de parallélisme doit tenir dans un seul nœud NUMA pour utiliser cette stratégie de remplacement, sinon la stratégie d’allocation de threads par défaut est utilisée à la place. Avec cet indicateur de trace, il est déconseillé d’exécuter des requêtes spécifiant un degré de parallélisme supérieur au nombre de planificateurs dans un seul nœud, car cela peut interférer avec des requêtes spécifiant un degré de parallélisme inférieur ou égal au nombre de planificateurs dans un seul nœud.

3226 (G) : évite la journalisation des opérations de sauvegardes dans le fichiers des événements applicatifs de SQL Server (les sauvegardes sont toujours tracées dans la base msdb).

4136 (G, S) : désactive la détection des paramètres, sauf si OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR (…) est utilisé. Voir §6 du présent chapitre, l’option de configuration au niveau du scope de la base « PARAMETER_SNIFFING ».

4137 (G, S, Q) : oblige l’optimiseur à générer un plan d’exécution de requête qui utilise la sélectivité minimale pour  l’évaluation des prédicats AND des filtres à prendre en compte pour la corrélation partielle à la place du principe d’indépendance, pour le modèle d’estimation de cardinalité (LEGACY) établi pour les versions 2012 et antérieures. Au niveau de la requête, utilisez l’option « ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES » via USE HINT (voir §4.3 du présent chapitre). À tester soigneusement avant mise en production.

4138 (G, S, Q) : oblige SQL Server à générer un plan d’exécution de requête qui n’utilise pas les ajustements d’objectif de nombre de lignes pour les requêtes contenant les opérateurs TOP (n),  IN, EXISTS ou ll’indicateur de requête FAST n. Au niveau de la requête, utilisez l’option « DISABLE_OPTIMIZER_ROWGOAL » via USE HINT (voir §4.3 du présent chapitre). À tester soigneusement avant mise en production.

4139 (G, S, Q) : active automatiquement la génération de statistiques rapides (modification de l’histogramme) quel que soit l’état de la colonne clé. L’histogramme utilisé pour estimer la cardinalité sera alors ajusté au moment de la compilation de la requête. Au niveau de la requête, utilisez l’option « ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS » via USE HINT (voir §4.3 du présent chapitre). À tester soigneusement avant mise en production.

4199 (G, S, Q, D) : active les corrections de l’optimiseur de requête publiées dans les mises à jour cumulatives et les Service Packs de SQL Server.  Voir §4.3 du présent chapitre (USE_HINT = ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’) et  §6 du présent chapitre, l’option de configuration au niveau du scope de la base : « QUERY_OPTIMIZER_HOTFIXES ».

4610, 4618 (G) : gestion de la taille de la table de hachage qui stocke les entrées du cache « TokenAndPermUserStore ». Appliqué seul le TF 4618 limite le nombre d’entrée à 1024. Combiné au TF 4610, le nombre d’entrée est multiplié par 8 (8 192).
4621 : Limite le nombre d’entrées dans le magasin de cache » TokenAndPermUserStore » au nombre spécifié par l’utilisateur dans une clé de Registre.

7412 (G) : active l’infrastructure légère de profilage des statistiques sur l’exécution des requêtes. §6 du présent chapitre, l’option de configuration au niveau du scope de la base : « LIGHTWEIGHT_QUERY_PROFILING ». Activé par défaut au niveau base depuis la version 2019.

7471 (G) : permet l’exécution simultanée de plusieurs instructions UPDATE STATISTICS pour différentes statistiques d’une seule table.

7745 (G) : interdit le vidage des données du magasin de requêtes sur le disque en cas d’arrêt de l’instance. La commande SHUTDOWN WITH NOWAIT peut être utilisée à la place de cet indicateur de trace pour forcer un arrêt immédiat sans vidage des données du Query Store.

7752 (G) : active le chargement asynchrone du magasin des requêtes (utilse si SQL Server rencontre un nombre élevé d’attentes de type QDS_LOADDB. Activé définitivement depuis la version 2019.

8032 (G) : rétablit les paramètres de limitation du cache au paramètre de la version qui établi en général des caches plus volumineux. Utilisez ce paramètre quand les entrées du cache fréquemment utilisées ne tiennent pas dans le cache et que l’option de configuration de serveur « optimize for ad hoc workload » ne résoud pas ce problème de cache de plans. À tester soigneusement avant mise en production.

8048 (G) : force la conversion des objets de mémoire partitionnés NUMA en objets de mémoire partitionnés par le processeur.

8086 (G) : désactive l’usage de NUMA pour la segmentation de la mémoire en noeuds par coeurs.

8099 (G) : active un correctif de contention de verrouillage tournant pour de grand systèmes (multiprocesseurs de plus de 8 coeurs physiques…) qui exécutent la version 2019 traitant de nombreuses requêtes simultanément. Version 2019 et ultérieures.
8101 (G) : résout des problématiques d’utilisation élevée du processeur sur du matériel moderne, comme les processeurs Intel Skylake, avec un grand nombre de processeurs et d’utilisateurs simultanés. Applicable à la version 2019 + Cu8, 14 et 16 et ultérieures.
8121 (G) : corrige un problème de faible mémoire à l’échelle du système qui se produit lorsque SQL Server valide la mémoire au-delà de la mémoire maximale du serveur sous le modèle de mémoire avec le verrouillage des pages en mémoire (voir §4.2 du chapitre 2). Version 2019 et ultérieures.

8142 (G) : partitionne la liste spécifique protégée par spinlock, par processeur, jusqu’à 64 partitions. Ne doit être utilisé que sur des machines à mémoire volumineuse (plus de 256 Go de RAM) qui rencontrent une contention de verrouillage de spinlock SOS_BLOCKALLOCPARTIALLIST combiné à une utilisation élevée du processeur. Version 2019 et ultérieures.
8145 (G) : modifie le partitionnement activé par l’indicateur de trace 8142 pour qu’il soit par nœud soft-NUMA, au lieu d’un partitionnement par processseur. Version 2019 et ultérieures.

8284 (G) : corrige un problème de nettoyage manuel lors des escalades répétées de verrous sur les tables, provoquent une contention et par consésuent une lenteur dans le nettoyage des métadonnées de suivi des modifications expirées.

8286, 8287 (G) : force la requête de nettoyage du Change Tracking à utiliser l’indicateur FORCE ORDER à des fins d’amélioration des performances. Peut être combiné avec TF 8287 pour exploiter l’indicateur FORCESEEK. Version 2022 CU 1 et ultérieures.

8790  (G, S, Q) : permet à SQL Server d’utiliser un plan d’exécution de requête « large » pour l’UPDATE afin de mettre à jour les index de la table cible de la mise à jour. Ceci conduit  SQL Server à choisir de trier toutes les données d’index non cluster en mémoire, puis de mettre à jour tous les index dans l’ordre.

8902 (G) : désactive le verouillage des pages en mémoire (LPIM – voir chapitre 2 § 4.2) pour les opérations d’E/S pour les systèmes haut de gamme (très nombreux coeurs et mémoire élevé) avec des charges de travail hautes performances.

9135 (G) : empêche l’utilisation des vues indexées. Équivalent de l’indicateur de requête NOEXPAND utilisé au niveau de la table.

9348 (G, S, Q) : permet l’utilisation des estimations de cardinalité de l’optimiseur de requête pour décider si une commande BULK INSERT visant un index Columnstore Clustered doit être utilisé ou non. Si le nombre estimé de lignes à insérer est inférieur à 102 400, le Moteur de base de données n’utilise pas BULK INSERT.

9349 (G, S, Q) : désactive le mode batch pour l’opérateur de tri des N premiers.
9358 (G, S, Q) : désactive le mode batch pour l’opérateur de tri.

9389 (G, S) : permet une allocation de mémoire dynamique supplémentaire pour les opérateurs en mode batch. Si une requête n’obtient pas toute la mémoire dont elle a besoin, elle déverse des données sur tempdb, ce qui occasionne des E/S supplémentaires et affecte potentiellement les performances des requêtes. Si l’indicateur de trace d’allocation de mémoire dynamique est activé, un opérateur en mode batch peut demander plus de mémoire et éviter le déversement sur tempdb. À réserver pour des instances ayant une mémoire importante.

9398 (G, S, Q) : désactive l’opérateur de jointure adaptative qui permet de différer un algorithme de jointure par hachage ou par boucles imbriquées tant que la première entrée n’a pas été analysée. À tester soigneusement avant mise en production.

9410 (G, S, Q) : Active un correctif non défini par défaut pour une requête qui utilise un opérateur d’agrégation avec DISTINCT utilisant l’algorithme par hachage et qui provoque des débordements. L’activation de cet indicateur augmente la mémoire disponible pour cette opération. À tester soigneusement avant mise en production.

9440 (G, S, Q) : désactive les correctifs des bogues 2112485 et 2636294pour le modèle récent d’estimation de cardinalité (CE). Les estimations de la cardinalité de jointure externe peuvent augmenter plus que la cardinalité des tables impliquées dans la jointure quand les prédicats de jointure sont composés de clés primaires des tables (par exemple sur le couple clé primaire / clés étrangères). Un plafond est appliqué qui limitera le montant de la surestimation de la cardinalité semblable à la limite de surestimation qui existe dans l’ancien CE (LEGACY) pour ce type  de scénario.

9453 (G, S, Q) : désactive l’exécution en mode batch. À tester soigneusement avant mise en production.

9471 (G, S, Q) : Amène l’optimiseur à utiliser  la sélectivité minimale pour les filtres sur une même table dans le modèle d’estimation de la cardinalité version 2014 et ultérieures. Voir §4.3 du présent chapitre (USE_HINT = ‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’). À tester soigneusement avant mise en production.

9476 (GSQ) : amène l’optimiseur à générer un plan d’exécution de requête qui utilise l’hypothèse de bases autonomes simples au lieu de l’hypothèse par défaut de bases autonomes, sous le modèle d’estimation de la cardinalité de l’optimiseur de requête de la version 2014 et ultérieures. À tester soigneusement avant mise en production.

9481 (G, S, Q) : définit le modèle d’estimation de cardinalité de l’optimiseur sur la version 2012 antérieures quel que soit le niveau de compatibilité de la base de données. Peut être défini au niveau du scope de la base via le paramètre LEGACY_CARDINALITY_ESTIMATION (voir § 6 du présent chapitre), ainsi que comme indicateur de requête (voir § 4.3 du présent chapitre).
À rapprocher du TF 2312 son inverse.

9488 (G, S, Q) : définit la valeur fixe de 1 pour l’estimation de cardinalité pour les fonctions table multi instructions (valeur par défaut pour le modèle d’estimation de  cardinalité de l’optimiseur de requête des versions 2008 R2 et antérieures) quand vous utilisez le modèle d’estimation de la cardinalité de l’optimiseur de requête de de la version 2012 et ultérieures.

9567 (G, S) : active la compression du flux de données pour les groupes de disponibilité AlwaysOn au cours de l’amorçage automatique (automatic seeding). La compression peut réduire considérablement le temps de transfert pendant l’amorçage automatique mais augmente la charge sur le processeur.

9571 (G, S) : désactive l’amorçage automatique des groupes de disponibilité pour le chemin d’accès de la base de données par défaut.

9939 (G, S, Q) : active les plans parallèles et l’analyse parallèle des tables à mémoire optimisée (in memory) et des variables de table dans les opérations DML qui référencent des tables à mémoire optimisée ou des variables de table, tant qu’ils ne sont pas la table cible de l’opération DML dans SQL Server 2016. Pas nécessaire si l’indicateur de trace 4199 est également activé.

10054 (G, S, Q) : désactive la règle de l’optimiseur de requête SQL Server qui décorrèle les sous-requêtes des prédicats OR en jointures externes.

11024 (G) : active le déclenchement de la mise à jour automatique des statistiques quand le nombre de modifications de n’importe quelle partition dépasse le seuil.

11064 (G) : améliore la scalabilité des opérations de chargement des données dans les index columnstore, en optimisant la distribution de la mémoire entre les instructions SELECT et INSERT.

11068 (G) :  utilise la valeur configurée indiquant le degré maximal de parallélisme (MAXDOP) du serveur, de la base de données ou de la liste de ressources partagées pour les opérations d’insertion d’index columnstore.valable à partir de la version 2019.

11631 (G) : change le seuil de défragmentation de 10 % du global pour la méthode REORGANIZE  appliquée aux index COLUMNSTORE à 10% du rowgroup.
11634 (G) change le seuil de défragmentation de 10 % du global pour la méthode REORGANIZE  appliquée aux index COLUMNSTORE à 1 % du nombre actuel de lignes d’un rowgroup, au lieu de 1 % de 1 million de lignes.

12618 (G) : permet au modèle de correction de plan automatique (APC) de la fonctionnalité de réglage automatique d’effectuer plusieurs vérifications consécutives de régression de plan sur le même plan, ce qui permet d’accumuler des statistiques supplémentaires pour l’évaluation par le nouveau modèle.
12656 (G) : permet au modèle de correction de plan automatique (APC) de la fonctionnalité de réglage automatique d’utiliser une vérification de régression de plan basée sur le temps. Cette vérification se produit cinq minutes après la détection d’une modification du plan, ce qui évite de fausser les vérifications de régression en raison de requêtes s’exécutant rapidement. Cela permet à la correction APC de prendre en compte les exécutions de requêtes susceptibles de durer plus longtemps ou sujettes à un délai d’expiration en raison d’un changement de plan. Valable pour version 2022.

13127 (G, S) : active des optimisations de critères spéciaux de chaîne supplémentaires. À destination de systèmes haut de gamme (très nombreux coeurs et mémoire élevé) avec des charges de travail hautes performances.

15097 (G, S) : aActive la prise en charge d’AVX-512 pour les versions 2022 et ultérieures. Particulièrement recommandé pour la prise en charge d’AVX-512 pour les processeurs Intel Ice Lake et versions ultérieures, et AMD EYPC Genoa et versions ultérieures. Version 2022 et ultérieure.

5.2 – Pour le débogage des performances

À n’utiliser qu’en test et souvent non documentés…

445 (non documenté ) : inscrit la requête qui a été compilée ou recompilé. À combiner avec le TF 3605 pour diriger l’inscription vers le journal des événements SQL Server

662 (non documenté ) : inscrit des informations détaillées sur le travail effectué par le nettoyeur de lignes mortes : À combiner avec le TF 3605 pour diriger l’inscription vers le journal des événements SQL Server.

818 : ajoute des informations aditionnelles de diagnostic sur les E/S permettant la recherche d’écritures perdues ou de latence en lecture pendant les opérations physiques sur les disques.

1200 (non documenté ) : affiche chaque verrou posé pendant l’exécution de la requête. À combiner avec le TF 3605 pour diriger l’inscription vers le journal des événements SQL Server.

2372, 2373 (non documentés) : affiche une progession de l’utilisation de la mémoire pendant la phase de compilation du plan d’exécution de la requête lors de l’utilisation des propriétés dérivées et des règles.

8605 (non documenté) : permet de voir les phases détaillées du plan d’exécution de la requête. À combiner avec le TF 3604 pour diriger l’inscription vers la console SSMS.

8606 (non documenté) : permet de voir les différentes hypothèèses de plans d’exécution de la requête. À combiner avec le TF 3604 pour diriger l’inscription vers la console SSMS.

8608 (non documenté) : permet de visualiser le « memo » c’est à dire l’arbre algébrique qui constitué le plan initial avant optimisation. Cet objet sera modifié au cours de l’exécution de la même requête en fonction des changements de plans induit par les différents paramètres passés à la requête au cours de ses exécutions successives. À combiner avec le TF 3604 pour diriger l’inscription vers la console SSMS.

8666 (non documenté) : donne la liste des statistiques utilisées par l’optimiseur au moment de la compilation.

9130 (non documenté) : permet de mettre en évidence dans le plan d’exécution graphique les filtres résiduels.

9204 et 9292 (non documentés) : Permettent de renvoyer la liste des statistiques utilisées par l’optimiseur. Statistiques considérées avec le 9292 et statistiques utilisées pour calculer le plan avec le 9204.

 

5.3 – Liste des drapeaux à éviter d’activer

101, 102, 139, 652, 661, 692, 1204, 1222, 2330, 2446, 2562, 3042, 7470, 8011, 8012, 8015, 8018, 8019, 8020, 8744, 9347, 9591, 9592, 11023, 13156

 

5.4 – Persistance des drapeau de trace

Les drapeau de trace sont actifs tant que l’instance est opérationnelle. En cas d’arrêt de l’instance ou de la machine, les drapeaux activés sont perdus. Pour les rendre permanent il faut les relancer au moment du démarrage de l’instance, ce qui sefait dans la ligne de commande en utilisant le commutateur -T (majuscule) suivi des buméros des drapeaux de trace.

Figure 19–  Mise en place d’un drapeau de trace au démarrage du service SQL Server

La figure 19montre comment mettre en place un drapeau de trace au démarrage du service SQL Server (outil SQL Server Configuration Manager).

 

6 – Réglages limités au scope de la base

Si nous avons tant attendu pour parler de ces réglages déjà pas mal évoqué dans les précédentes parties de nos articles, c’est parce que la grande majorité de ces règlages sont des reports de réglages existant au niveau de l’instance ou de la session, mais que l’on peut limiter aux bases de données que l’on souhiate voir se comporter différemment par rapport à l’instance…

Une syntaxe allégée de la commande est la suivante :

NOTA : nous ne l’avons pas fait figurer sur cette syntaxe, mais il est possible d’activer certains paramètres pour le noeud primaire ou secondaire d’une configuration de haute disponibilité AlwaysOn avec les mots clés PRIMARY ou FOR SECONDARY.

6.1 – Réglages déjà étudiés :

Les réglages suivants sont hérités de sp_configure (voir §1 du chapitre 2) :

  • MAXDOP = { <value> | PRIMARY}
  • OPTIMIZE_FOR_AD_HOC_WORKLOADS = { ON | OFF }

Réglages faisant partie de l’Intelligent Query Processing (voir § 2 du présent chapitre):

  • TSQL_SCALAR_UDF_INLINING = { ON | OFF }
  • PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = { ON | OFF }
  • OPTIMIZED_PLAN_FORCING = { ON | OFF }
  • DEFERRED_COMPILATION_TV = { ON | OFF }
  • BATCH_MODE_ON_ROWSTORE = { ON | OFF }
  • INTERLEAVED_EXECUTION_TVF = { ON | OFF }
  • BATCH_MODE_ADAPTIVE_JOINS = { ON | OFF }
  • CE_FEEDBACK = { ON | OFF }
  • DOP_FEEDBACK = { ON | OFF }
  • MEMORY_GRANT_FEEDBACK_PERSISTENCE = { ON | OFF }
  • MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = { ON | OFF }
  • BATCH_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
  • ROW_MODE_MEMORY_GRANT_FEEDBACK = { ON | OFF }
  • ACCELERATED_PLAN_FORCING = { ON | OFF }

NOTA : le nom de certains paramètres ont évolués au passage de la version 2019 :

  • DISABLE_INTERLEAVED_EXECUTION_TVF est devenu INTERLEAVED_EXECUTION_TVF
  • DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK est devenu BATCH_MODE_MEMORY_GRANT_FEEDBACK
  • DISABLE_BATCH_MODE_ADAPTIVE_JOINS est devenu BATCH_MODE_ADAPTIVE_JOINS

NOTA : l’option ACCELERATED_PLAN_FORCING disponible dans la commande ALTER DATABASE SCOPED CONFIGURATION est activée par défaut pour accélérer la mise en place des plans forcés quelque’en soit la manière (Query Store, l’indicateur global USE_PLAN, guide de plan…). Il n’est pas conseillé de le désactiver…

Estimateur de cardinalité (voir §1.2 du présent chapitre) :

  • LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}

6.2 – Paramètres de performances

EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = { ON | OFF } permet de désactiver la collecte des statistiques d’exécution sur les fonctions utilisateur (UDF) scalaires affichées dans la DMV dm_exec_function_stats. Par défaut ON.

IDENTITY_CACHE = { ON | OFF } : active ou désactive le cache d’autoincrément de type IDENTITY (par défaut ON). Mettre en cache ces valeurs permet d’améliorer les performances des commandes INSERT pour les tables comportant une colonne ayant la propriété INDENTITY. Écrase l’activation de l’indicateur de trace 272 (voir § 5 du présent chapitre).

LAST_QUERY_PLAN_STATS = { ON | OFF } : permet d’activer la collecte du dernier plans réel d’exécution de chaque requête encore en cache. par défaut OFF. Ce plan est visible à travers la dmf sys.dm_exec_query_plan_stats (plan_handle). Équivalent de la trace 2451.

LIGHTWEIGHT_QUERY_PROFILING = { ON | OFF } : permet de désactiver l’infrastructure de profilage de requête léger (LWP)qui  fournit les données de performances de requête plus efficacement que les mécanismes de profilage standard. Par défaut ON.

PARAMETER_SNIFFING = { ON | OFF | PRIMARY} : équivalent de l’indicateur de requête « OPTIMIZE FOR UNKNOWN » vu § 4.3 du présent chapitre.

QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY} : permet d’activer les correctifs d’optimisation apportés par les différents patches (CU) ultérieurs au niveau RTM. Par défaut OFF. Voir, dans le présent chapitre, le § 1.3 et le drapeau de trace 4199 (§ 5).

XTP_PROCEDURE_EXECUTION_STATISTICS = { ON | OFF } : permet d’activer la collecte des statistiques sur les procédures stockées compilées en mode natif qui apparaitrons dans la dmv dm_exec_procedure_stats. Par défaut OFF.

XTP_QUERY_EXECUTION_STATISTICS = { ON | OFF } : permet d’activer la collecte des statistiques sur les requêtes présentes dans les procédure stockées compilées en mode natif qui apparaitrons dans la dmv dm_exec_query_stats. Par défaut OFF.

Les autres paramètres ne concernant pas les performances, nous ne les décrivons pas.

6.3 – Suppression de(s) plan(s) du cache

Une syntaxe particulière de cette commande permet de supprimer tous les plans d’exécution de requête de la base du cache, ou bien un seul identifié par son « handle » :

 

7 – Le gourneur de ressources

Le gouverneur de ressources disponible unqiuement dans l’édition Enterprise, permet de spécifier des limites de quantité de ressources processeur, d’E/S disque physiques et de mémoire appelés « pool de ressource » par des profils appelé « groupe de charge de travail » a définir sur des utilisateurs par le biais d’une règle de classification.

Le pool de ressources représente les limites physiques (utilisation du CPU, des E/S disques et de la mémoire) appliquées aux ressources disponibles pour l’instance SQL Server. Il existe en permanence deux pools de ressources créés à l’installation de SQL Server :

  • le pool interne : regroupe les processus systèmes de SQL Server, qui ne sont ajamais limités et n’est pas modifiable.
  • le pool par défaut : est le pool de tous les utilisateurs du moteur SQL à défaut de ceux qui seront enrôlés dans un autre poll spécifiquement créé. Il est modifiabele en limitation.

Vous pouvez créer en sus autant de pools « utilisateurs » que vous le souhaitez.

Un groupe de charges de travail contient les limites logique d’exécution des requêtes. Il est affectable à un pool de ressources.
Il existe en permanence deux groupes de charge de travail affectés aux deux pool de resources (interne et défaut). Seul le groupe de charge de travail par défaut peut être modifié

La règle de classification affecte les sessions entrantes à un groupe de charges de travail en fonction des caractéristiques de la session. Vous pouvez adapter la logique de classification en entrant une fonction définie par l’utilisateur, appelée fonction de classifieur. Resource Governor prend en charge également une fonction classifiante définie par l’utilisateur afin d’implémenter ces règles.

Syntaxe des commandes :

Syntaxe de la commande CREATE RESOURCE POOL (gouverneur de ressources MS SQL Server)

Syntaxe de la commande CREATE WORKLOAD GROUP (gouverneur de ressources MS SQL Server)

Ces objets ne sont réellement pris en compte par le gouverneur de ressources que lorsqu’est appliquée la commande  :

Exemple de création d’une fonction classifiante et application à un groupe de charge de nom « WG_REPORT_LOW » :

Exemple 17 – création d’une fonction classifiante
et application dans le gouverneur de ressources.

 

8 – Réglages internes de l’optimiseur

Il n’existe pas en théorie de réglage particulier de l’optimiseur autre que les drapeaux de trace vu au §5. Mais il existe trois commandes non documentées pour s’amuser à faire des tests…

ATTENTION : ces règlages ne sont pas documentés et dans la plupart des cas conduirait à la création de plan de reuête désastreux sauf dans des conditions très partriculières…

DBCC SETCPUWEIGHT (value) applique un multiplicateur sous forme de réel pour modifier le poids de calcul du CPU.

DBCC SETIOWEIGHT (value) applique un multiplicateur sous forme de réel pour modifier le poids de calcul des disques.

Par défaut ces paramètres sont valuées à 1.

DBCC SHOWWEIGHTS : afciche les valeurs de ces deux indicateurs (nécessite l’activation du drapeau de trace 3604 pour affichage dans le console). Exemple :

Notez que les drapeaux de trace non documentés 2387 et 2399 changent légèrement ces valeurs dynamiquement pour certaines opérations.

L’optimiseur cesse d’analyser les différentes possibilités de requête après avoir étudié environ 500 000 opérations élémentaires (Optimizer Timeout). Il en résulte non pas un plan optimal, mais un plan acceptable. On peut aller plus loin dans l’analyse en activant certains drapeaux de trace.. Les drapeaux de trace 2301 et 8780 non documentés permettnt de dépasser cette limite. Attention de ne jamais les laisser en production au niveau global. Acceptable pour quelques requêtes très particulières, mais à vos risques et périls…

Ce contenu a été publié dans Uncategorized, avec comme mot(s)-clé(s) , , , , , , , , , , , , , , , , , . Vous pouvez le mettre en favoris avec ce permalien.