SQL Tips

Detect Indexes Fragmentation

SELECT 
    t.NAME 'Table name',
    i.NAME 'Index name',
    ips.index_type_desc,
    ips.alloc_unit_type_desc,
    ips.index_depth,
    ips.index_level,
    ips.avg_fragmentation_in_percent,
    ips.fragment_count,
    ips.avg_fragment_size_in_pages,
    ips.page_count,
    ips.avg_page_space_used_in_percent,
    ips.record_count,
    ips.ghost_record_count,
    ips.Version_ghost_record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes,
    ips.forwarded_record_count
FROM 
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
    sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
    sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
    AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
    AVG_FRAGMENTATION_IN_PERCENT, fragment_count

Rebuild All Indexes

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'

Reorganize All Indexes

Exec sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? Reorganize'

Reorganize top fragmented index

begin
DECLARE @QUERY_TEXT VARCHAR(8000)   
declare @TABLE_NAME VARCHAR(50)
declare @INDEX_NAME VARCHAR(50)
    SELECT TOP 1 @TABLE_NAME = OBJECT_NAME(fq.OBJECT_ID),
@INDEX_NAME = fq.name 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) qs
INNER JOIN sys.indexes fq
ON fq.object_id = qs.object_id
AND fq.index_id = qs.index_id
WHERE qs.avg_fragmentation_in_percent > 0  and fq.name is not null
ORDER BY qs.avg_fragmentation_in_percent DESC, fq.object_id

set @QUERY_TEXT  =  'ALTER INDEX '+@INDEX_NAME +' ON ' +@TABLE_NAME + ' REORGANIZE '
exec PCKG_TRACE_EXECUTE_SQL @QUERY_TEXT
end