Index Optimization - SQL Server

Reference¶

Query¶

SELECT 
     SchemaName = OBJECT_SCHEMA_NAME(ips.object_id)
    ,ObjectName = OBJECT_NAME(ips.object_id)
    ,IndexName = i.name
    ,IndexType = i.type_desc
    ,ips.avg_fragmentation_in_percent
    ,ips.avg_page_space_used_in_percent
    ,ips.page_count
    ,ips.alloc_unit_type_desc
from       
    sys.dm_db_index_physical_stats(DB_ID(),default,default,default,default) ips
    INNER JOIN sys.indexes AS i 
        ON ips.object_id = i.object_id
        AND ips.index_id = i.index_id
where 1=1
    and ips.object_id = object_id('dbo.MyTable') -- TODO
ORDER BY 
    page_count DESC;

Optimizations¶

• Update statistics on a table: UPDATE STATISTICS dbo.MyTable
• Update statistics on a database: EXEC sp_updatestats;
• Reorganize an index: ALTER INDEX IndexName ON dbo.MyTable REORGANIZE
• Reorganize all indexes on a table: ALTER INDEX ALL ON dbo.MyTable REORGANIZE
• Rebuild an index: ALTER INDEX IndexName ON dbo.MyTable REBUILD
• Rebuild all indexes on a table: ALTER INDEX ALL on dbo.MyTable REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)