Gibt es irgendwelche Tools, die speziell für Parameter-Sniffing-Probleme überwachen / erkennen, im Gegensatz zu denen, die Anfragen mit langer Laufzeit melden?
Ich habe gerade ein Problem mit dem Parameter-Sniffing bekommen. (Es war nicht zu ernst, da ein Bericht bei richtiger Cachespeicherung etwa zwei Minuten anstatt von ein paar Sekunden dauern würde und bei einer erneuten Kompilierung 30 Sekunden. Und da der Bericht normalerweise nur ein paar Mal pro Monat läuft, ist dies der Fall nicht wirklich ein Problem).
Da ich jedoch den Bericht geschrieben habe und ich wusste, was er getan hat, war ich neugierig und untersuchte SQL Profiler, und ich konnte einen Abschnitt im Abfrageplan sehen, in dem die Anzahl der geschätzten Zeilen 1, aber die tatsächliche Anzahl war von Reihen war mehrere hunderttausend.
Also, es ist mir aufgefallen, dass, wenn SQL diese Zahlen hat (oder zumindest diese Zahlen bekommen kann), dass es vielleicht einen Weg gibt, SQL zu verfolgen und zu berichten, welche Pläne signifikant out waren.
Sie haben da ein paar Fragen:
Gibt es Tools, die speziell auf Parameter-Sniffing-Probleme reagieren, anstatt solche, die langanhaltende Abfragen melden?
Um dies zu erfassen, müssen Sie den Prozedurcache überwachen, um herauszufinden, wann der Ausführungsplan einer Abfrage von "Gut" zu "Schlecht" wechselt. SQL Server 2008 hat dies erheblich vereinfacht, indem die Felder query_hash und query_plan_hash zu sys.dm_exec_query_stats hinzugefügt wurden. Sie können den aktuellen Abfrageplan für denselben query_hash mit früheren vergleichen und bei Änderungen die Anzahl der logischen Lesevorgänge oder die Menge der Arbeitszeit von der alten Abfrage zur neuen Abfrage vergleichen. Wenn es in die Höhe schießt, haben Sie möglicherweise ein Parameter-Sniffing-Problem.
Andererseits hat vielleicht jemand einen Index gelöscht oder den Code in einer aufgerufenen UDF oder einer Änderung in MAXDOP oder einer der Millionen Einstellungen, die das Verhalten des Abfrageplans beeinflussen, geändert.
Was Sie wünschen, ist ein einziges Dashboard, das die meisten ressourcenintensiven Abfragen in Aggregat anzeigt (weil Sie dieses Problem möglicherweise bei einer Abfrage haben, die extrem häufig aufgerufen wird, aber jedes Mal winzige Mengen an Ressourcen verbraucht) und Ihnen dann Änderungen anzeigt der Ausführungsplan im Laufe der Zeit plus Änderungen auf System- und Datenbankebene. Quest Foglight-Leistungsanalyse tut dies. (Ich habe für Quest gearbeitet, daher kenne ich das Produkt, aber ich schreibe hier nicht.) Beachten Sie, dass Quest ein separates Produkt verkauft, Foglight, das nichts mit Leistungsanalyse zu tun hat. Mir ist kein anderes Produkt bekannt, das auf diese Detailebene eingeht.
Ich konnte im Abfrageplan einen Abschnitt sehen, in dem die Anzahl der geschätzten Zeilen 1 war, aber die tatsächliche Anzahl der Zeilen mehrere Hunderttausend betrug.
Das ist nicht unbedingt ein Parameter-Sniffing - das könnte zum Beispiel eine schlechte Statistik oder eine Tabellenvariable sein. Um diese Art von Problem zu lösen, mag ich das kostenlose Tool SQL Sentry Plan Advisor . Auf der Registerkarte Top Operations werden Abweichungen zwischen geschätzten und tatsächlichen Zeilen hervorgehoben.
Nun, das ist nur für jeweils einen Plan, und Sie müssen den Plan zuerst kennen. Du willst das 24/7 machen, oder? Sicher tun Sie - aber es ist rechenintensiv. Der Prozedurcache kann riesig sein (ich habe Clients mit & gt; 100 GB Prozedurcache), und es ist alles nicht indizierte XML. Um geschätzte vs tatsächliche Zeilen zu vergleichen, müssen Sie das ganze XML zerlegen - und bedenken Sie, dass sich der Prozedurcache unter Last ständig ändern kann.
Was Sie wirklich wollen, ist ein Produkt, das sehr schnell den gesamten Prozedurcache in eine Datenbank laden, XML-Indizes darauf werfen und dann Schätzungen mit tatsächlichen Zeilen vergleichen kann. Ich kann mir vorstellen, dass ein Skript das macht, aber ich habe es noch nicht gesehen.
Sie sagten
"geschätzte Zeilen war 1, aber die tatsächliche Anzahl der Zeilen war mehrere hunderttausend."
Dies kann durch Tabellenvariablen verursacht werden, die keine Statistiken haben.
Das Erkennen von Parameter-Sniffern ist schwierig, Sie können jedoch feststellen, dass dies geschieht, indem Sie sp_updatestats ausführen. Wenn die Probleme verschwinden, ist es höchstwahrscheinlich Parameter-Sniffing. Wenn dies nicht der Fall ist, haben Sie andere Probleme, z. B. zu große Tabellenvariablen
Wir verwenden die Parametermaskierung jetzt konsequent (System wurde auf SQL Server 2000 entwickelt). Wir brauchen es nicht 99,9% der Zeit, aber die & lt; 0,1% rechtfertigen dies aufgrund des Benutzervertrauens und des damit verbundenen Supportaufwands.
Sie können eine Ablaufverfolgung einrichten, die den Abfragetext aller Batches / gespeicherten Prozeduren aufzeichnet, die die Dauer & gt; Ns.
Sie müssen natürlich N für Ihr System anpassen (und wahrscheinlich Regeln hinzufügen, um Stapeljobs auszuschließen, die selbst bei normaler Ausführung sehr lange dauern), aber dies sollte ermitteln, welche Abfragen die schlechteste Leistung bieten, und alle Anfragen aufzeichnen mit ihren Parametern), die ungewöhnlich lange Ausführungszeiten haben - möglicherweise das Ergebnis eines Parameter-Schnüffelproblems.
Siehe Erstellen einer SQL-Ablaufverfolgung mit T-SQL zum Erstellen einer Ablaufverfolgung mit T-SQL. Dies ergibt eine bessere Leistung als die Verwendung von SQL Profiler, da nur die Ereignisse erfasst werden, für die Traceereignisse festgelegt wurden (SQL Profiler erfasst Berichten zufolge alle Ereignisse und filtert diese dann in der Anwendung).
Tags und Links sql-server monitoring performance