SQL Server 2008: Mit gefilterten Indexen die Performance verbessern

Gefilterte Indexe sind eine praktische Funktion von SQL Server 2008, mit der man Indexe für Teilmengen von Daten definieren kann. ZDNet erklärt ihre Vor- und Nachteile und zeigt, wie man einen gefilterten Index erstellt.

Ein gefilterter Index ist ein nicht-geclusterter Index, der für eine genau definierte Datenteilmenge in einem SQL-Server-Tabellenobjekt erstellt wird. "Genau definiert" bedeutet in diesem Fall diejenigen Datenteilmengen, die ausschließlich zur Erfüllung der Abfragekriterien verwendet werden. Wenn beispielsweise ein Feld in einer Tabelle enthalten ist, die vorwiegend NULL-Werte aufweist, könnte das Erstellen eines gefilterten Index von Vorteil sein, der ausschließlich die Werte enthält, die NICHT NULL sind. Allerdings lassen sich keine geclusterten Indexe mit einem Filter definieren.

Warum ein gefilterter Index?

Gefilterte Indexe können Performancevorteile in Szenarien bieten, in denen ein Großteil der Abfragen in einer Tabelle eine Filterung für eine bestimmte Datenteilmenge durchführen. Diese Indexe sind meist viel kleiner als ein Index für das gesamte Feld, so dass weniger Indexspeicher erforderlich ist. Außerdem erfordern gefilterte Indexe in der Regel weniger Administrationsaufwand. Da der gefilterte Index kleiner ist, betreffen an den Daten vorgenommene Bearbeitungsvorgänge kleinere Teilmengen des Index und erfordern somit weniger Datenbank-I/O-Operationen.

Erstellen eines gefilterten Index

Im Folgenden wird dargestellt, wie man einen gefilterten Index erstellt und wie sich die durch seine Nutzung erreichten Performance-Vorteile auswirken. Dazu muss man als Erstes das folgende Script ausführen, um die SalesHistory-Tabelle zu erstellen und zu bevölkern.

IF OBJECT_ID(’SalesHistory’, ‘U’) IS NOT NULL DROP TABLE SalesHistory GO CREATE TABLE [dbo].[SalesHistory] ( [SaleID] [int] IDENTITY(1,1), Product] [varchar](10) NULL, [SaleDate] [datetime] NULL, [SalePrice] [money] NULL, CONSTRAINT PK_SalesHistory_SaleID PRIMARY KEY CLUSTERED (SaleID ASC) ) GO SET NOCOUNT ON BEGIN TRANSACTION DECLARE @i INT SET @i = 1 WHILE (@i <=5000) BEGIN INSERT INTO [SalesHistory](Product, SaleDate, SalePrice) VALUES (’Computer’, DATEADD(ww, @i, ‘3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57)) INSERT INTO [SalesHistory](Product, SaleDate, SalePrice) VALUES(’BigScreen’, DATEADD(ww, @i, ‘3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13)) INSERT INTO [SalesHistory](Product, SaleDate, SalePrice) VALUES(’PoolTable’, DATEADD(ww, @i, ‘3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29)) SET @i = @i + 1 END COMMIT TRANSACTION GO

Mit einigen Einträgen in der SalesHistory-Tabelle aktualisiert man dann das SaleDate auf NULL, und zwar für 6 von 7 Einträgen in der Tabelle. So entsteht eine recht spärliche Verteilung von Werten im SaleDate-Feld. Nach der Aktualisierung soll ein normaler nicht-geclusterter Index im SaleDate-Feld erstellt werden.

UPDATE SalesHistory SET SaleDate = NULL WHERE (SaleID % 7) > 0 GO CREATE INDEX idx_SalesHistory_SaleDate ON SalesHistory(SaleDate)

Dann den folgenden Befehl ausführen, um eine IO-Statistik hierfür auszuführen. So kann man die IO-Werte für jeden ausgeführten TSQL-Befehl anzeigen.

SET STATISTICS IO ON

Die folgende Abfrage gibt alle Zeilen aus SalesHistory-Tabelle aus, in denen SaleDate einen Wert enthält. Diese Abfrage verwendet den vorher erstellten Index idx_SalesHistory_SaleDate sowie eine Index-Seek-Operation zur Ausgabe von 2142 Zeilen. Diese Abfrage erfordert 8 logische Lesevorgänge aus der Datenbank, um die erforderlichen Zeilen auszugeben.

Dabei ist zu beachten, dass die in diesem Artikel verwendeten Abfragen nur das SaleDate-Feld im Resultset ausgeben. Das liegt daran, dass ein Ausschließen von Feldern oder Hinzufügen zusätzlicher Felder in der SELECT-Liste den Ausführungsplan verändert. Für den Zweck dieses Artikels soll nur das Feld ausgegeben werden, für das Kriterien festgelegt werden.

SELECT SaleDate FROM SalesHistory WHERE SaleDate IS NOT NULL

Die indexbezogenen Daten für den idx_SalesHistory_SaleDate-Index können durch Abfragen einiger System-Views angezeigt werden.

SELECT i.name, p.rows, i.filter_definition FROM sys.partitions p JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE OBJECT_NAME(i.object_id) = ‘SalesHistory’ AND i.name = ‘idx_SalesHistory_SaleDate’

Die folgende Abfrage ähnelt der oben genannten Abfrage, filtert jedoch die Zeilen, in denen SaleDate einen NULL-Wert enthält. Diese Abfrage führt außerdem einen Index-Seek für den oben erstellten Index durch, benötigt dabei jedoch 31 logische Lesevorgänge aus der Datenbank, da die Abfrage 12858 Einträge ausgibt.

SELECT SaleDate FROM SalesHistory WHERE SaleDate IS NULL

Nach diesem Einblick in die Funktionsweise nicht-geclusterter Indexe soll nun näher beleuchtet werden, wie man Filter einsetzen kann, wenn der nicht-geclusterte Index so definiert ist, dass er nur Datenteilmengen indexiert. Dazu muss zunächst der vorher erstellte Index entfernt werden.

DROP INDEX SalesHistory.idx_SalesHistory_SaleDate

Im folgenden Script wird ein gefilterter nicht-geclusterter Index im SaleDate-Feld erstellt. Dieser Index wird Datenpointer AUSSCHLIESSLICH für diejenigen Einträge enthalten, für die das SaleDate NICHT NULL ist. Das bedeutet, dass für alle Einträge, in denen das SaleDate NULL ist, der Index überhaupt nicht berücksichtigt wird.

CREATE INDEX idx_SalesHistory_SaleDate ON SalesHistory(SaleDate) WHERE SaleDate IS NOT NULL

In der folgenden Abfrage wird ein Indexscan des idx_SalesHistory_SalePrice mit 7 logischen Datenbanklesevorgängen verwendet. Somit erfordert die Operation dank des gefilterten Index trotz Durchführung eines Indexscans weniger logische Lesevorgänge.

SELECT SaleDate FROM SalesHistory WHERE SaleDate IS NOT NULL

Man kann die gleiche Systemtabellenabfrage wie vorher durchführen, um die Anzahl der im Index enthaltenen Einträge anzuzeigen. Der vorherige Index enthielt alle 15.000 Einträge aus der Tabelle, während der aktuelle Index nur Einträge enthält, in denen das SaleDate NICHT NULL ist (2142 Einträge).

SELECT i.name, p.rows, i.filter_definition FROM sys.partitions p JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id WHERE OBJECT_NAME(i.object_id) = ‘SalesHistory’ AND i.name = ‘idx_SalesHistory_SaleDate’

In der folgenden Abfrage werden alle Einträge, in denen das SaleDate NULL ist, gesucht. Dabei ist zu bedenken, dass der vorher definierte Index nur die Einträge enthält, in denen das SaleDate NICHT NULL ist, weshalb er für diese Abfrage nicht berücksichtigt wird. Tatsächlich wird ein Scan eines geclusterten Index verwendet, um die Einträge zu finden, in denen das SaleDate NULL ist, was 79 logische Datenbanklesevorgänge ergibt.

SELECT SaleDate FROM SalesHistory WHERE SaleDate IS NULL

Fazit

Die neue Funktion für gefilterte Indexe in SQL Server 2008 ist sehr praktisch. Mit ihr kann man Indexe ausschließlich für Teilmengen häufig genutzter Daten durchführen. Diese Indextypen sollten allerdings mit Vorsicht eingesetzt werden. In fast allen Fällen ist ein normaler nicht-geclusterter Index nützlicher als ein gefilterter Index. Nur wenn man die Datennutzungsmuster seiner Datenbank gut kennt, sollte man das Erstellen gefilterter Indexe erwägen.