Parameter Sniffing ist eine häufig zu beobachtende Problemstellungen, mit denen sich Programmierer und Datenbankadministratoren beschäftigen (müssen). Mit Einführung der datenbankspezifischen Konfigurationselemente gibt es Möglichkeiten, dieses Problem auf Datenbankebene zu „optimieren“.
Inhaltsverzeichnis
Was ist Parameter Sniffing?
Wenn eine Abfrage von Microsoft SQL Server kompiliert wird, werden Prädikate in JOIN und WHERE-Bedingungen analysiert und Statistiken für die Ermittlung eines geeigneten Ausführungsplans (Optimierungsphase) verwendet. Die verwendeten Prädikate sind Bestandteil des Ausführungsplans! Werden Variablen statt Literale verwendet, speichert Microsoft SQL Server bei der ersten Ausführung der Abfrage den für die Variable verwendeten Wert als „Compiled Parameter“ ab.
Beispiel
Zunächst wird eine Tabelle erstellt; diese Tabelle verwendet für das Attribut [severity] einen Index.
SELECT *
INTO dbo.messages
FROM sys.messages;
GO
CREATE NONCLUSTERED INDEX ix_messages_severity ON dbo.messages (severity);
GO
Im Anschluss wird die Tabelle mit zwei unterschiedlichen Parameterwerten abgefragt:
-- Nested Loop Operation
SELECT *
FROM dbo.messages
WHERE severity = 12
ORDER BY
language_id,
message_id;
GO
-- Table Scan Operation
SELECT *
FROM dbo.messages
WHERE severity = 16
ORDER BY
language_id,
message_id;
GO
Die erste Abfrage verwendet für die Ausführung einen NESTED LOOP Operator für die Ausführung der Abfrage während die zweite Abfrage einen TABLE SCAN Operator verwendet, da deutlich mehr Datensätze an den Client geliefert werden.
Die unterschiedlichen Speicheranforderungen resultieren aus der SORT-Operation. Bei einem SORT-Operator handelt es sich um einen STOP-Operator, der zunächst die Daten empfangen muss um sie anschließend zu sortieren. Da bei der zweiten Abfrage deutlich mehr Datensätze geliefert wurden, ist die hohe Speicheranforderung – prinzipiell – gerechtfertigt.
Das obige Beispiel zeigt, wie bei der Verwendung von unterschiedlichen Prädikatswerten unterschiedliche Ausführungspläne mit unterschiedlichen Ressourcenanforderungen generiert und verwendet werden.
Wie verhält sich eine Abfrage, wenn statt eines Literals eine Variable verwendet wird?
Für das Beispiel mit Variablen wird die Abfrage in eine Stored Procedure gekapselt. Die Stored Procedure verwendet einen Parameter für die Eingabe eines Wertes für die [severity].
CREATE PROC dbo.GetMessages
@severity TINYINT
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM dbo.messages
WHERE severity = @severity
ORDER BY
language_id,
message_id;
END
GO
Zunächst wird die Stored Procedure mit dem Wert „12“ ausgeführt
EXEC dbo.GetMessages @severity = 12;
GO
Der Ausführungsplan wurde so gewählt, wie es optimal für eine kleine Datenmenge ist. Der Ausführungsplan ist identisch mit dem Ausführungsplan, der für die Abfrage mit dem Literal „12“ erstellt wurde.
Der Ausführungsplan für die Stored Procedure mit dem Wert „16“ stellt sich jedoch anders dar, als die Ausführung mit dem Literal!
Microsoft SQL Server hat den ERSTEN Ausführungsplan verwendet; dieses Prinzip der Wiederverwendung von Plänen ist gewünscht und führt dann zu den oben genannten Problemen. Zu diesem Thema habe ich bereits einen ausführlichen Artikel geschrieben.
Parameter Sniffing deaktivieren
Microsoft bietet verschiedene Möglichkeiten, das Problem von Parameter Sniffing zu umgehen. Jedoch basieren ALLE Möglichkeiten auf dem Umstand, dass SQL Server in der Kompilierphase der Abfrage nicht mehr auf das Histogramm zugreift, sondern den Density Vector verwendet.
Traceflag 4136
Die Verwendung von Traceflag 4136 wirkt sich auf ALLE Datenbanken einer Instanz aus, sofern das Traceflag global gesetzt wird. Tatsächlich ist es aber möglich, dieses Traceflag auch für die aktuelle Session zu verwenden.
-- Deaktivierung von Parameter Sniffing für aktuelle Session
DBCC TRACEON (4136);
GO
-- Deaktivierung von Parameter Sniffing für Instanz
DBCC TRACEON (4136, -1);
GO
Das Problem der Verwendung von Traceflags ist jedoch, dass es immer die Mitgliedschaft in der Serverrolle „sysadmin“ erfordert. Die Verwendung in Stored Procedures und/oder Applikationscode scheidet demnach häufig aus.
Verwendung von Variablen
Eine weitere Alternative ist es, innerhalb einer Prozedur eine weitere Variable zu verwenden, die anschließend als Prädikat verwendet wird. Dieses Verfahren habe ich in dem weiter oben verlinkten Artikel beschrieben.
CREATE OR ALTER PROC dbo.GetMessages
@severity TINYINT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @s TINYINT = @severity;
SELECT *
FROM dbo.messages
WHERE severity = @s
ORDER BY
language_id,
message_id;
END
GO
HINT „DISABLE_PARAMETER_SNIFFING“
Microsoft war sich des Berechtigungsproblems bewußt und hat mit der Version 2016 von SQL Server die Möglichkeit implementiert, gezielt für Abfragen mit Abfragehinweisen zu arbeiten.
CREATE OR ALTER PROC dbo.GetMessages
@severity TINYINT
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM dbo.messages
WHERE severity = @severity
ORDER BY
language_id,
message_id
OPTION (USE HINT('DISABLE_PARAMETER_SNIFFING'));
END
GO
Diese Option ermöglichte erstmals, im Code der Abfrage gezielt Parameter Sniffing zu deaktivieren, ohne administrative Berechtigungen zu erteilen!
Paramter Sniffing für alle Abfragen einer Datenbank deaktivieren
Alle oben gezeigten Möglichkeiten haben – aus Sicht einer Applikation – den Nachteil, dass sie entweder für die ganze Instanz, Session oder einzelne Abfrage gelten. Es kann sein, dass eine Applikation grundsätzlich auf Parameter Sniffing verzichten möchte?
Ich kann es mir zwar nur schwerlich vorstellen, dass eine generelle Deaktivierung wirklich benötigt wird aber es gibt verschiedene Szenarien, in denen dann nur noch einen gezielte Deaktivierung für die Datenbank hilft:
- Abfragen können nicht geändert werden, da sie im Code generiert werden
- Probleme mit veralteten Statistiken
- Anwendung ist schreiblastig und leidet unter dem „ASCENDING KEY“ Problem (<= SQL Server 2012)
Seit Version SQL Server 2016 gibt es die Möglichkeit, bestimmte Konfigurationselemente nicht mehr für Instanzen, sondern für Datenbanken zu konfigurieren. Soll für eine Datenbank die Option „Parameter Sniffing“ deaktiviert werden, reicht ein einfacher T-SQL-Befehl (optional natürlich auch die Konfiguration über die Eigenschaften einer Datenbank!) aus:
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
GO
Ob tatsächlich eine datenbankweite Deaktivierung sinnvoll ist, muss letztendlich der Entwickler der Software beurteilen. Mit Microsoft SQL Server 2014 wurde ein neuer Cardinal Estimator implementiert, der einige der typischen Szenarien, in denen Parameter Sniffing ein Problem sein, beseitigt.
Szenario für Deaktivierung
Ein typischen Szenario ist die Aktualisierung von Statistiken in der Nacht und über den Tag werden große Datenmengen in die betroffene Tabelle eingetragen. Die neuen Werte sind natürlich in der Statistik noch nicht berücksichtigt. Dieses Problem ist vor allen Dingen bei „fortlaufenden“ Indexwerten ein Problem gewesen.
Das oben verwendete Beispiel besitzt die folgende Datenverteilung:
DBCC SHOW_STATISTICS (N'dbo.messages', N'ix_messages_severity');
GO
Die Anzahl der Datensätze mit der [severity] = „15“ beträgt 11.242. Diese Datensätze werden nun mit der severity = „25“ in die Tabelle eingetragen. Statistiken werden aufgrund der geringen Menge nicht aktualisiert!
INSERT INTO dbo.messages
(
message_id,
language_id,
severity,
is_event_logged,
text
)
SELECT message_id,
language_id,
25,
is_event_logged,
text
FROM dbo.messages
WHERE severity = 15;
GO
Wird eine Abfrage mit dem Prädikatswert für die neu eingetragenen Datensätze ausgeführt, gab es mit SQL Server (<=2012) das typische Problem des „Ascending Keys“.
SELECT *
FROM dbo.messages
WHERE severity = 25
ORDER BY
language_id,
message_id
OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
GO
Mit SQL Server 2014 wurde dieses Problem entschärft, da der Cardinal Estimator einen neuen Algorithmus für die Berechnung der geschätzten Zeilen verwendet.
SELECT *
FROM dbo.messages
WHERE severity = 25
ORDER BY
language_id,
message_id
OPTION (USE HINT('FORCE_DEFAULT_CARDINALITY_ESTIMATION'));
GO
In dem Beispiel hilft weder der alte noch der neue CE, wird hier jedoch mit dem Density Vector (Deaktivierung von Parameter Sniffing) gearbeitet, sieht das Ergebnis deutlich besser aus!
Ob dieses Szenario eine generelle Deaktivierung für die komplette Datenbank gerechtfertigt, überlasse ich gerne dem Leser. Ich habe in meiner bisherigen Karriere als Berater für Microsoft SQL Server noch keinen Fall gesehen, der eine Deaktivierung rechtfertigt.
Ich finde die Deaktivierung auf Statement-Level viel interessanter; sie gibt dem Entwickler die Möglichkeit, gezielt bei Problemen mit Statistiken einzugreifen. Es bedarf in der Regel eines Kunsthandwerks-Hammers und nicht eines Vorschlaghammers!
Vielen Dank fürs Lesen
Bisher erschienene Artikel zu „Database Scoped Configuration“
Geplante Artikel
- OPTIMIZE_FOR_AD_HOC_WORKLOADS
- Identity Cache
- LEGACY_CARDINALITY_ESTIMATION
- INTERLEAVED_EXECUTION_TVF
- TSQL_SCALAR_UDF_INLINING
- DEFERRED_COMPILATION_TV
- VERBOSE_TRUNCATION_WARNINGS
