Page History: Index Optimization - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Wed, Feb 22, 2023, 8:35 AM

Reference¶

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver16

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; (UCSF = 43 seconds)
• 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)