Verwenden von Statistiken zum Verbessern der Abfrageleistung

Gefilterte Statistiken können die Abfrageleistung für Abfragen verbessern, bei denen aus klar definierten Teilmengen von Daten ausgewählt wird. Gefilterte Statistiken verwenden ein Filterprädikat, um die Teilmenge von Daten auszuwählen, die in der Statistik enthalten ist. Sorgfältig entworfene gefilterte Statistiken können den Abfrageausführungsplan im Vergleich zu Tabellenstatistiken verbessern. Weitere Informationen zum Filterprädikat finden Sie unter CREATE STATISTICS (Transact-SQL). Weitere Informationen zum Zeitpunkt, zu dem gefilterte Statistiken erstellt werden sollten, finden Sie im Abschnitt Wann Statistiken erstellt werden sollten in diesem Thema. Eine Fallstudie finden Sie im Blogeintrag Verwenden von gefilterten Statistiken mit partitionierten Tabellen auf der SQLCAT-Website.

Wenn die AUTO_CREATE_STATISTICS-Option zum automatischen Erstellen von Statistiken aktiviert ist, erstellt der Abfrageoptimierer nach Bedarf Statistiken für einzelne Spalten im Abfrageprädikat, um Kardinalitätsschätzungen für den Abfrageplan zu verbessern. Diese Statistiken für einzelne Spalten werden für Spalten erstellt, die noch nicht über ein Histogramm in einem vorhandenen Statistikobjekt verfügen.

Mithilfe der folgenden Abfrage können Sie bestimmen, ob der Abfrageoptimierer Statistiken für eine Abfrageprädikatsspalte erstellt hat. Es werden die Katalogsichten sys.stats und sys.stats_columns abgefragt, um den Namen des Datenbankobjekts, den Spaltennamen und den Statistiknamen aller Spalten, die über Statistiken für einzelne Spalten verfügen, zurückzugeben. Wenn der Abfrageoptimierer durch die Verwendung der AUTO_CREATE_STATISTICS-Option Statistiken für einzelne Spalten erstellt, beginnt der Name der Statistik mit _WA.

USE AdventureWorks;
GO
SELECT OBJECT_NAME(s.object_id) AS object_name,
    COL_NAME(sc.object_id, sc.column_id) AS column_name,
    s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
    ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

Durch die AUTO_CREATE_STATISTICS-Option wird nicht festgelegt, ob Statistiken für Indizes erstellt werden. Durch diese Option werden auch keine gefilterten Statistiken generiert. Sie gilt ausschließlich für Statistiken für einzelne Spalten der gesamten Tabelle.

Wenn die AUTO_UPDATE_STATISTICS-Option zur automatischen Aktualisierung von Statistiken aktiviert ist, stellt der Abfrageoptimierer fest, wann Statistiken veraltet sein könnten, und aktualisiert diese Statistiken, sobald sie von einer Abfrage verwendet werden. Statistiken sind veraltet, wenn die Datenverteilung in der Tabelle oder indizierten Sicht durch INSERT-, UPDATE-, DELETE- oder MERGE-Vorgänge geändert wurde. Der Abfrageoptimierer stellt fest, wann Statistiken veraltet sein könnten, indem er die Anzahl der Datenänderungen seit der letzten Statistikaktualisierung ermittelt und sie mit einem Schwellenwert vergleicht. Der Schwellenwert basiert auf der Anzahl von Zeilen in der Tabelle oder indizierten Sicht.

Bevor der Abfrageoptimierer eine Abfrage kompiliert und einen zwischengespeicherten Abfrageplan ausführt, sucht er nach veralteten Statistiken. Vor dem Kompilieren einer Abfrage ermittelt der Abfrageoptimierer anhand der Spalten, Tabellen und indizierten Sichten im Abfrageprädikat, welche Statistiken veraltet sein könnten. Vor dem Ausführen eines zwischengespeicherten Abfrageplans überprüft das Database Engine (Datenbankmodul), ob der Abfrageplan auf aktuelle Statistiken verweist.

Die AUTO_UPDATE_STATISTICS-Option gilt für Statistikobjekte, die für Indizes, einzelne Spalten in Abfrageprädikaten und mit der CREATE STATISTICS-Anweisung generierte Statistiken erstellt wurden. Diese Option gilt auch für gefilterte Statistiken.

Die AUTO_CREATE_STATISTICS-Option zum automatischen Erstellen von Statistiken und die AUTO_UPDATE_STATISTICS-Option zur automatischen Aktualisierung von Statistiken sind standardmäßig aktiviert. Für die meisten Benutzerdatenbanken empfiehlt sich die Verwendung der Standardeinstellung. Sie können die aktuellen Werte dieser Optionen für alle Benutzerdatenbanken mithilfe der folgenden SELECT-Anweisung anzeigen:

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;
GO

Im folgenden Beispiel werden AUTO_CREATE_STATISTICS und AUTO_UPDATE_STATISTICS für die AdventureWorks2008R2-Datenbank aktiviert:

USE AdventureWorks2008R2;
GO
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE AdventureWorks2008R2
    SET AUTO_UPDATE_STATISTICS ON;
GO

Weitere Informationen zum Festlegen dieser Statistikoptionen finden Sie unter ALTER DATABASE SET-Optionen (Transact-SQL).

Wenn AUTO_UPDATE_STATISTICS aktiviert ist, können Sie das datenbankweite Aktualisierungsverhalten für Statistiken außer Kraft setzen und automatische Statistikaktualisierungen je nach den Anforderungen Ihrer Anwendung für einzelne Tabellen, Indizes oder Spalten deaktivieren. Wenn AUTO_UPDATE_STATISTICS aktiviert ist, können Sie automatische Statistikaktualisierungen für eine Tabelle, einen Index oder eine Spalte auf folgende Weisen deaktivieren und erneut aktivieren:

• Verwenden der gespeicherten Systemprozedur sp_autostats. Hierdurch können Sie Statistikaktualisierungen für eine Tabelle oder einen Index deaktivieren und erneut aktivieren.

• Angeben der NORECOMPUTE-Option mit der UPDATE STATISTICS-Anweisung. Um Statistikaktualisierungen wieder zu aktivieren, führen Sie UPDATE STATISTICS ohne die NORECOMPUTE-Option erneut aus.

• Angeben der NORECOMPUTE-Option mit der CREATE STATISTICS-Anweisung. Um Statistikaktualisierungen wieder zu aktivieren, entfernen Sie die Statistiken mit DROP STATISTICS und führen dann CREATE STATISTICS ohne die NORECOMPUTE-Option aus.

• Angeben der STATISTICS_NORECOMPUTE-Option mit der CREATE INDEX-Anweisung. Um Statistikaktualisierungen wieder zu aktivieren, können Sie ALTER INDEX mit STATISTICS_NORECOMPUTE = OFF ausführen.

Wenn AUTO_UPDATE_STATISTICS deaktiviert ist, können für einzelne Tabellen, Indizes oder Spalten keine automatischen Aktualisierungen aktiviert werden. Durch das erneute Aktivieren von automatischen Statistikaktualisierungen wird das mit der AUTO_UPDATE_STATISTICS-Option angegebene Verhalten wiederhergestellt. Wenn die AUTO_UPDATE_STATISTICS-Option deaktiviert ist, werden keine Statistikaktualisierungen ausgeführt.

Statistikaktualisierungen können entweder synchron (Standard) oder asynchron sein. Bei synchronen Statistikaktualisierungen werden Abfragen immer anhand aktueller Statistiken kompiliert und ausgeführt. Wenn Statistiken veraltet sind, wartet der Abfrageoptimierer auf aktualisierte Statistiken, bevor er die Abfrage kompiliert und ausführt. Bei asynchronen Statistikaktualisierungen werden Abfragen anhand vorhandener Statistiken kompiliert, auch wenn diese veraltet sind. Der Abfrageoptimierer könnte einen suboptimalen Abfrageplan auswählen, wenn die Statistiken beim Kompilieren der Abfrage veraltet sind. Wenn Abfragen nach dem Ausführen asynchroner Aktualisierungen kompiliert werden, hat dies den Vorteil, dass für die Abfragen aktualisierte Statistiken verwendet werden.

Mit der datenbankweiten AUTO_UPDATE_STATISTICS_ASYNC-Option für die asynchrone Statistikaktualisierung wird festgelegt, ob der Abfrageoptimierer die synchrone oder asynchrone Statistikaktualisierung verwendet. Die Option für die asynchrone Statistikaktualisierung ist standardmäßig deaktiviert, sodass der Abfrageoptimierer Statistiken synchron aktualisiert. Die AUTO_UPDATE_STATISTICS_ASYNC-Option gilt für Statistikobjekte, die für Indizes, einzelne Spalten in Abfrageprädikaten und mit der CREATE STATISTICS-Anweisung generierte Statistiken erstellt wurden.

Mit dem folgenden Befehl können Sie die Option für die asynchrone automatische Aktualisierung für alle Datenbanken anzeigen:

SELECT name AS "Name", 
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;
GO

Im folgenden Szenario sind synchrone Statistiken geeignet:

• Sie führen Vorgänge aus, durch die die Verteilung der Daten geändert wird; hierzu gehören z. B. das Abschneiden einer Tabelle oder das Ausführen einer Massenaktualisierung für einen großen Prozentsatz von Zeilen. Wenn Sie die Statistiken nach Ende dieses Vorgangs nicht aktualisieren, können Sie mithilfe synchroner Statistiken sicherstellen, dass die Statistiken aktuell sind, bevor Sie Abfragen auf der Basis der geänderten Daten ausführen.

In den folgenden Szenarien empfiehlt sich die Verwendung asynchroner Statistiken, um besser vorhersagbare Antwortzeiten für Abfragen zu erzielen:

• Häufig werden von der Anwendung die gleichen Abfragen, ähnliche Abfragen bzw. ähnliche zwischengespeicherte Abfragepläne ausgeführt. Bei Verwendung asynchroner Statistikaktualisierungen können die Antwortzeiten für Abfragen vorhersagbarer sein als bei synchronen Statistikaktualisierungen, weil der Abfrageoptimierer eingehende Abfragen direkt ausführen kann, ohne auf aktuelle Statistiken zu warten. Dadurch wird verhindert, dass sich einige Abfragen verzögern und andere nicht. Weitere Informationen zur Suche nach ähnlichen Abfragen finden Sie unter Suchen und Optimieren von ähnlichen Abfragen mit Abfrage und Abfrageplanhashes.

• In der Anwendung sind Timeouts bei Clientanforderungen aufgetreten, die dadurch verursacht werden, dass mindestens eine Abfrage auf aktualisierte Statistiken wartet. In einigen Fällen kann das Warten auf synchrone Statistiken dazu führen, dass Anwendungen mit kurzen Timeouts einen Fehler erzeugen.

Beim Database Engine (Datenbankmodul)-Optimierungsratgeber handelt es sich um ein Tool zum Analysieren der Leistungsauswirkungen, die Arbeitsauslastungen auf eine oder mehrere Datenbanken haben können. Er gibt Empfehlungen zur Leistungsverbesserung (z. B. welcher Index erstellt werden könnte) und schlägt möglicherweise vor, mithilfe von CREATE STATISTICS Abfrageoptimierungsstatistiken zu erstellen. Sie sollten dieser Empfehlung folgen. Weitere Informationen zum Database Engine (Datenbankmodul)-Optimierungsratgeber finden Sie unter Optimieren des physischen Datenbankentwurfs.

Wenn ein Abfrageprädikat mehrere Spalten mit spaltenübergreifenden Beziehungen und Abhängigkeiten enthält, könnte der Abfrageplan durch Statistiken für mehrere Spalten optimiert werden. Statistiken für mehrere Spalten enthalten spaltenübergreifende Korrelationsstatistiken, so genannte Dichten, die in Statistiken für einzelne Spalten nicht verfügbar sind. Durch Dichten können Kardinalitätsschätzungen verbessert werden, wenn Abfrageergebnisse von Datenbeziehungen zwischen mehreren Spalten abhängig sind.

Wenn sich die Spalten bereits im gleichen Index befinden, ist das Statistikobjekt für mehrere Spalten bereits vorhanden und muss nicht manuell erstellt werden. Wenn sich die Spalten noch nicht im gleichen Index befinden, können Sie Statistiken für mehrere Spalten erstellen, indem Sie einen Index für die Spalten anlegen oder die CREATE STATISTICS-Anweisung verwenden. Zur Verwaltung eines Indexes werden mehr Systemressourcen benötigt als zur Verwaltung eines Statistikobjekts. Wenn die Anwendung keinen Index für mehrere Spalten erfordert, können Sie Systemressourcen sparen, indem Sie das Statistikobjekt erstellen, ohne den Index zu generieren.

Wenn Statistiken für mehrere Spalten erstellt werden, wirkt sich die Reihenfolge der Spalten in der Statistikobjektdefinition darauf aus, wie effektiv die Dichten beim Erstellen von Kardinalitätsschätzungen sind. Im Statistikobjekt werden Dichten für jedes Präfix von Schlüsselspalten in der Statistikobjektdefinition gespeichert. Weitere Informationen zu Dichten finden Sie unter DBCC SHOW_STATISTICS (Transact-SQL).

Zum Erstellen von Dichten, die für Kardinalitätsschätzungen hilfreich sind, müssen die Spalten im Abfrageprädikat einem der Spaltenpräfixe in der Statistikobjektdefinition entsprechen. Im Folgenden wird beispielsweise aus den Spalten LastName, MiddleName und FirstName ein Objekt für eine Statistik für mehrere Spalten erstellt.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.stats
    WHERE name = 'LastFirst'
    AND object_ID = OBJECT_ID ('Person.Person'))
DROP STATISTICS Person.Person.LastFirst;
GO
CREATE STATISTICS LastFirst ON Person.Person (LastName, MiddleName, FirstName);
GO

In diesem Beispiel verfügt das Statistikobjekt LastFirst über Dichten für die folgenden Spaltenpräfixe: (LastName), (LastName, MiddleName) und (LastName, MiddleName, FirstName). Für (LastName, FirstName) ist keine Dichte verfügbar. Wenn in der Abfrage LastName und FirstName ohne MiddleName verwendet werden, ist die Dichte für Kardinalitätsschätzungen nicht verfügbar.

Wenn der Abfrageoptimierer Statistiken für einzelne Spalten und Indizes erstellt, berechnet er Statistiken für die Werte sämtlicher Zeilen. Wenn bei Abfragen aus einer Teilmenge von Zeilen ausgewählt wird und diese Teilmenge über eine eindeutige Datenverteilung verfügt, können Abfragepläne durch gefilterte Statistiken verbessert werden. Sie können gefilterte Statistiken erstellen, indem Sie die CREATE STATISTICS-Anweisung mit der WHERE-Klausel verwenden, um den Filterprädikatausdruck zu definieren.

Durch die Verwendung von AdventureWorks2008R2 beispielsweise gehört jedes Produkt in der Production.Product-Tabelle zu einer von vier Kategorien in der Production.ProductCategory-Tabelle: Fahrräder, Bauteile, Bekleidung und Zubehör. Jede Kategorie verfügt über eine andere Datenverteilung für das Gewicht: Die Gewichte der Fahrräder reichen von 13,77 bis 30,0, die Gewichte der Bauteile reichen von 2,12 bis 1050,00 mit einigen NULL-Werten, die Gewichte der Bekleidung sind alle NULL, und die Gewichte des Zubehörs sind ebenfalls NULL.

Bei den Fahrrädern liefern gefilterte Statistiken dem Abfrageoptimierer zu allen Fahrradgewichten genauere Statistikdaten und können die Abfrageplanqualität im Vergleich zu Tabellenstatistiken oder nicht vorhandenen Statistiken für die Weight-Spalte verbessern. Die Spalte mit dem Fahrradgewicht eignet sich besonders für gefilterte Statistiken, jedoch weniger für einen gefilterten Index, wenn nur relativ wenige Suchen nach Gewichtsangaben ausgeführt werden. Die Leistungsvorteile, die gefilterte Indizes bei der Suche bieten, können die zusätzlichen Kosten für Wartung und Speicher, die mit der Implementierung eines gefilterten Indexes in der Datenbank verbunden sind, jedoch nicht aufwiegen.

Durch die folgende Anweisung wird die gefilterte BikeWeights-Statistik für alle Unterkategorien von Fahrrädern erstellt. Durch den gefilterten Prädikatausdruck werden Fahrräder definiert, indem alle Fahrradunterkategorien mit dem Vergleich Production.ProductSubcategoryID IN (1,2,3) aufgelistet werden. Das Prädikat kann den Kategorienamen für Fahrräder nicht verwenden, da er in der Production.ProductCategory-Tabelle gespeichert ist; alle Spalten im Filterausdruck müssen sich in der gleichen Tabelle befinden.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.stats
    WHERE name = 'BikeWeights'
    AND object_ID = OBJECT_ID ('Production.Product'))
DROP STATISTICS Production.Product.BikeWeights;
GO
CREATE STATISTICS BikeWeights
    ON Production.Product (Weight)
WHERE ProductSubcategoryID IN (1,2,3);
GO

Der Abfrageoptimierer kann die gefilterte Statistik für die BikeWeights verwenden, um den Abfrageplan für die folgende Abfrage zu verbessern, bei der alle Fahrräder ausgewählt werden, deren Gewicht größer ist als 25.

SELECT P.Weight AS Weight, S.Name AS BikeName
FROM Production.Product AS P
    JOIN Production.ProductSubcategory AS S 
    ON P.ProductSubcategoryID = S.ProductSubcategoryID
WHERE P.ProductSubcategoryID IN (1,2,3) AND P.Weight > 25
ORDER BY P.Weight;
GO

Wenn der Abfrageoptimierer aufgrund eines Fehlers oder eines anderen Ereignisses keine Statistiken erstellen kann, erstellt er den Abfrageplan ohne Verwendung von Statistiken. Der Abfrageoptimierer kennzeichnet die Statistik als nicht vorhanden und versucht beim nächsten Ausführen der Abfrage, die Statistik erneut zu generieren.

Fehlende Statistiken werden als Warnungen angegeben (Tabellenname als rot formatierter Text), wenn der Ausführungsplan einer Abfrage mithilfe von SQL Server Management Studio grafisch angezeigt wird. Weitere Informationen finden Sie unter Anzeigen von grafischen Ausführungsplänen (SQL Server Management Studio). Das Fehlen von Statistiken wird zudem angezeigt, wenn die Missing Column Statistics-Ereignisklasse mithilfe von SQL Server Profiler überwacht wird. Weitere Informationen finden Sie unter Fehler und Warnungen-Ereigniskategorie (Datenbankmodul).

Wenn Statistiken fehlen, führen Sie die folgenden Schritte aus:

• Überprüfen Sie, ob AUTO_CREATE_STATISTICS und AUTO_UPDATE_STATISTICS aktiviert sind.

• Stellen Sie sicher, dass die Datenbank nicht schreibgeschützt ist. Wenn die Datenbank schreibgeschützt ist, können vom Abfrageoptimierer keine Statistiken gespeichert werden.

• Erstellen Sie die fehlende Statistik mithilfe der CREATE STATISTICS-Anweisung.

Wenn die Antwortzeiten von Abfragen langsam oder nicht vorhersagbar sind, sollten Sie sicherstellen, dass Abfragen auf aktuelle Statistiken zugreifen, bevor Sie weitere Schritte zur Problembehandlung ausführen. Weitere Informationen zur Problembehandlung bei langsamen Abfragen finden Sie unter Checkliste für die Analyse langsam ausgeführter Abfragen.

Statistiken für aufsteigend oder absteigend sortierte Schlüsselspalten, z. B. IDENTITY-Spalten oder Spalten mit Echtzeit-Timestamps, können häufigere Statistikaktualisierungen erfordern, als sie vom Abfrageoptimierer ausgeführt werden. Durch INSERT-Vorgänge werden neue Werte an aufsteigend oder absteigend sortierte Spalten angefügt. Möglicherweise wurden zu wenige Zeilen hinzugefügt, um eine Statistikaktualisierung auszulösen. Wenn Statistiken nicht aktuell sind und bei der Abfrageausführung aus den zuletzt hinzugefügten Zeilen ausgewählt wird, weisen die aktuellen Statistiken keine Kardinalitätsschätzungen für diese neuen Werte auf. Dies kann zu ungenauen Kardinalitätsschätzungen und einer langsamen Abfrageleistung führen.

Eine Abfrage, die aus den letzten Bestelldaten auswählt, verfügt z. B. über ungenaue Kardinalitätsschätzungen, wenn die Statistiken nicht aktualisiert werden, um Kardinalitätsschätzungen für die letzten Bestelldaten einzuschließen.

Die Aktualisierung von Statistiken empfiehlt sich auch nach dem Durchführen von Wartungsvorgängen, durch die die Verteilung der Daten geändert wird; hierzu gehören z. B. das Abschneiden einer Tabelle oder das Ausführen einer Masseneinfügung für einen großen Prozentsatz von Zeilen. Dadurch lassen sich zukünftige Verzögerungen bei der Abfrageverarbeitung vermeiden, d. h., Abfragen müssen nicht auf automatische Statistikaktualisierungen warten.

Aktualisieren Sie Statistiken nicht nach Vorgängen wie dem Neuerstellen, Defragmentieren oder Neuorganisieren eines Indexes. Diese Vorgänge wirken sich nicht auf die Verteilung von Daten aus und haben keinen Einfluss auf die Statistik. Beachten Sie aber, dass der Abfrageoptimierer Statistiken aktualisiert, wenn mit ALTER INDEX REBUILD oder DBCC DBREINDEX ein Index für eine Tabelle oder Sicht neu erstellt wird. Dies ist ein Nebeneffekt der internen Implementierung dieser Vorgänge. Tatsächlich erforderlich ist die Aktualisierung der Statistiken nicht. Der Abfrageoptimierer führt nach einer Defragmentierung oder dem Neuorganisieren keine Statistikaktualisierung durch, da der Index bei diesen Vorgängen nicht gelöscht oder neu erstellt wird.

Um Kardinalitätsschätzungen für Ausdrücke zu verbessern, beachten Sie die folgenden Richtlinien:

• Vereinfachen Sie nach Möglichkeit Ausdrücke, in denen Konstanten enthalten sind. Der Abfrageoptimierer wertet nicht alle Funktionen und Ausdrücke mit Konstanten aus, bevor er Kardinalitätsschätzungen ermittelt. Vereinfachen Sie z. B. den Ausdruck ABS(-100) to 100.

• Wenn der Ausdruck mehrere Variablen verwendet, können Sie in Betracht ziehen, eine berechnete Spalte für den Ausdruck und dann Statistiken oder einen Index für die berechnete Spalte zu erstellen. Das Abfrageprädikat WHERE PRICE + Tax > 100 könnte beispielsweise eine bessere Kardinalitätsschätzung aufweisen, wenn Sie eine berechnete Spalte für den Ausdruck Price + Tax erstellen.

Um die Kardinalitätsschätzungen für Variablen und Funktionen zu verbessern, beachten Sie die folgenden Richtlinien:

• Wenn das Abfrageprädikat eine lokale Variable verwendet, könnte das Umschreiben der Abfrage sinnvoll sein, sodass sie statt einer lokalen Variablen einen Parameter verwendet. Der Wert einer lokalen Variablen ist nicht bekannt, wenn der Abfrageoptimierer den Abfrageausführungsplan erstellt. Wenn eine Abfrage auf einem Parameter basiert, verwendet der Abfrageoptimierer die Kardinalitätsschätzung für den ersten tatsächlichen Parameterwert, der an die gespeicherte Prozedur übergeben wird.

• Erwägen Sie die Verwendung einer Standardtabelle oder temporären Tabelle, in der die Ergebnisse der Tabellenwertfunktionen mit mehreren Anweisungen enthalten sind. Der Abfrageoptimierer erstellt keine Statistiken für Tabellenwertfunktionen mit mehreren Anweisungen. Bei dieser Vorgehensweise kann der Abfrageoptimierer Statistiken für die Tabellenspalten erstellen und sie zum Optimieren der Abfragepläne nutzen. Weitere Informationen zu Tabellenwertfunktionen mit mehreren Anweisungen finden Sie unter Funktionstypen.

• Standardtabellen oder temporäre Tabelle können auch als Ersatz für Tabellenvariablen verwendet werden. Der Abfrageoptimierer erstellt keine Statistiken für Tabellenvariablen. Bei dieser Vorgehensweise kann der Abfrageoptimierer Statistiken für die Tabellenspalten erstellen und sie zum Optimieren der Abfragepläne nutzen. Die Vorteile von temporären Tabellen und Tabellenvariablen müssen gegeneinander abgewogen werden. Tabellenvariablen, die in gespeicherten Prozeduren verwendet werden, verursachen weniger Neukompilierungen der gespeicherten Prozedur als temporäre Tabellen. Nicht bei allen Anwendungen wird die Leistung optimiert, wenn statt einer Tabellenvariablen eine temporäre Tabelle verwendet wird.

• Wenn eine gespeicherte Prozedur eine Abfrage enthält, die einen übergebenen Parameter verwendet, sollten Sie den Parameterwert innerhalb der gespeicherten Prozedur nicht ändern, bevor Sie ihn in der Abfrage verwenden. Die Kardinalitätsschätzungen für die Abfrage basieren auf dem übergebenen Parameterwert und nicht auf dem aktualisierten Wert. Damit der Parameterwert nicht geändert werden kann, können Sie die Abfrage so umschreiben, dass zwei gespeicherte Prozeduren verwendet werden.

  Durch die gespeicherte Prozedur Sales.GetRecentSales wird der Wert des @date-Parameters z. B. geändert, wenn @date is NULL gilt.

  USE AdventureWorks2008R2;
  GO
  IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetRecentSales;
  GO
  CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
  AS BEGIN
      IF @date is NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END;
  GO
  

  Wenn der erste Aufruf der gespeicherten Prozedur Sales.GetRecentSales für den @date-Parameter NULL übergibt, kompiliert der Abfrageoptimierer die gespeicherte Prozedur mit der Kardinalitätsschätzung für @date = NULL, obwohl das Abfrageprädikat nicht mit @date = NULL aufgerufen wird. Diese Kardinalitätsschätzung kann deutlich von der Anzahl der Zeilen im tatsächlichen Abfrageergebnis abweichen. Folglich könnte der Abfrageoptimierer einen suboptimalen Abfrageplan auswählen. Um dies zu vermeiden, können Sie die gespeicherte Prozedur wie folgt in zwei Prozeduren unterteilen:

  USE AdventureWorks2008R2;
  GO
  IF OBJECT_ID ( 'Sales.GetNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNullRecentSales (@date datetime)
  AS BEGIN
      IF @date is NULL
          SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
      EXEC Sales.GetNonNullRecentSales @date;
  END
  GO
  IF OBJECT_ID ( 'Sales.GetNonNullRecentSales', 'P') IS NOT NULL
      DROP PROCEDURE Sales.GetNonNullRecentSales;
  GO
  CREATE PROCEDURE Sales.GetNonNullRecentSales (@date datetime)
  AS BEGIN
      SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
      WHERE h.SalesOrderID = d.SalesOrderID
      AND h.OrderDate > @date
  END;
  GO
  

Um Kardinalitätsschätzungen für lokale Variablen zu verbessern, können Sie den OPTIMIZE FOR-Abfragehinweis oder den OPTIMIZE FOR UNKNOWN-Abfragehinweis mit RECOMPILE verwenden. Weitere Informationen finden Sie unter Abfragehinweise (Transact-SQL).

Bei einigen Anwendungen könnte es zu lange dauern, die Abfrage bei jeder Ausführung neu zu kompilieren. Der OPTIMIZER FOR-Abfragehinweis kann hilfreich sein, auch wenn Sie die RECOMPILE-Option nicht verwenden. Sie können der gespeicherten Sales.GetRecentSales-Prozedur z. B. eine OPTIMIZER FOR-Option hinzufügen, um ein bestimmtes Datum anzugeben. Im folgenden Beispiel wird der Sales.GetRecentSales-Prozedur die OPTIMIZE FOR-Option hinzugefügt.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ( 'Sales.GetRecentSales', 'P') IS NOT NULL
    DROP PROCEDURE Sales.GetRecentSales;
GO
CREATE PROCEDURE Sales.GetRecentSales (@date datetime)
AS BEGIN
    IF @date is NULL
        SET @date = DATEADD(MONTH, -3, (SELECT MAX(ORDERDATE) FROM Sales.SalesOrderHeader))
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderID
    AND h.OrderDate > @date
    OPTION ( OPTIMIZE FOR ( @date = '2004-05-01 00:00:00.000'))
END;
GO

Für einige Anwendungen sind die Abfrageentwurfsrichtlinien möglicherweise nicht geeignet, weil Sie die Abfrage nicht ändern können oder die Verwendung des RECOMPILE-Abfragehinweises zu viele Neukompilierungen verursacht. Sie können mithilfe der Planhinweislisten weitere Hinweise (z. B. USE PLAN) angeben, um das Abfrageverhalten zu steuern. Zur gleichen Zeit können Sie mit dem Hersteller klären, ob die Anwendung geändert wurde. Weitere Informationen zu Planhinweislisten finden Sie unter Optimieren von Abfragen in bereitgestellten Anwendungen mit Planhinweislisten.