W tym artykule opiszę swoje doświadczenia z tematem defragmentacji indeksów w MSSQL Server.
Do tej pory, jeżeli pojawiła się konieczność uporządkowania indeksów, korzystałem z polecenia
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX ('?')"
dla wszystkich tabel, albo
DBCC DBREINDEX ('table_name')
dla wybranej tabeli.
Ponieważ zgodnie z informacją na stronie https://msdn.microsoft.com/pl-pl/library/ms181671(v=sql.110).aspx polecenie to będzie usunięte w przyszłych wersjach, Microsoft zaleca użycie polecenia ALTER INDEX.
Dlatego przy okazji zagłębiania się w temat postanowiłem napisać skrypt, który wykona defragmentację indeksów z wykorzystaniem tego polecenia zgodnie z zaleceniami firmy Microsoft.
Pierwszy krok, to sprawdzenie aktualnych wartości fragmentacji indeksów przy użyciu polecenia
sys.dm_db_index_physical_stats
Znając aktualne wartości fragmentacji indeksów możemy określić rodzaj operacji (reorganize lub rebuild), jaki należy wykonać na każdym z indeksów (zgodnie z informacją zawartą tutaj).
- jeżeli wartość fragmentacji zawiera się między 5 a 30 – wykonujemy operację reorganize
- jeżeli wartość fragmentacji jest większa niż 30 – wykonujemy operację rebuild.
Dodatkowo, przy wykonywaniu defragmentacji indeksów należy wziąć pod uwagę wartość page_count. Zgodnie z informacją m.in. z tej strony defragmentacji należy poddać te indeksy, dla których wartość page_count jest większa niż 1000.
Biorąc pod uwagę powyższe założenia napisałem poniższy skrypt
if (object_id('tempdb.dbo.#tables')) is not null drop table #tables
use database1
go
select
t4.name as database_name,
t1.database_id,
t3.name as table_name,
t2.name as index_name,
t2.index_id, t1.avg_fragmentation_in_percent,
t1.index_depth, t1.page_count
into #tables
from sys.dm_db_index_physical_stats (db_id(),null,null,null,'limited') as t1
join sys.indexes as t2 on t1.index_id=t2.index_id and t1.object_id=t2.object_id
join sys.tables as t3 on t3.object_id = t1.object_id
join sys.databases as t4 on t4.database_id = t1.database_id
where
isnull(avg_fragmentation_in_percent,0)>0
declare @table_name varchar(100), @index_name varchar(100), @index_id int, @frag numeric(38,15),
@text varchar(1000), @query_string varchar(1000), @frag_after numeric(38,15),
@old_table_name varchar(100), @index_depth int, @page_count int
select @old_table_name=''
declare k cursor for
select table_name, index_name,index_id, avg_fragmentation_in_percent, index_depth, page_count
from #tables
order by
table_name
open k
fetch next from k into @table_name, @index_name,@index_id, @frag, @index_depth, @page_count
while @@fetch_status=0 begin
if @table_name<>@old_table_name begin
print '['+@table_name+']'
select @old_table_name = @table_name
end
if @page_count<1000 begin
print ' '+left('['+@index_name+']'+space(50),50)+' page_count<1000'
end else begin
if @frag>5 and @frag<=30 begin
select @query_string = 'alter index '+@index_name+' on '+@table_name+' reorganize '
exec (@query_string)
select @frag_after=avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),object_id(@table_name),@index_id,null,'limited')
where index_depth=@index_depth
print ' '+left('['+@index_name+']'+space(50),50)+' reorganize before '+convert(varchar,@frag)+' after '+convert(varchar,@frag_after)
end else if @frag>30 begin
select @query_string = 'alter index '+@index_name+' on '+@table_name+' rebuild '
exec (@query_string)
select @frag_after=avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),object_id(@table_name),@index_id,null,'limited')
where index_depth=@index_depth
print ' '+left('['+@index_name+']'+space(50),50)+' rebuild before '+convert(varchar,@frag)+' after '+convert(varchar,@frag_after)
end else begin
print ' '+left('['+@index_name+']'+space(50),50)+' defrag not required, avg_fragmentation '+convert(varchar,@frag)
end
end
fetch next from k into @table_name, @index_name,@index_id, @frag, @index_depth, @page_count
end
close k
deallocate k
drop table #tables
print 'done'
Poniżej wynik wykonania skryptu dla wybranej tabeli
[table_name] [index1] defrag not required, avg_fragmentation 0.170921321056404 [index2] reorganize before 8.669527896995708 after 0.438212094653812 [index3] defrag not required, avg_fragmentation 0.880281690140845 [index4] rebuild before 33.511450381679389 after 0.000000000000000 [index5] defrag not required, avg_fragmentation 0.992720052945070 done
