Parameter sniffing
Konzept
Verhalten von SQL-Server-ähnlichen Engines, die einen Plan für die Parameterwerte kompilieren, die zur Kompilierzeit gesehen wurden, und den zwischengespeicherten Plan wiederverwenden; das kann bei sehr unterschiedlichen Parameterwerten schlecht performen. Auch Parameter-Sensitivität genannt.
Beschreibung
Parameter Sniffing ist ein Verhalten von SQL-Server-ähnlichen Datenbank-Engines, bei dem ein Abfrageplan für die Parameterwerte kompiliert wird, die zur Kompilierzeit gesehen wurden, und der zwischengespeicherte Plan anschließend wiederverwendet wird. Es wird auch Parameter-Sensitivität genannt. Das Verhalten ist an sich kein Fehler: Die Engine untersucht die aktuellen Parameterwerte beim Kompilieren oder Neukompilieren einer Abfrage und übergibt sie an den Abfrageoptimierer, der dadurch für diese Werte einen effizienteren Ausführungsplan erstellen kann. Die Schwierigkeit tritt auf, wenn der zwischengespeicherte Plan später für Parameterwerte verwendet wird, die sich stark von den ursprünglichen unterscheiden, denn ein Plan, der zu einem Wert passt, kann für einen anderen Wert schlecht performen.
Microsofts Leitfaden zur Abfrageverarbeitungsarchitektur für SQL Server beschreibt Parameter Sniffing in einem Abschnitt zur Parameter-Sensitivität. Laut diesem Leitfaden gilt der Vorgang für gespeicherte Prozeduren, Abfragen, die über sp_executesql ausgeführt werden, und vorbereitete Abfragen. Er wirkt sich direkt auf die Wiederverwendung von Plänen aus, da kompilierte Pläne im Plan-Cache gespeichert und von späteren Ausführungen wieder aufgegriffen werden können. Die erste Wertemenge, die eine Kompilierung auslöst, bestimmt daher den Plan, den nachfolgende Aufrufe erhalten, weshalb Ergebnisse davon abhängen können, welcher Aufruf den Plan zufällig kompiliert hat.
Der Leitfaden nennt eine Abhilfe: den Abfragehinweis OPTION (RECOMPILE). Mit diesem Hinweis behandelt der Optimierer Parameter und lokale Variablen als Kompilierzeitkonstanten, die zu Literalen zusammengefasst werden können. Verwendet werden die Werte, die unmittelbar vor der Ausführung der Anweisung gelten, die nicht unbedingt den ursprünglich übergebenen Werten entsprechen. Das kann hilfreich sein, wenn die Eingabedaten stark variieren oder verzerrt verteilt sind. Der Leitfaden warnt außerdem, dass der Hinweis die Abfrage bei jeder Ausführung neu kompiliert, was teuer sein kann, und empfiehlt, die Wirkung auf die gesamte Arbeitslast zu messen, bevor man ihn einsetzt. Ferner verweist der Leitfaden auf einen separaten Artikel zur Optimierung Parameter Sensitive Plan für weiterführende Informationen.
Ein verwandtes Detail derselben Dokumentation betrifft lokale Variablen. Ihre Werte werden zur Kompilierzeit nicht gesnifft. Stattdessen schätzt der Optimierer anhand von Dichteinformationen, oder, wenn keine Statistiken vorliegen, verwendet er feste prozentuale Annahmen für Gleichheits- und Bereichsvergleiche. Diese Unterscheidung ist bei der Analyse einer langsamen Abfrage wichtig, da ein Parameter und eine lokale Variable zu unterschiedlichen Schätzungen und damit zu unterschiedlichen Plänen führen können.
In der Praxis zeigt sich Parameter Sniffing darin, dass Abfragen bei manchen Eingaben schnell und bei anderen langsam laufen, oder dass sich die Performance ändert, nachdem ein Plan entfernt und neu kompiliert wurde. Die M365.FM-Folge "Fix Bad Data Upstream with T-SQL Contracts" zählt Parameter Sniffing zu den häufigen Fehlern in Unternehmen und beschreibt es als etwas, das sich als Zufall tarnen kann. Die Folge behandelt Planformen außerdem als Governance-Thema: Ändern sich die Planformen, ändern sich damit Kosten und Zuverlässigkeit, sodass sich das Planverhalten in Kostenstabilität und betriebliches Risiko übersetzt.
Die Folge "Design Reliable and Resilient Azure Architectures" ordnet Parameter Sniffing in einen Fehlersuche-Workflow für Azure SQL Database ein. Sie schlägt vor, Intelligent Insights und Query Performance Insight zu aktivieren, lang laufende Abfragen zu erfassen, Ausführungspläne zu prüfen und fehlende Indizes oder Parameter Sniffing zu identifizieren. Außerdem werden die automatischen Abstimmungsempfehlungen von Azure SQL und Indexvorschläge über Azure Advisor genannt, ebenso Abfrageoptimierung, passende Indizierung sowie die Überlegung, DTUs oder vCores zu skalieren oder auf Hyperscale umzusteigen.
Insgesamt behandeln die Quellen Parameter Sniffing als Zielkonflikt beim Plan-Caching: Die Wiederverwendung eines kompilierten Plans spart Kompilieraufwand, während ein auf einen Parameterwert abgestimmter Plan für andere Werte ungeeignet sein kann. Typische Entscheidungen darin betreffen die Analyse von Ausführungsplänen, die Prüfung, ob die Datenverteilung verzerrt ist, und die Abwägung der Kompilierkosten von Optionen wie OPTION (RECOMPILE) gegen den Nutzen besser passender Pläne.
Überblick, mit KI aus M365.FM-Folgen und den Fakten dieser Seite zusammengestellt. Du kannst ihn unten über „Bearbeiten“ verbessern.
| Typ | Konzept |
|---|
Häufige Fragen
Mit KI markierte Fragen wurden mit KI aus den Fakten dieser Seite geschrieben.
Was ist Parameter Sniffing? (KI)
Es ist das Verhalten von SQL-Server-ähnlichen Engines, einen Abfrageplan für die zur Kompilierzeit gesehenen Parameterwerte zu erstellen und den zwischengespeicherten Plan wiederzuverwenden. Das kann schlecht performen, wenn spätere Parameterwerte stark abweichen. Es wird auch Parameter-Sensitivität genannt.
Wie kann OPTION (RECOMPILE) Probleme mit Parameter Sniffing beeinflussen? (KI)
Laut Microsofts Leitfaden zur Abfrageverarbeitung behandelt der Hinweis Parameter und lokale Variablen als Kompilierzeitkonstanten, die zu Literalen zusammengefasst werden können, was bei stark variierenden oder verzerrt verteilten Daten helfen kann. Die Abfrage wird bei jeder Ausführung neu kompiliert, was teuer sein kann, daher sollte die Wirkung zuerst auf die gesamte Arbeitslast gemessen werden.
Werden lokale Variablen auf dieselbe Weise gesnifft wie Parameter? (KI)
Nein. Die Dokumentation besagt, dass Werte lokaler Variablen zur Kompilierzeit nicht gesnifft werden. Der Optimierer schätzt stattdessen anhand von Dichteinformationen oder, ohne Statistiken, verwendet er feste prozentuale Annahmen für Gleichheits- und Bereichsvergleiche.
Community-Wiki
Oben die Kurzinfo, unten das Wissen der Community: ergänze praktische Tipps, Beispiele oder Korrekturen. Neue Beiträge werden zuerst geprüft, bewährte Mitglieder veröffentlichen direkt.
Experten und Firmen zu diesem Thema
Dieses Thema hat noch niemand im Profil angegeben. Trag es in deinem Profil ein.
Gepflegt von Mirko Peters (Microsoft MVP) · Zuletzt aktualisiert: