Kto powinien i kiedy defragmentować indeksy? Warto zacząć od sprawdzenia fragmentacji indeksów. Poniżej jest kod pozwalający na weryfikację fragmentacji zewnętrznej i wewnętrznej:
SELECT OBJECT_NAME(dt.object_id), si.name, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM (SELECT OBJECT_ID, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'BAZA_DANYCH'), NULL, NULL, NULL, 'DETAILED') WHERE index_id <> 0) as dt
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
Kolumna avg_fragmentation_in_percent mówi nam o fragmentacji zewnętrznej. Wartości powyżej 10 procent wskazuje na fragmentację (warto już coś zadziałać). Kolumna avg_page_space_used_in_percent mówi natomiast o fragmentacji wewnętrznej indeksu. Wartość poniżej 75% wskazuje na problem.
Jeżeli powyższe zapytanie kończy się błędem:
Incorrect syntax near '('
To oznacza, że nasza baza danych pracuje w "compatibility level 80 (SQL Server 2000)". Należy podnieść ten poziom i wtedy zapytanie działa jak należy.Jeżeli natomiast nie ma możliwości (z różnych powodów) podniesienia poziomu funkcjonalności należy zacząć od zdefiniowania zmiennych. Oto przykład takiego zapytania:
USE BAZA_DANYCH
GO
DECLARE
@database_id INT = DB_ID(),
@object_id INT = OBJECT_ID(N'BAZA_DANYCH')
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (@database_id ,@object_id , NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
GO
Do fragmentacji indeksów otrzymujemy dwa polecenia: ALTER INDEX...REORGANIZE oraz ALTER INDEX...REBUILD. Pierwszą z nich używamy, gdy problem fragmentacji jest nie wielki. REORGANIZE dodatkowo zużywa mniej zasobów i może zostać wykonana on-line.
Mocno pofragmentowane indeksy powinno się przebudować (REBUILD). Należy pamiętać, że domyślnie powinno się to wykonywać, gdy baza danych nie jest wykorzystywana. Jest tu także opcja uruchomienia tego zadania w "locie" przy pomocy określenia dodatkowej opcji ONLINE, jednak REBUILD przebudowuje indeksy od samego początku, więc jest to zadanie dość czasochłonne i zasobożerne.
Polecenia stosuje się bardzo prosto. Aby przebudować wszystkie indeksy w danej tabeli wpisujemy polecenie:
ALTER INDEX ALL ON nazwa_tabeli REBUILD
ALTER INDEX ALL ON nazwa_tabeli REORGANIZE
Aby przebudować wszystkie indeksy we wszystkich tabelach danej bazy danych kod mamy już nieco dłuższy:
USE [Baza_danych]
GO
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Skrypt ten przebuduje wszystkie indeksy nie zmieniając wskaźnika wypełnienia stron indeksu (fill factor). Aby przebudować z nowym wskaźnikiem wypełnienia dodajemy do kodu małe zmiany:
USE [Baza_danych]
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Idąc już tą drogą pozostaje podać jeszcze skrypt reorganizacji wszystkich indeksów bazy danych:
USE [Baza_danych]
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
To wszystko może wyglądać dość skomplikowanie. Przypomnę więc dla tych bardziej początkujących, że defragmentacje można przeprowadzić również poprzez MaintenancePlan, używając klocków Reorganize Index Task lub Rebuild Index Task
