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“.

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.

Nested Loop Operation und minimale Speicheranforderung
Table Scan Operation und hohe Speicheranforderungen

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.

Blogartikel über Problematiken bei Parameter Sniffing

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
Wer kein T-SQL mag, kann die Konfiguration auch über die GUI ändern.

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
Aufgrund von nicht aktuellen Statistiken schätzt SQL Server, dass 1 Datensatz vorhanden ist

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
Nicht sehr gut – aber besser!

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!

Ohne „Parameter Sniffing“ wird der Density Vector verwendet

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