テーブルのインデックスは次第に断片化が進み、
パフォーマンスに影響するようです。
GUI(ManagementStudio)で1個1個調べるのはえらい時間がかかるので、
どのインデックスが断片化しているかを一覧表示する方法を探しました。
すると、SQL AUTHORITYというサイトにありました。
Find Index Fragmentation Details – Slow Index Performance
SELECT ps.database_id, ps.OBJECT_ID,
ps.index_id, b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
これをもとに再構築するコマンドを
ALTER INDEX インデックス名 ON テーブル REBUILD
のように入力するのですが、どうせならテーブル名も表示できたら・・・
と思ったらそれを表示する投稿が読者からありますね。
SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ORDER BY ps.avg_fragmentation_in_percent desc
これの実行結果をExcelに書き出して、ALTER文を作成していましたが、
読者投稿の最後のほうを見ると、完全自動化する方法がありました！
よりよい方法を読者が加えていく、というのは良いですね。
create table #frag(
tabName varchar(200)
)
insert into #frag
select S.name + '.’ + tbl.[name] TableName
from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
inner join sys.schemas S on tbl.schema_id = S.schema_id
where [database_id] = db_id() and mn.avg_fragmentation_in_percent > 40
order by mn.avg_fragmentation_in_percent desc
while exists(select top 1 tabName from #frag)
begin
declare @name as varchar(200), @sql varchar(1000)
select top 1 @name = tabName from #frag
set @sql = 'ALTER INDEX ALL ON ' + @name + ' REBUILD’
select @sql
delete from #frag where tabName = @name
exec(@sql)
end
drop table #frag
ただし、巨大なテーブルのインデックス再構築には時間がかかるので、
少しづつ実行できる処理を加えたほうがよさそうです。