Parameter-Sniffing (oder Spoofing) in SQL Server

Question

Vor einiger Zeit hatte ich eine Abfrage, die ich ziemlich oft für einen meiner Benutzer ausführte. Sie wurde immer noch weiterentwickelt und optimiert, aber schließlich stabilisierte sie sich und lief recht schnell, so dass wir daraus eine gespeicherte Prozedur erstellten.

So weit, so normal.

Die gespeicherte Prozedur war jedoch sehr langsam. Kein wesentlicher Unterschied zwischen der Abfrage und der Prozedur, aber die Geschwindigkeitsänderung war massiv.

(Hintergrund: Wir arbeiten mit SQL Server 2005.)

Ein freundlicher lokaler DBA (der nicht mehr hier arbeitet) warf einen Blick auf die gespeicherte Prozedur und sagte: "Parameter-Spoofing!" ( Editar: obwohl es anscheinend auch als "Parameter Sniffing" bekannt ist, was die geringe Anzahl von Google-Treffern erklären könnte, als ich versuchte, danach zu suchen).

Wir haben einen Teil der gespeicherten Prozedur in eine zweite abstrahiert, den Aufruf dieser neuen inneren Prozedur in die bereits vorhandene äußere Prozedur eingeschlossen, die äußere Prozedur aufgerufen und siehe da, sie war genauso schnell wie die ursprüngliche Abfrage.

Also, was ist los? Kann jemand Parameter-Spoofing erklären?

Bonuspunkte für

• Hervorhebung, wie man sie vermeiden kann
• Vorschläge zur Erkennung möglicher Ursachen
• alternative Strategien, z. B. Statistiken, Indizes, Schlüssel, zur Entschärfung der Situation zu diskutieren

Answer 1

Zu Ihrer Information - Sie müssen noch etwas anderes beachten, wenn Sie mit SQL 2005 und gespeicherten Prozessen mit Parametern arbeiten.

SQL Server kompiliert den Ausführungsplan der gespeicherten Prozedur mit dem ersten Parameter, der verwendet wird. Wenn Sie dies also ausführen:

usp_QueryMyDataByState 'Rhode Island'

Der Ausführungsplan wird am besten mit den Daten eines kleinen Staates funktionieren. Aber wenn sich jemand umdreht und läuft:

usp_QueryMyDataByState 'Texas'

Der Ausführungsplan, der für Daten in der Größe von Rhode-Island entwickelt wurde, ist bei Daten in der Größe von Texas möglicherweise nicht so effizient. Dies kann zu überraschenden Ergebnissen führen, wenn der Server neu gestartet wird, da der neu erstellte Ausführungsplan auf den zuerst verwendeten Parameter abzielt, der nicht unbedingt der beste ist. Der Plan wird erst dann neu kompiliert, wenn es einen wichtigen Grund dafür gibt, z. B. wenn die Statistiken neu erstellt werden.

Hier kommen Abfragepläne ins Spiel, und SQL Server 2008 bietet viele neue Funktionen, die DBAs dabei helfen, einen bestimmten Abfrageplan langfristig zu fixieren, unabhängig davon, welche Parameter zuerst aufgerufen werden.

Meine Sorge ist, dass Sie den Ausführungsplan zum Neukompilieren gezwungen haben, als Sie Ihre gespeicherte Prozedur neu aufgebaut haben. Sie haben ihn mit Ihrem Lieblingsparameter aufgerufen, und dann war er natürlich schnell - aber das Problem war vielleicht nicht der Stored Proc. Es könnte sein, dass die Stored Proc zu einem bestimmten Zeitpunkt mit einem ungewöhnlichen Satz von Parametern neu kompiliert wurde und somit einen ineffizienten Abfrageplan aufwies. Möglicherweise haben Sie nichts behoben und stehen beim nächsten Neustart des Servers oder der Neukompilierung des Abfrageplans vor demselben Problem.

Answer 2

Eine einfache Möglichkeit, dies zu beschleunigen, besteht darin, die Eingabeparameter gleich zu Beginn des Sprocs lokalen Parametern zuzuordnen, z. B.

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

    --Work w/ @SniffAvoidingLocalParameter in sproc body 
    -- ...

Answer 3

Ja, ich glaube, Sie meinen Parameter-Sniffing, eine Technik, mit der der SQL Server-Optimierer versucht, Parameterwerte/-bereiche herauszufinden, damit er den besten Ausführungsplan für Ihre Abfrage wählen kann. In einigen Fällen leistet SQL Server beim Parameter-Sniffing schlechte Arbeit und wählt nicht den besten Ausführungsplan für die Abfrage.

Ich glaube, dieser Blogartikel http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx hat eine gute Erklärung.

Es scheint, dass der DBA in Ihrem Beispiel die Option 4 gewählt hat, um die Abfrage in eine andere Sproc in einen separaten prozeduralen Kontext zu verschieben.

Sie hätten auch die Option mit Neukompilierung auf der ursprünglichen Sproc oder mit der optimieren für Option für den Parameter.

Answer 4

Parameter-Sniffing ist eine Technik, die SQL Server zur Optimierung des Abfrageausführungsplans für eine gespeicherte Prozedur verwendet. Wenn Sie die gespeicherte Prozedur zum ersten Mal aufrufen, prüft SQL Server die angegebenen Parameterwerte Ihres Aufrufs und entscheidet anhand der Parameterwerte, welche Indizes verwendet werden sollen.

Wenn also der erste Aufruf nicht sehr typische Parameter enthält, könnte SQL Server einen suboptimalen Ausführungsplan für die folgenden Aufrufe der gespeicherten Prozedur auswählen und speichern.

Sie können dieses Problem umgehen, indem Sie entweder

• mit WITH RECOMPILE
• Kopieren der Parameterwerte in lokale Variablen innerhalb der gespeicherten Prozedur und Verwendung der lokalen Variablen in Ihren Abfragen.

Ich habe sogar gehört, dass es besser ist, überhaupt keine gespeicherten Prozeduren zu verwenden, sondern die Abfragen direkt an den Server zu senden. Ich bin vor kurzem auf das gleiche Problem gestoßen, für das ich noch keine wirkliche Lösung habe. Bei einigen Abfragen hilft das Kopieren in lokale Variablen, um wieder den richtigen Ausführungsplan zu erhalten, bei anderen Abfragen verschlechtert sich die Leistung mit lokalen Variablen.

Ich muss noch genauer untersuchen, wie SQL Server (suboptimale) Ausführungspläne zwischenspeichert und wiederverwendet.

Answer 5

Meiner Erfahrung nach ist die beste Lösung für Parameter-Sniffing "Dynamic SQL". Zwei wichtige Dinge sind zu beachten: 1. Sie sollten Parameter in Ihrer dynamischen SQL-Abfrage verwenden 2. Sie sollten sp_executesql (und nicht sp_execute) verwenden, das den Ausführungsplan für jeden Parameterwert speichert