Parameter sniffing
Concept
Gedrag van SQL Server-achtige engines die een plan compileren voor de parameterwaarden die tijdens het compileren worden gezien, en het gecachte plan daarna hergebruiken; dit kan slecht presteren bij sterk afwijkende parameterwaarden. Ook wel parameter sensitivity genoemd.
Beschrijving
Parameter sniffing is een gedrag van databasemotoren in de stijl van SQL Server waarbij een queryplan wordt gecompileerd voor de parameterwaarden die bij het compileren worden gezien, waarna het gecachte plan opnieuw wordt gebruikt. Het wordt ook parameter sensitivity genoemd. Het gedrag is op zichzelf geen fout: de engine bekijkt de huidige parameterwaarden tijdens het compileren of opnieuw compileren van een query en geeft ze door aan de query-optimizer, die dan een efficiënter uitvoeringsplan voor die waarden kan opstellen. De moeilijkheid ontstaat wanneer het gecachte plan later wordt gebruikt voor parameterwaarden die sterk afwijken van de oorspronkelijke, omdat een plan dat bij de ene waarde past, bij een andere waarde slecht kan presteren.
De documentatie van Microsoft over de verwerkingsarchitectuur van queries in SQL Server beschrijft parameter sniffing in een sectie over parameter sensitivity. Volgens die gids geldt het proces voor opgeslagen procedures, queries die via sp_executesql worden uitgevoerd, en voorbereide queries. Het heeft direct te maken met het hergebruik van plannen, omdat gecompileerde plannen in de plan cache worden opgeslagen en door latere uitvoeringen opnieuw kunnen worden gebruikt. De eerste set waarden die een compilatie veroorzaakt, bepaalt daarom het plan dat volgende aanroepen krijgen, en daarom kunnen de resultaten verschillen afhankelijk van welke aanroep het plan toevallig heeft gecompileerd.
De gids noemt één oplossing: de queryhint OPTION (RECOMPILE). Met deze hint behandelt de optimizer parameters en lokale variabelen als constanten op compileertijd, die in letterlijke waarden kunnen worden omgezet. De gebruikte waarden zijn die welke gelden vlak voordat de instructie wordt uitgevoerd, en dat zijn niet noodzakelijk de waarden die oorspronkelijk zijn doorgegeven. Dit kan helpen wanneer invoergegevens sterk variëren of scheef verdeeld zijn. De gids waarschuwt ook dat de hint de query bij elke uitvoering opnieuw compileert, wat kostbaar kan zijn, en adviseert het effect op de hele werklast te meten voordat de hint wordt toegepast. De gids verwijst bovendien naar een apart artikel over de optimalisatie Parameter Sensitive Plan voor verder lezen.
Een verwant detail uit dezelfde documentatie betreft lokale variabelen. Hun waarden worden niet op compileertijd gesnuffeld. In plaats daarvan schat de optimizer op basis van dichtheidsinformatie, of, wanneer er geen statistieken zijn, gebruikt hij vaste percentagegissingen voor gelijkheids- en bereikvergelijkingen. Dit onderscheid is belangrijk bij het diagnosticeren van een trage query, omdat een parameter en een lokale variabele tot verschillende schattingen en dus verschillende plannen kunnen leiden.
In de praktijk uit parameter sniffing zich als queries die voor sommige invoer snel en voor andere traag lopen, of als prestaties die veranderen nadat een plan is verwijderd en opnieuw is gecompileerd. De M365.FM-aflevering "Fix Bad Data Upstream with T-SQL Contracts" noemt parameter sniffing bij de veelvoorkomende fouten in grote organisaties, en beschrijft het als iets dat zich kan voordoen als willekeur. De aflevering presenteert plannen ook als een governancekwestie: als de vorm van plannen verandert, veranderen kosten en betrouwbaarheid mee, zodat plangedrag zich vertaalt naar kostenstabiliteit en operationeel risico.
De aflevering "Design Reliable and Resilient Azure Architectures" plaatst parameter sniffing in een probleemoplossingsworkflow voor Azure SQL Database. Ze raadt aan Intelligent Insights en Query Performance Insight in te schakelen, langlopende queries vast te leggen, uitvoeringsplannen te bekijken en ontbrekende indexen of parameter sniffing te identificeren. Verder noemt ze de automatische afstemmingsaanbevelingen van Azure SQL en indexsuggesties via Azure Advisor, samen met queryoptimalisatie, passende indexering, en het overwegen van het schalen van DTU's of vCores of een overstap naar Hyperscale.
Samengenomen behandelen de bronnen parameter sniffing als een afweging bij het cachen van plannen: het hergebruiken van een gecompileerd plan bespaart compilatiewerk, terwijl een plan dat op één parameterwaarde is afgestemd, mogelijk niet bij andere past. Typische beslissingen hierover betreffen het onderzoeken van uitvoeringsplannen, het controleren of de gegevensverdeling scheef is, en het afwegen van de compilatiekosten van opties zoals OPTION (RECOMPILE) tegen het voordeel van beter passende plannen.
Overzicht samengesteld met AI op basis van M365.FM-afleveringen en de feiten op deze pagina. Je kunt het verbeteren met de bewerkknop hieronder.
| Type | Concept |
|---|
Veelgestelde vragen
Vragen met AI zijn met AI geschreven op basis van de feiten op deze pagina.
Wat is parameter sniffing? (AI)
Het is het gedrag van SQL Server-achtige engines die een queryplan compileren voor de parameterwaarden die bij het compileren worden gezien, en het gecachte plan hergebruiken. Dit kan slecht presteren wanneer latere parameterwaarden sterk verschillen. Het wordt ook parameter sensitivity genoemd.
Hoe kan OPTION (RECOMPILE) problemen met parameter sniffing beïnvloeden? (AI)
Volgens de verwerkingsgids van Microsoft laat de hint de optimizer parameters en lokale variabelen behandelen als constanten op compileertijd, die in letterlijke waarden kunnen worden omgezet, wat kan helpen bij sterk variërende of scheef verdeelde gegevens. De query wordt bij elke uitvoering opnieuw gecompileerd, wat kostbaar kan zijn, dus het effect op de hele werklast moet eerst worden gemeten.
Worden lokale variabelen op dezelfde manier gesnuffeld als parameters? (AI)
Nee. De documentatie stelt dat waarden van lokale variabelen op compileertijd niet worden gesnuffeld. De optimizer schat ze in plaats daarvan op basis van dichtheidsinformatie of, zonder statistieken, gebruikt hij vaste percentagegissingen voor gelijkheids- en bereikvergelijkingen.
Community wiki
Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.
Experts en bedrijven voor dit onderwerp
Niemand heeft dit onderwerp nog opgegeven. Voeg het toe aan je profiel.
Beheerd door Mirko Peters (Microsoft MVP) · Laatst bijgewerkt: