DBのチューニングねた

某シアトルの会社のDBを使っている際のネタ。
統計情報ってインデックスにも影響されるということらしい。

with index_stat as (
    select *
    from sys.dm_db_index_physical_stats(DB_ID(N'ALM'), 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;