Page History: Index Optimization - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Mon, Feb 20, 2023, 9:16 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;