SQL Server: Index Leitfaden – Verwaltung der Indizes durch Rebuild oder Reorganize

Indizes, Beschleuniger oder Bremse Nr 1, bedürfen einer Wartung. Man stelle sich mal das Münchener Telefonbuch vor, wenn alle neuen Telefonnummern nur immer hinten angehängt werden würden. Ähnlicher Fall bei den Indizes…

Jede Änderung der Daten führt zu dazu, dass die entsprechenden Indizes ebenfalls mitgepflegt werden müssen. Im Gegensatz zu Spalten werden bei Indizes die Statistiken aktualisiert. Daher bedarf es seitens der Statistiken keinerlei Aufwand. Allerdings wird der Abfrageoptimierer nicht dazu übergehen und mitteilen, dass die Indizes aufgrund großer Änderungen nicht mehr optimal sind.  Viele Seiten werden durch delete etwa geleert durch Seitenteilungen bei updates ist der Füllfaktor nicht mehr optimal.

Der SQL Server stellt dazu zwei Methoden zur Verfügung: Neuerstellen oder Reorganisieren. Was man letztendlich anwendet hängt vom Fragmentierungsgrad ab und von der Performance. Eine Reorganisation benötigt relativ wenig Systemressourcen und es werden weniger Seiten während der Reorganisation der Indizes gesperrt. Allerdings werden zunächst mal auch keine Seiten gelöscht. Dieser Vorgang bewirkt zunächst mal nur eines: Die Zeiger der Indizes sind in gleichen Reihenfolge wie die der tatsächliche Daten, da nur die Blattebene des Index “in Ordnung” gebracht wird.

Ein Rebuild bewirkt ein Löschen und einen Neuaufbau, wobei die Daten durchaus Online zur Verfügung gestellt werden können.

Fragementierung

Wann letztendlich eine Reorganisation sinnvoll ist, hat sich in der Praxis ein Wert der Fragmentierung zwischen 10% (MS Hilfe) und 30% etabliert.

Woher bekommt man den Fragementierungsgrad?

SELECT avg_fragmentation , object_id, object_name(object_id) 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'LIMITED')
GO

In der Onlinedoku ist sogar SQL Statement, das Ihnen die Entscheidung abnimmt, sondern per Task die dementsprechende Maßnahme ergreift: Den Wert für die Fragmentierung können Sie natürlich ändern.

USE yourDB
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Wartungsplan

Sofern man den Fragmentierungsgrad pro Index als Kriterium für den Reorg bzw. Rebuild verwendet, ist der Wartungsplanassistent die falsche Adresse. Dieser erlaubt keine Überprüfung der Fragmentierung und würde daher Tabellen , je nach Plan, immer neu erstellen oder reorganisieren.

Platzbedarf

Bedenken Sie! Die Index Neuerstellung benötigt große Mengen Platz. Da können schon mal 3 bis  4– fachen Platz des Tabellenvolumens beansprucht werden. So werden aus 200 MB Tabelle (ohne Index), mal schnell 800 MB bis 1000 MB während der Neuerstellung.