Skip to content

Instantly share code, notes, and snippets.

@nakaji
Created October 10, 2012 02:00
Show Gist options
  • Save nakaji/3862715 to your computer and use it in GitHub Desktop.
Save nakaji/3862715 to your computer and use it in GitHub Desktop.
インデックス断片化の状況を表示する ref: http://qiita.com/nakaji/items/d6e8484fa67c246487ef
ALTER INDEX インデックス名 on テーブル名 REBUILD with ONLINE=ON;
with index_stat as (
select *
from sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'), NULL, NULL, NULL, NULL)
)
select
C.schema_id
,A.object_id table_id
,B.object_id index_id
,C.name schema_name
,A.name table_name
,B.name index_name
,D.avg_fragmentation_in_percent
,'ALTER INDEX [' + B.name + '] ON [' + C.name + '].[' + A.name + '] REBUILD;'
from sys.tables as A
inner join sys.indexes as B on (B.object_id = A.object_id)
inner join sys.schemas as C on (C.schema_id = A.schema_id)
inner join index_stat as D on (D.object_id = B.object_id AND D.index_id = B.index_id)
where A.type='U'
order by D.avg_fragmentation_in_percent DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment