fill factor の現状確認 SQL

-- 全 INDEX の fill factor 現状
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    i.type_desc              AS index_type,
    i.fill_factor,           -- 0 は実質 100 と同じ意味
    i.is_padded,
    si.user_updates          AS update_count_since_restart
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats si
    ON si.object_id = i.object_id AND si.index_id = i.index_id
WHERE i.name IS NOT NULL
  AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY si.user_updates DESC;