Indizes warten–Neu erstellen oder reorganisieren

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, daß die entprechenden Indizes ebenfalls mitgepflegt werden müssen. Im Gegensatz zu Spalten werden bei Indizes die Statistiken aktualisert. Daher bedarfs seitens der Statistiken keinerei Aufwand. Allerdings wird der Abfrageoptimierer nicht dazu übergehen und mitteilen, daß 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 mehrere Methoden zur Verfügung. Neuerstellen oder Reorganisieren. Die grundsätzliche Frage, welche davon die bessere ist. Das hängt davon ab. Letztendlich ist es einer Frage der Performance. Eine Reorganisation benötigt relativ wenig Systemressourcen und es werden weniger Seiten während der reorganisation der Iddizes gesperrt. Allerdings werden zunächt mal auch keine Seiten gelöscht. Dieser Vorgang bewirkt zunächt mal nur eines: Die Zeiger der Indizes sind in gleichen Reihenfolge wie die der tatsächliche Daten.

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

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

Woher weiß man das:

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.

-- Ensure a USE <databasename> statement has been executed first.
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
Author: Fumus

2 thoughts on “Indizes warten–Neu erstellen oder reorganisieren

  1. „avg_fragmentation“ gibt es nicht. Es gibt „avg_fragmentation_in_percent“. (https://msdn.microsoft.com/de-de/library/ms188917.aspx)

    Quellenangaben mit Links wären nett gewesen.

    Wozu sollte man eine Temporär-Tabelle erstellen, wenn man auch direkt den Cursor auf die ursprüngliche Auswahl legen kann? Eigentlich schwer vorstellbar, dass das Skript in irgendeiner Onlinedoku sowas tut.

    1. Da hat aber einer genau gelesene 🙂

      Stimmt: es muss avg_fragmentation_in_precent heißen. Ist im fogenden Script dann aber auch richtig. Das Script für den Reorg bzw Rebuild ist direkt aus Punkt D der MSDN kopiert und stellt nur einen Weg dar. Ich persönlich würde den maintenanceplan Prozedur IndexOptimize von Ola Hallengren bevorzugen, wobei ab SQL 2016 der Wartungsplan endlich auch alle wichtigen Optionen wie Fragmentierungsgrad, Anzahl der der Seiten etc berücksichtigt..

Schreibe einen Kommentar zu Edgemeister Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert