Richtlinien für den Entwurf gefilterter Indizes
Ein gefilterter Index ist ein optimierter nicht gruppierter Index, der sich besonders für Abfragen eignet, bei denen aus einer fest definierten Teilmenge von Daten ausgewählt wird. Dieser verwendet ein Filterprädikat, um einen Teil der Zeilen in der Tabelle zu indizieren. Mit einem sorgfältig entworfenen gefilterten Index können im Vergleich zu Tabellenindizes die Abfrageleistung verbessert und der Aufwand für die Indexverwaltung und die Indexspeicherung reduziert werden.
Gefilterte Indizes können gegenüber Tabellenindizes folgende Vorteile bieten:
Verbesserte Abfrageleistung und Planqualität
Mit einem sorgfältig entworfenen gefilterten Index wird die Abfrageleistung und die Ausführungsplanqualität verbessert, da dieser kleiner ist als ein nicht gruppierter Tabellenindex und mit gefilterten Statistiken arbeitet. Die gefilterten Statistiken sind genauer als Tabellenstatistiken, da diese nur die Zeilen im gefilterten Index umfassen.
Reduzierter Aufwand bei der Indexverwaltung
Ein Index wird nur beibehalten, wenn DML-Anweisungen (Data Manipulation Language) die Daten im Index beeinflussen. Ein gefilterter Index reduziert im Vergleich zu einem nicht gruppierten Tabellenindex den Aufwand für die Indexverwaltung, da dieser kleiner ist und nur beibehalten wird, wenn die Daten im Index beeinflusst werden. Eine große Anzahl von gefilterten Indizes ist insbesondere dann von Vorteil, wenn diese Daten enthalten, die nur selten beeinflusst werden. Ebenso reduziert die geringere Indexgröße den Aufwand für die Aktualisierung der Statistiken, wenn ein gefilterter Index nur die häufig beeinflussten Daten enthält.
Reduzierter Aufwand bei der Indexspeicherung
Ein gefilterter Index kann den Speicherplatzbedarf von nicht gruppierten Indizes reduzieren, wenn ein Tabellenindex nicht erforderlich ist. Sie können einen nicht gruppierten Tabellenindex durch mehrere gefilterte Indizes ersetzen, ohne damit die Speicherplatzanforderungen wesentlich zu erhöhen.
Entwurfsaspekte
Wenn Sie effektive gefilterte Indizes entwerfen möchten, müssen Sie wissen, welche Abfragen von Ihrer Anwendung verwendet werden und wie diese mit Teilmengen Ihrer Daten in Beziehung stehen. Einige Beispiele für Daten mit fest definierten Teilmengen sind Spalten, die größtenteils nur NULL-Werte enthalten, Spalten mit heterogenen Wertekategorien und Spalten mit verschiedenen Wertebereichen. Die folgenden Entwurfsüberlegungen zeigen, wann ein gefilterter Index Vorteile gegenüber Tabellenindizes hat.
Gefilterte Indizes für Datenteilmengen
Wenn eine Spalte nur wenig relevante Werte für Abfragen aufweist, können Sie für die Teilmenge der Werte einen gefilterten Index erstellen. Wenn beispielsweise die Werte in einer Spalte größtenteils NULL sind und die Abfrage nur die Werte ungleich NULL berücksichtigt, können Sie für die Datenzeilen mit den Werten ungleich NULL einen gefilterten Index erstellen. Der resultierende Index ist kleiner und verursacht weniger Verwaltungsaufwand als ein nicht gruppierter Tabellenindex, der für dieselben Schlüsselspalten festgelegt wird.
Beispielsweise enthält die AdventureWorks2008R2-Datenbank eine Production.BillOfMaterials-Tabelle mit 2.679 Zeilen. Die EndDate-Spalte hat nur 199 Zeilen mit einem Wert ungleich NULL. Die anderen 2.480 Zeilen enthalten einen NULL-Wert. Der folgende gefilterte Index würde Abfragen abdecken, die die im Index definierten Spalten zurückgeben und die für EndDate nur Zeilen mit einem Wert ungleich NULL auswählen.
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithEndDate'
AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL ;
GO
Der gefilterte Index FIBillOfMaterialsWithEndDate ist für die folgende Abfrage gültig. Sie können den Abfrageausführungsplan anzeigen, um zu bestimmen, ob der Abfrageoptimierer den gefilterten Index verwendet hat. Informationen zum Anzeigen des Abfrageausführungsplans finden Sie unter Analysieren einer Abfrage.
SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
AND ComponentID = 5
AND StartDate > '01/01/2008' ;
GO
Weitere Informationen zum Erstellen von gefilterten Indizes und zum Definieren des Prädikatausdrucks für gefilterte Indizes finden Sie unter CREATE INDEX (Transact-SQL).
Gefilterte Indizes für heterogene Daten
Wenn eine Tabelle heterogene Datenzeilen enthält, können Sie einen gefilterten Index für eine oder mehrere Datenkategorien erstellen.
Zum Beispiel wird jedes Produkt, das in der Production.Product-Tabelle aufgelistet ist, einer ProductSubcategoryID zugewiesen, die wiederum den Produktkategorien Fahrräder, Bauteile, Bekleidung oder Zubehör zugeordnet wird. Diese Kategorien sind heterogen, da ihre Spaltenwerte in der Production.Product-Tabelle nicht eng zueinander in Beziehung stehen. Zum Beispiel besitzen Farbe, ReorderPoint, ListPrice, Gewicht, Klasse und Stil eindeutige Merkmale für jede Produktkategorie. Angenommen, es gibt häufig Abfragen für Zubehör mit den Unterkategorien 27-36. Sie können die Abfrageleistung für Zubehör verbessern, indem Sie einen gefilterten Index für die Unterkategorien von Zubehör erstellen.
Im folgenden Beispiel wird ein gefilterter Index für alle Produkte in den Unterkategorien von Zubehör in der Production.Product-Tabelle erstellt.
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIProductAccessories'
AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product (ProductSubcategoryID, ListPrice)
Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO
Der gefilterte FIProductAccessories-Index deckt die folgende Abfrage ab, da die Abfrageergebnisse
im Index enthalten sind und der Abfrageplan keine Basistabellensuche einschließt. Der Abfrageprädikatausdruck ProductSubcategoryID = 33 ist z. B. eine Teilmenge der gefilterten Indexprädikate ProductSubcategoryID >= 27 und ProductSubcategoryID <= 36, die Spalten ProductSubcategoryID und ListPrice im Abfrageprädikat sind beides Schlüsselspalten im Index, und der Name wird in der Blattebene des Index als einbezogene Spalte gespeichert.
SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO
Sichten gegenüber gefilterten Indizes
Eine Sicht ist eine virtuelle Tabelle, in der die Definition einer Abfrage gespeichert wird. Sie hat umfassendere Funktionen als ein gefilterter Index. Weitere Informationen zu Sichten finden Sie unter Grundlegendes zu Sichten und Szenarien für das Verwenden von Sichten. In der folgenden Tabelle werden einige der in Sichten zulässigen Funktionen mit denen von gefilterten Indizes verglichen.
Zulässig in Ausdrücken |
Sichten |
Gefilterte Indizes |
|---|---|---|
Berechnete Spalten |
Ja |
Nein |
Joins |
Ja |
Nein |
Mehrere Tabellen |
Ja |
Nein |
Einfache Vergleichslogik in einem Prädikat* |
Ja |
Ja |
Komplexe Logik in einem Prädikat ** |
Ja |
Nein |
* Einfache Vergleichslogik in einem Prädikat finden Sie unter Syntax einer WHERE-Klausel in CREATE INDEX.
** Komplexe Vergleichslogik in einem Prädikat finden Sie unter Syntax einer WHERE-Klausel für SELECT.
Sie können keinen gefilterten Index für eine Sicht erstellen. Der Abfrageoptimierer kann jedoch von einem für eine Tabelle definierten gefilterten Index profitieren, auf den in einer Sicht verwiesen wird. Der Abfrageoptimierer berücksichtigt einen gefilterten Index für eine Abfrage, die aus einer Sicht auswählt, wenn die Ergebnisse der Abfrage korrekt sind. Im folgenden Beispiel werden eine Sicht mit Startdaten nach dem 1. April 2000 und ein gefilterter Index mit Startdaten nach dem 1. August 2000 erstellt.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsByStartDate'
AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO
Im folgenden Beispiel werden von der Abfrage Startdaten höher als 1. September 2004 ausgewählt. Diese sind sämtlich im gefilterten Index und in der gefilterten Sicht enthalten. Der Abfrageoptimierer berücksichtigt den gefilterten Index FIBillOfMaterialsByStartDate, da dieser die korrekten Ergebnisse für die Abfrage enthält.
SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO
Im nächsten Beispiel wählt die Abfrage Startdaten größer als 1. Juni 2004 aus, die alle in der Sicht, jedoch nicht im gefilterten Index enthalten sind. Der Abfrageoptimierer berücksichtigt den gefilterten Index FIBillOfMaterialsByStartDate nicht, da die Abfrage unterschiedliche Ergebnisse zurückgeben kann, indem sie den gefilterten Index verwendet, der mit den korrekten Ergebnissen verglichen wird, wenn die Abfrage aus der Sicht auswählt.
SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO
Indizierte Sichten und gefilterte Indizes
Gefilterte Indizes haben die folgenden Vorteile gegenüber indizierten Sichten:
Reduzierter Aufwand bei der Indexverwaltung. Im Vergleich zu einer indizierten Sicht benötigt der Abfrageprozessor weniger CPU-Ressourcen, um einen gefilterten Index zu aktualisieren.
Verbesserte Planqualität. Während der Abfragekompilierung wählt der Abfrageoptimierer in vielen Situationen bevorzugt einen gefilterten Index anstelle der äquivalenten indizierten Sicht aus.
Neuerstellung von online geschalteten Indizes. Sie können gefilterte Indizes neu erstellen, während die Indizes für Abfragen verfügbar sind. Die Neuerstellung von online geschalteten Indizes wird bei indizierten Sichten nicht unterstützt. Weitere Informationen finden Sie unter der REBUILD-Option von ALTER INDEX (Transact-SQL).
Nicht eindeutige Indizes Gefilterte Indizes können nicht eindeutig sein, wohingegen indizierte Sichten eindeutig sein müssen.
Aus den oben erwähnten Gründen empfehlen wir, bevorzugt einen gefilterten Index statt einer indizierten Sicht zu verwenden. Ein gefilterter Index kann statt einer indizierten Sicht verwendet werden, wenn die folgenden Bedingungen erfüllt sind: Die Sicht verweist nur auf eine Tabelle, Abfragen geben keine berechneten Spalten zurück, und das Sichtprädikat verwendet einfache Vergleichslogik. Der folgende Prädikatausdruck ist beispielsweise in einer Sichtdefinition zulässig, jedoch nicht in gefilterten Indizes, da dieser den LIKE-Operator enthält.
WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'
Schlüsselspalten
Die bewährte Methode besteht darin, eine geringe Anzahl von Schlüsselspalten oder eingeschlossenen Spalten in eine gefilterte Indexdefinition einzuschließen und nur die Spalten einzubeziehen, die der Abfrageoptimierer benötigt, um den gefilterten Index für den Abfrageausführungsplan auszuwählen. Der Abfrageoptimierer kann einen gefilterten Index für die Abfrage auswählen, unabhängig davon, ob dieser die Abfrage abdeckt oder nicht. Der Abfrageoptimierer wählt jedoch eher einen gefilterten Index aus, der die Abfrage abdeckt. Weitere Informationen zum Abdecken von Abfragen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten.
In einigen Fällen deckt ein gefilterter Index die Abfrage ab, ohne die Spalten im gefilterten Indexausdruck als Schlüsselspalten oder eingeschlossene Spalten in der gefilterten Indexdefinition einzuschließen. Die folgenden Richtlinien erläutern, wann eine Spalte im gefilterten Indexausdruck eine Schlüsselspalte oder eingeschlossene Spalte in der gefilterten Indexdefinition sein sollte. Die Beispiele beziehen sich auf den gefilterten Index FIBillOfMaterialsWithEndDate, der zuvor erstellt wurde.
Eine Spalte im gefilterten Indexausdruck muss in der gefilterten Indexdefinition keine Schlüsselspalte oder eingeschlossene Spalte sein, wenn der gefilterte Indexausdruck dem Abfrageprädikat entspricht und die Abfrage die Spalte im gefilterten Indexausdruck mit den Abfrageergebnissen nicht zurückgibt. Zum Beispiel deckt FIBillOfMaterialsWithEndDate die folgende Abfrage ab, da das Abfrageprädikat dem Filterausdruck entspricht und EndDate nicht mit den Abfrageergebnissen zurückgegeben wird. FIBillOfMaterialsWithEndDate erfordert nicht, dass EndDate eine Schlüsselspalte oder eingeschlossene Spalte in der gefilterten Indexdefinition ist.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
Eine Spalte im gefilterten Indexausdruck sollte in der gefilterten Indexdefinition eine Schlüsselspalte oder eingeschlossene Spalte sein, wenn das Abfrageprädikat die Spalte in einem Vergleich verwendet, der nicht dem gefilterten Indexausdruck entspricht. Zum Beispiel ist FIBillOfMaterialsWithEndDate für die folgende Abfrage gültig, da damit aus dem gefilterten Index eine Teilmenge von Zeilen ausgewählt wird. Damit wird jedoch nicht die folgende Abfrage abgedeckt, da EndDate im Vergleich EndDate > '20040101' verwendet wird, der nicht dem gefilterten Indexausdruck entspricht. Der Abfrageprozessor kann diese Abfrage nicht ausführen, ohne die Werte von EndDate abzurufen. Deshalb sollte EndDate eine Schlüsselspalte oder eingeschlossene Spalte in der gefilterten Indexdefinition sein.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO
Eine Spalte im gefilterten Indexausdruck sollte in der gefilterten Indexdefinition eine Schlüsselspalte oder eingeschlossene Spalte sein, wenn die Spalte im Abfrageresultset enthalten ist. Zum Beispiel deckt FIBillOfMaterialsWithEndDate die folgende Abfrage nicht ab, da damit die EndDate-Spalte in den Abfrageergebnissen zurückgegeben wird. Deshalb sollte EndDate eine Schlüsselspalte oder eingeschlossene Spalte in der gefilterten Indexdefinition sein.
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
Der Primärschlüssel der Tabelle muss in der gefilterten Indexdefinition keine Schlüsselspalte oder eingeschlossene Spalte sein. Der Primärschlüssel ist automatisch in allen nicht gruppierten Indizes enthalten, dazu zählen auch gefilterte Indizes.
Datenkonvertierungsoperatoren im Filterprädikat
Wenn der im gefilterten Indexausdruck der gefilterten Indexergebnisse angegebene Vergleichsoperator eine implizite oder explizite Datenkonvertierung ergibt, kommt es zu einem Fehler, wenn die Konvertierung auf der linken Seite eines Vergleichsoperators auftritt. Eine mögliche Lösung besteht darin, den gefilterten Indexausdruck mit dem Datenkonvertierungsoperator (CAST oder CONVERT) auf die rechte Seite des Vergleichsoperators zu schreiben.
Im folgenden Beispiel wird eine Tabelle mit einer Vielzahl von Datentypen erstellt.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO
In der folgenden gefilterten Indexdefinition wird die Spalte b implizit in einen ganzzahligen Datentyp konvertiert, um sie mit der Konstante 1 vergleichen zu können. Dadurch wird die Fehlermeldung 10611 erzeugt, da die Konvertierung auf der linken Seite des Operators im gefilterten Prädikat auftritt.
USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes
WHERE name = N'TestTabIndex'
AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO
Die Lösung besteht darin, die Konstante auf der rechten Seite zu konvertieren, damit diese vom gleichen Typ ist wie Spalte b, wie aus dem folgenden Beispiel hervorgeht:
CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO
Durch das Verschieben der Datenkonvertierung von der linken Seite auf die rechte Seite eines Vergleichsoperators wird möglicherweise die Bedeutung der Konvertierung geändert. Im obigen Vergleichsbeispiel wurde aus einem Integer-Vergleich ein varbinary-Vergleich, wenn der CONVERT-Operator zur rechten Seite hinzugefügt wurde.
Verweisende Abhängigkeiten
Die sys.sql_expression_dependencies-Katalogsicht kennzeichnet jede Spalte im gefilterten Indexausdruck als eine verweisende Abhängigkeit. Sie können die Definition einer in einem gefilterten Indexausdruck definierten Tabellenspalte nicht löschen, umbenennen oder ändern.
Wann gefilterte Indizes verwendet werden
Gefilterte Indizes sind nützlich, wenn Spalten klar definierte Teilmengen von Daten enthalten, auf die Abfragen in SELECT-Anweisungen verweisen. Beispiele:
Spalten mit geringer Dichte, die nur wenige Werte ungleich NULL enthalten.
Heterogene Spalten, die Datenkategorien enthalten.
Spalten, die Wertebereiche enthalten, z. B. Dollarmengen, Zeit- und Datumsangaben.
Tabellenpartitionen, die durch einfache Vergleichslogik für Spaltenwerte definiert werden.
Der reduzierte Verwaltungsaufwand für gefilterte Indizes ist am deutlichsten, wenn die Zeilenanzahl im Index verglichen mit der eines Tabellenindex klein ist. Wenn der gefilterte Index die meisten Zeilen in der Tabelle einschließt, ist der Verwaltungsaufwand möglicherweise größer als bei einem Tabellenindex. In diesem Fall sollten Sie anstelle eines gefilterten Index einen Tabellenindex verwenden.
Gefilterte Indizes werden für eine Tabelle definiert und unterstützen nur einfache Vergleichsoperatoren. Wenn Sie einen Filterausdruck benötigen, der auf mehrere Tabellen verweist oder eine komplexe Logik aufweist, sollten Sie eine Sicht erstellen.
Unterstützte Funktionen für gefilterte Indizes
Im Allgemeinen bieten Database Engine (Datenbankmodul) und Tools die gleiche Unterstützung für gefilterte Indizes wie für nicht gruppierte Tabellenindizes. Die gefilterten Indizes werden hier als eine besondere Art von nicht gruppierten Indizes angesehen. Die folgende Liste enthält Hinweise zu Tools und Funktionen, die vollständige Unterstützung, keine Unterstützung oder eingeschränkte Unterstützung für gefilterte Indizes bieten.
ALTER INDEX unterstützt gefilterte Indizes. Verwenden Sie CREATE INDEX WITH DROP_EXISTING, um den gefilterten Indexausdruck zu ändern.
Die Funktion für fehlende Indizes schlägt keine gefilterten Indizes vor.
Der Database Engine (Datenbankmodul)-Optimierungsratgeber berücksichtigt beim Empfehlen einer Indexoptimierung gefilterte Indizes und kann den gefilterten Index is not null empfehlen.
Onlineindexvorgänge unterstützen gefilterte Indizes.
Tabellenhinweise unterstützen gefilterte Indizes, unterliegen jedoch einigen Einschränkungen, die nicht für nicht gefilterte Indizes gelten. Diese werden im folgenden Abschnitt erläutert.
Überlegungen zu Abfragen
Der Abfrageoptimierer kann einen gefilterten Index verwenden, wenn bei der Abfrage mit oder ohne gefilterten Index dieselben Ergebnisse ausgewählt werden. Der zuvor beschriebene gefilterte Index FIBillOfMaterialsWithEndDate ist für die folgenden zwei Abfragen gültig. Im ersten Beispiel stimmt das Abfrageprädikat mit dem Prädikat für gefilterte Indizes WHERE EndDate IS NOT NULL genau überein. Im zweiten Beispiel weist das Abfrageprädikat eine höhere Selektivität auf als das Filterprädikat, da es eine Teilmenge von Zeilen im Index enthält.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate < '20040701';
GO
Bei der nächsten Abfrage kann auch FIBillOfMaterialsWithEndDate verwendet werden. Es ist jedoch möglich, dass der Abfrageoptimierer den gefilterten Index aufgrund anderer Faktoren, die den Abfrageaufwand bestimmen, z. B. die Selektivität des Abfrageprädikats, nicht auswählt. Sie können erzwingen, dass der Abfrageoptimierer den gefilterten Index auswählt, indem Sie diesen als Abfragehinweis verwenden, wie das folgende Beispiel zeigt.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IN ('20040825', '20040908', '20040918');
GO
Der Abfrageoptimierer verwendet keinen gefilterten Index, wenn bei der Abfrage Zeilen zurückgegeben werden können, die nicht im gefilterten Index enthalten sind. Beispielsweise wird FIBillOfMaterialsWithEndDate vom Abfrageoptimierer für die folgende Abfrage nicht berücksichtigt, da bei der Abfrage möglicherweise eine Zeile mit einem NULL-EndDate und einem ModifiedDate ungleich NULL zurückgegeben werden kann, die im FIBillOfMaterialsWithEndDate nicht vorkommt, da hier nur Werte ungleich NULL für EndDate enthalten sind.
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO
Wenn ein gefilterter Index explizit als Tabellenhinweis verwendet wird und der gefilterte Index möglicherweise nicht alle Abfrageergebnisse enthält, wird vom Abfrageoptimierer der Abfragekompilierungsfehler 8622 generiert. Im folgenden Beispiel generiert der Abfrageoptimierer den Fehler 8622, da FIBillOfMaterialsWithEndDate für die Abfrage ungültig ist und explizit als Indexhinweis verwendet wird:
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO
Parametrisierte Abfragen
In einigen Fällen enthält eine parametrisierte Abfrage zur Kompilierungszeit nicht genügend Informationen, damit vom Abfrageoptimierer ein gefilterter Index ausgewählt werden kann. Es ist möglich, die Abfrage erneut zu schreiben, damit die fehlenden Informationen bereitgestellt werden. Im folgenden Beispiel wird der gefilterte Index FIBillOfMaterialsWithComponentID für die SELECT-Anweisung vom Abfrageoptimierer nicht berücksichtigt, da die Parameterwerte für @p und @q zur Kompilierungszeit nicht bekannt sind. Das folgende Abfragebeispiel wird mit SHOWPLAN_XML (auf ON festgelegt) ausgeführt, damit Sie in der SHOWPLAN_XML-Ausgabe die gefilterten Indizes ohne Übereinstimmung für parametrisierte Abfragen anzeigen können.
USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
WHERE name = N'FIBillOfMaterialsWithComponentID'
AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO
Das UnmatchedIndexes-Element und das Parameterization-Unterelement in der SHOWPLAN_XML-Ausgabe geben an, dass der gefilterte Index keine Übereinstimmung für die Abfrage war. Weitere Informationen zum Anzeigen der SHOWPLAN_XML-Ausgabe finden Sie unter XML-Showplans.
Die Lösung besteht darin, die Abfrage so zu ändern, dass die Abfrageergebnisse leer sind, wenn ein parametrisierter Ausdruck keine Teilmenge des Filterprädikats ist. Die folgende Abfrage veranschaulicht diese Änderung. Durch das Hinzufügen des ComponentID in (533, 324, 753)-Ausdrucks zur WHERE-Klausel wird sichergestellt, dass die Ergebnisse der Abfrage eine Teilmenge des gefilterten Prädikatausdrucks sind. Dank dieser Änderung kann der Abfrageoptimierer den gefilterten Index FIBillOfMaterialsWithComponentID für die folgende SELECT-Anweisung berücksichtigen.
USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO
Einfache Parametrisierung
In den meisten Fällen führt der Abfrageoptimierer für eine Abfrage keine einfache Parametrisierung aus (wird in SQL Server 2005 als automatische Parametrisierung bezeichnet), wenn der Abfrageplan einen gefilterten Index enthält. Das Ausführen einer einfachen Parametrisierung für diese Abfragen kann den Bereich möglicher Parameterwerte erweitern, sodass der gefilterte Index für die Abfrageergebnisse keine Genauigkeit gewährleisten kann. Beispielsweise führt der Abfrageoptimierer ggf. keine einfache Parametrisierung durch, wenn die WHERE-Klausel der SELECT-Anweisung eine Spalte verwendet, die im Prädikat eines gefilterten Index verwendet wird. Der Grund ist, dass es in diesem Fall wahrscheinlich so ist, dass der Abfrageplan einen gefilterten Index enthält.
Ggf. können Sie die Abfrage parametrisieren, indem Sie sie umschreiben. Verwenden Sie dazu die in diesem Abschnitt beschriebenen Richtlinien, um sicherzustellen, dass der gefilterte Index die Abfrage abdeckt.
Abfragen mit Key Lookups
Der Abfrageoptimierer kann einen gefilterten Index verwenden, auch wenn dieser die Abfrage nicht abdeckt, indem er ein Key Lookup ausführt, um die übrigen Spalten abzurufen, die der gefilterte Index nicht abdeckt. Weitere Informationen zu Key Lookups finden Sie unter Key Lookup (Showplanoperator). Der Abfrageoptimierer wählt ggf. diesen Ansatz, wenn die geschätzte Anzahl von Key Lookups gering ist. Die folgende Abfrage verwendet einen Indexhinweis, um zu erzwingen, dass der Abfrageprozessor FIBillOfMaterialsWithEndDate mit Bookmark Lookups für EndDate verwendet. Der Key Lookup tritt für den EndDate > @date-Vergleich im Abfrageprädikat auf.
USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO
Beachten Sie, dass EndDate > @Date keine genaue Übereinstimmung mit dem gefilterten Indexausdruck EndDate IS NOT NULL ist. Der gefilterte Index ist für diese parametrisierte Abfrage weiterhin gültig, da dieser eine Teilmenge der Zeilen zurückgibt, die durch den gefilterten Indexausdruck definiert wurden.
Siehe auch