Wie kann ich den durch Parameter-Sniffing verursachten Leistungsabfall gespeicherter SQL Server-Prozeduren beheben?

Leistungsverschlechterung gespeicherter SQL Server-Prozeduren aufgrund von Parameter-Sniffing

Parameter-Sniffing ist eine Optimierungstechnik, die von SQL Server verwendet wird, um die Abfrageleistung durch Auswahl zu verbessern ein Ausführungsplan basierend auf den Parameterwerten zum Zeitpunkt der ersten Kompilierung der gespeicherten Prozedur. In bestimmten Fällen kann das Parameter-Sniffing jedoch zu Leistungsproblemen führen.

Betrachten Sie die folgende gespeicherte Prozedur:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

Wenn diese gespeicherte Prozedur zum ersten Mal mit @MyDate als NULL ausgeführt wird, wird die Abfrage Der Optimierer generiert einen Ausführungsplan, der für diesen bestimmten Parameterwert optimiert ist. Wenn die gespeicherte Prozedur jedoch anschließend mit einem anderen Wert für @MyDate ausgeführt wird, kompiliert der Abfrageoptimierer möglicherweise keinen optimalen Ausführungsplan, was zu Leistungseinbußen führt.

Dieses Problem wird als „Fehler beim Parameter-Sniffing“ bezeichnet. . Selbst wenn im obigen Beispiel @MyDate tatsächlich NULL ist, wenn es in der gespeicherten Prozedur verwendet wird, bleibt der schlechte Ausführungsplan, der auf der Grundlage der ersten Kompilierung generiert wurde, in Kraft.

Um dieses Problem zu beheben, kann man den Parameter deaktivieren Sniffing durch Spoofing des Parameters:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

Dieser Ansatz erstellt eine Kopie des Eingabeparameters und verwendet diese Kopie für den Abfrageoptimierungsprozess, wodurch verzerrte Parameter verhindert werden Sniffing.

In SQL Server 2008 und höher kann das Parameter-Sniffing mit dem Hinweis OPTIMIZE FOR UNKNOWN optimiert werden:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
WITH RECOMPILE, OPTIMIZE FOR UNKNOWN
AS
    -- Do Something using @MyDate

Dieser Hinweis weist den Abfrageoptimierer an, einen Ausführungsplan basierend auf Unbekanntem zu generieren Parameterwerte, wodurch das Risiko von Leistungsproblemen im Zusammenhang mit Parameter-Sniffing eliminiert wird.

Das obige ist der detaillierte Inhalt vonWie kann ich den durch Parameter-Sniffing verursachten Leistungsabfall gespeicherter SQL Server-Prozeduren beheben?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!