Freigeben über

  • Artikel

Problembehandlung bei unzureichender Abfrageleistung: Reduktion konstanter Ausdrücke und Ausdrucksauswertung während der Schätzung der Kardinalität

SQL Server wertet bestimmte konstante Ausdrücke frühzeitig aus, um die Abfrageleistung zu steigern. Dies wird als Reduktion konstanter Ausdrücke bezeichnet. Eine Konstante ist ein Transact-SQL-Literal wie z. B. 3, 'ABC', '2005-12-31', 1.0e3 oder 0x12345678.

Zur Kompilierzeit reduzierbare Ausdrücke

SQL Server verwendet die Reduktion konstanter Ausdrücke mit den folgenden Ausdruckstypen:

  • Arithmetische Ausdrücke wie 1+1, 5/3*2, die nur Konstanten enthalten.

  • Logische Ausdrücke wie 1=1 und 1>2 AND 3>4, die nur Konstanten enthalten.

  • Integrierte Funktionen, die von SQL Server zur Kompilierzeit reduziert werden können, einschließlich CAST und CONVERT. Im Allgemeinen gilt eine systeminterne Funktion als zur Kompilierzeit reduzierbar, wenn sie ausschließlich aus Eingaben besteht – ohne weitere kontextbezogene Informationen wie SET-Optionen, Spracheinstellungen, Datenbankoptionen oder Verschlüsselungsschlüssel. Nicht deterministische Funktionen sind nicht zur Kompilierzeit reduzierbar. Deterministische integrierte Funktionen sind bis auf einige Ausnahmen zur Kompilierzeit reduzierbar.

Eine Ausnahme sind große Objekte. Wenn der Ausgabetyp des Reduktionsprozesses ein großes Objekt ist (text, image, nvarchar(max), varchar(max) oder varbinary(max)), reduziert SQL Server den Ausdruck nicht zur Kompilierzeit.

Nicht zur Kompilierzeit reduzierbare Ausdrücke

Alle anderen Ausdruckstypen können nicht zur Kompilierzeit reduziert werden. Dabei handelt es sich insbesondere um folgende Ausdrücke:

  • Nicht konstante Ausdrücke, wie z. B. Ausdrücke, deren Ergebnisse vom Wert einer Spalte abhängig sind.

  • Ausdrücke, deren Ergebnisse von einer lokalen Variable bzw. einem lokalen Parameter abhängig sind, wie z. B. @x.

  • Nicht deterministische Funktionen.

  • Benutzerdefinierte Funktionen (Transact-SQL und CLR).

  • Ausdrücke, deren Ergebnisse von Spracheinstellungen abhängig sind.

  • Ausdrücke, deren Ergebnisse von SET-Optionen abhängig sind.

  • Ausdrücke, deren Ergebnisse von Serverkonfigurationsoptionen abhängig sind.

Beispiele für zur Kompilierzeit reduzierbare und nicht zur Kompilierzeit reduzierbare konstante Ausdrücke

Betrachten Sie die folgende Abfrage:

SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d 
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00

Wenn für diese Abfrage die PARAMETERIZATION-Datenbankoption nicht auf FORCED festgelegt wird (standardmäßig ist SIMPLE festgelegt), wird der Ausdruck 117.00 + 1000.00 ausgewertet und durch sein Ergebnis, 1117.00, ersetzt, bevor die Abfrage kompiliert wird. Die Vorteile dieser Reduktion des konstanten Ausdrucks sind folgende:

  • Der Ausdruck muss zur Laufzeit nicht mehrmals ausgewertet werden.

  • Der durch die Auswertung des Ausdrucks erhaltene Wert wird vom Abfrageoptimierer verwendet, um die Größe des Resultsets der Teilabfrage TotalDue > 117.00 + 1000.00 zu schätzen.

Wenn dbo.f jedoch eine skalare benutzerdefinierte Funktion ist, wird der Ausdruck dbo.f(100) nicht zur Kompilierzeit reduziert, da SQL Server keine benutzerdefinierten Funktionen zur Kompilierzeit reduziert, auch wenn sie deterministisch sind.

Auswertung von Ausdrücken zur Kompilierzeit zur Schätzung der Kardinalität

Außerdem werden bestimmte Ausdrücke, die zwar nicht zur Kompilierzeit ausgewertet werden, deren Argumente jedoch zur Kompilierzeit bekannt sind – unabhängig davon, ob es sich bei den Argumenten um Parameter oder Konstanten handelt – hinsichtlich der Größe ihrer Resultsets (Kardinalität) geschätzt. Dieser Vorgang ist ein Bestandteil des Abfrageoptimierers.

Insbesondere werden folgende integrierte Funktionen und spezielle Operatoren zur Kompilierzeit ausgewertet, wenn alle diesbezüglichen Eingaben bekannt sind: UPPER, LOWER, RTRIM, DATEPART (nur YY), GetDate, CAST und CONVERT.

Die folgenden Operatoren werden ebenfalls zur Kompilierzeit ausgewertet, wenn alle diesbezüglichen Eingaben bekannt sind:

  • Arithmetische Operatoren: +, -, *, /, unär -,

  • Logische Operatoren: AND, OR, NOT

  • Vergleichsoperatoren: <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL

Während der Kardinalitätsschätzung wertet der Optimierer keine anderen Funktionen oder Operatoren aus.

Beispiele für die Ausdrucksauswertung zur Kompilierzeit

Sehen Sie sich diese gespeicherte Prozedur von Transact-SQL an:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1;

Während der Optimierung der SELECT-Anweisung der Prozedur versucht der Optimierer, die erwartete Kardinalität des Resultsets für die Bedingung OrderDate > @d+1 auszuwerten. Der Ausdruck @d+1 kann nicht zur Kompilierzeit reduziert werden, da @d ein Parameter ist. Zum Zeitpunkt der Optimierung ist der Wert dieses Parameters jedoch bekannt. Dies ermöglicht, dass der Optimierer die Größe des Resultsets mit Genauigkeit schätzen kann, was zur Auswahl des optimalen Abfrageplans beiträgt.

Betrachten Sie nun ein ähnliches Beispiel, in dem jedoch @d+1 durch eine lokale Variable, @d2, ersetzt wird, und der Ausdruck statt in einer Abfrage in einer SET-Anweisung ausgewertet wird.

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END;

Wenn die SELECT-Anweisung MyProc2 in SQL Server optimiert wird, ist der Wert von @d2 nicht bekannt. Aus diesem Grund verwendet der Optimierer dann eine Standardschätzung für die Selektivität von OrderDate > @d2 (in diesem Fall 30 Prozent).

Bewährte Methoden: Verwenden der Reduktion konstanter Ausdrücke und der Auswertung von Ausdrücken zur Kompilierzeit zum Generieren optimaler Abfragepläne

Zum Generieren optimaler Abfragepläne sollten Sie sicherstellen, dass Sie Ihre Abfragen, gespeicherten Prozeduren und Batches so entwerfen, dass der Abfrageoptimierer die Selektivität der Abfragebedingungen auf der Grundlage der Statistiken zur Datenverteilung genau schätzen kann. Andernfalls verwendet der Optimierer eine Standardschätzung für die Selektivität (z. B. 30 Prozent im vorhergehenden Beispiel).

Um sicherzustellen, dass die Kardinalität vom Optimierer richtig eingeschätzt wird, sollten Sie zunächst überprüfen, dass die Datenbank-SET-Optionen AUTO_CREATE_STATISTICS und AUTO_UPDATE_STATISTICS auf ON festgelegt sind (dies ist die Standardeinstellung), oder manuell Statistiken für alle Spalten erstellen, auf die in einer Abfragebedingung verwiesen wird. Anschließend sollten Sie beim Entwerfen der Abfragebedingungen wenn möglich Folgendes beachten:

  • Vermeiden Sie lokale Variablen in Abfragen. Verwenden Sie stattdessen Parameter, Literale oder Ausdrücke in der Abfrage.

  • Begrenzen Sie die Verwendung von Operatoren und in Abfragen eingebetteten Operatoren mit Parametern auf die unter Compile-Time Expression Evaluation for Cardinality Estimation aufgeführten Operatoren und Funktionen.

  • Stellen Sie sicher, dass Ausdrücke, die nur Konstanten beinhalten, entweder zur Kompilierzeit auf eine Konstante reduziert oder zur Kompilierzeit ausgewertet werden können.

  • Wenn Sie eine lokale Variable zum Auswerten eines Ausdrucks in einer Abfrage verwenden müssen, überlegen Sie, ob Sie die Variable in einem anderen Bereich auswerten können. Beispielsweise könnte es sich lohnen, eine der folgenden Aktionen auszuführen:

    • Übergeben Sie den Wert der Variablen an eine gespeicherte Prozedur, die die auszuwertende Abfrage enthält, sodass die Abfrage den Parameter der gespeicherten Prozedur anstelle der lokalen Variablen verwendet.

    • Erstellen Sie eine Zeichenfolge, die eine teilweise auf dem Wert der lokalen Variablen basierende Abfrage enthält, und führen Sie dann die Zeichenfolge mithilfe von dynamischem SQL-Code aus (EXEC oder sp_executesql).

    • Parametrisieren Sie die Abfrage, und führen Sie sie mithilfe von sp_executesql aus. Übergeben Sie dabei den Wert der Variablen als Parameter an die Abfrage.