piątek, 19 grudnia 2014

Defragmentacja indeksów MS SQL

Fragmentacja indeksów w MS SQL jest dość ważnym problemem wydajnościowym baz danych. Utrzymanie tychże indeksów w dobrej kondycji staje się więc bardzo ważnym zadaniem administratora baz danych.
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

Odblokowanie xp_cmdshell

Niektóre narzędzia potrzebują rozszerzonego dostępu do MS SQL. Najczęściej są to narzędzia optymalizacji baz danych (defragmentacja, odbudowanie indeksów, backup, itp.). Od MS SQL 2008 podniesiono poziom zabezpieczeń nie pozwalając poprawnie działać tym programom. Wynika to z zablokowania fukcji sys.xp_cmdshell. Próba uruchomienia tych narzędzi najczęściej kończy się takim komunikatem:
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

Aby odblokować tę funkcjonalność należy wykonać poniższy kod:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO