SQL Server 2016 : Les nouveautés

Comprendre l'option Parameter sniffing

TESTEZ LINKEDIN LEARNING GRATUITEMENT ET SANS ENGAGEMENT

Tester maintenant Afficher tous les abonnements
Vous allez comprendre comment gérer la détection de paramètres dans les procédures stockées. Aidez-vous de l'option Parameter sniffing.
06:24

Transcription

Voyons maintenant la dernière option qui s'appelle « Parameter Sniffing ». De quoi s'agit-il ? Lorsque j'exécute une requête, pour que SQL Serveur puisse calculer le plan d'exécution, donc, nous l'avons vu, il faut faire une estimation de la cardinalité du résultat. Parce que si je prends des contacts qui s'appellent Garcia ici, j'en obtient 104, si je veux des contacts qui s'appellent « Feragotto », j'en obtient un, et pour SQL Serveur, choisir une stratégie, s'il y a 104 lignes à retourner ou une seule, pas du tout la même chose. En tout cas, ça peut ne pas être la même chose. Si vous avez un index sur la colonne-nom, par exemple, ici, il est peut-être meilleur de parcourir la table toute entière. Pour retrouver parmi cette table les 104 possibilités, par contre si vous avez une seule possibilité, eh bien, il est plus intéressant de chercher dans l'index la référence, et ensuite d'aller chercher la bonne ligne dans la table, comme c'est le cas, ici. Mais pour les Garcia, s'il fallait chercher ici Garcia, trouver 104 références et faire 104 fois cette recherche, eh bien, ce serait peut-être plus coûteux que de parcourir la table. A ce moment-là, est-ce que le serveur change sa stratégie et dit : OK, j'abandonne l'index, je parcours la table. vous voyez donc, que selon le nombre de lignes affectées, la stratégie peut complètement changer. Mais que se passe-t-il si je fais une procédure stockée ? Donc, je vais supprimer ici ma procédure, entre parenthèses, cette syntaxe ici est nouvelle, voyez Drop, Procédure, pour supprimer la procédure, IF Existe, si je fais ça. La deuxième fois impossible de supprimer la procédure, car elle n'existe pas. J'essaie de supprimer quelque chose qui n'existe pas. Il me suffit maintenant d'ajouter juste après « if existe », pour dire, eh bien, voilà, je réagis : si elle existe, je la supprime, si non, je ne dis rien. Ça fait longtemps qu'on attendait ça quand même, bon. Maintenant, je recrée ma procédure, voyez, que j'ai mis quelque chose ici, en commentaire, pour vous en parler ensuite. Mais à la base donc, c'est en commentaire, il n'y a que cela dans la procédure. Cette procédure donc, cherche un contact, on passe le paramètre nom et ensuite on fait un select, avec le Nom est égal au paramètre. Donc je crée cette procédure, c'était déjà fait, et le plan d'exécution va être calculé au premier appel. Au premier appel il va y avoir ce qu'on appelle du « Parameter Sniffing ». On prend ce paramètre, et on va regarder quelle est sa valeur, par exemple Garcia et donc on va créer ce plan, vraiment par rapport au nombre de Garcia, qu'il y a dans la table. Et on va décider d'une stratégie, qui dépend, en fait, du premier paramètre, qu'on a envoyé du premier rappel à la procédure, depuis qu'elle a été créé ou que SQL serveur a démarré. Donc, j'exécute. Je regarde le plan d'exécution et j'ai bien ici, mon scan. Puisqu'on a 104, nombre de lignes estimées, 104. Mais « Feragotto », je l'exécute et je n'en ai qu'un, mais je regarde le plan d'exécution et j'ai exactement la même chose. Nombre de lignes estimées, vous le voyez un petit peu plus haut, 104, alors que le nombre réel de lignes sera de un. Parce que ce plan d'exécution maintenant est fixé en mémoire, la procédure stocké était compilée une fois pour toute et tous les appels vont estimer la cardinalité à 104. Même si c'est complètement différent. donc, il y a des moyens de gérer ce type de problématique. Mais à partir de 2016, vous en avez un supplémentaire, Vous l'aviez déjà auparavant sous la forme d'un « Drapeau de Trace », mais là, c'est beaucoup plus clair. Vous pouvez dire : « Je modifie ma base de données et je supprime le Parameter Sniffing ». Je vais supprimer ma procédure, la recrée, et maintenant j'appelle Garcia. J'en ai 104. Mais je regarde le plan d'exécution, et cette fois-ci, eh bien, il est parti sur une recherche d'index. son estimation de cardinalité est de, voyez juste en dessus, nombre de lignes estimées, ici, deux virgules, huit, pourquoi ? Tout à coup, deux virgules huit, parce que, en activant cette option, c'est-à-dire en désactivant le Parameter Sniffing, je force SQL Serveur pour cette base de données, à partir du principe que, on va plus du tout détecter l'appel de paramètre, mais on va optimiser cette requête pour une valeur moyenne, de distribution des noms dans la colonne « Nom ». En fait, je peux faire comme ça également, option « Optimize for Unknown » ça veux dire [inaudible] lève ça, voilà, ça veux dire « Optimize [inaudible]» ici, pour une valeur inconnue. Et la valeur inconnue, ça ne veut pas dire n'importe quoi, mais ça veut dire un calcul qui est fait sur la table, pour retourner la valeur moyenne du nombre de nom dans la table, ce qu'on appelle le « Density Vector », c'est-à-dire quelle est la densité moyenne des noms dans cette colonne. Et en désactivant le « Parameter Snifing » je vais dire à l'optimiseur, eh bien, lorsque tu as détecté un paramètre, ne le fais plus, prends la requête qui est à l'intérieur d'une procédure et optimise cette requête pour une valeur moyenne. Dans certains cas ça peut ne pas être bon, ne pas être optimale, mais on a une plus grande garantie que sur des requêtes qui sont faites sur des valeurs qui sont assez inégalement distribuées, et bien on a au moins la garantie qu’on va prendre une valeur moyenne et que dans tous les cas, on ne sera pas trop,trop loin de la réalité. Donc, cette option, est à désactiver, si vous voulez « Parameter Sniffing à OFF », sur des bases de données où vous avez beaucoup de problèmes d'estimation de cardinalité, dus à des données qui sont très imparfaitement distribuées, des colonnes où vous avez une valeur et tout à coup 10000 valeurs, et deux valeurs et 5000 valeurs, ce qui fait que sur des procédures stockées, eh bien, vous aurez des plans d'exécution qui ne conviennent pas à tous les coups.

SQL Server 2016 : Les nouveautés

Découvrez les nouveautés de SQL Server 2016. Voyez les options de configuration limitées aux bases de données, le chiffrage de données à partir des applications clientes, etc.

2h26 (27 vidéos)
Aucun commentaire n´est disponible actuellement

Votre formation est disponible en ligne avec option de téléchargement. Bonne nouvelle : vous ne devez pas choisir entre les deux. Dès que vous achetez une formation, vous disposez des deux options de consultation !

Le téléchargement vous permet de consulter la formation hors ligne et offre une interface plus conviviale. Si vous travaillez sur différents ordinateurs ou que vous ne voulez pas regarder la formation en une seule fois, connectez-vous sur cette page pour consulter en ligne les vidéos de la formation. Nous vous souhaitons un excellent apprentissage avec cette formation vidéo.

N'hésitez pas à nous contacter si vous avez des questions !