Index Fragmentation Info
--All Indexes
select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where avg_fragmentation_in_percent > 5 and ps.index_id > 0 order by page_count desc
--Specific Index
select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where ps.index_id > 0 and i.name like 'Index_Name' order by 5 desc
======================================================================================================================
USE [DB_Name]
ALTER INDEX [Index_Name] ON [dbo].[Table_Name] REORGANIZE
ALTER INDEX [Index_Name] ON [dbo].[Table_Name] REBUILD
ALTER INDEX ALL ON [dbo].[Table_Name] REBUILD WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER INDEX ALL ON [dbo].[Table_Name] REORGANIZE ;
Working Method
/* --DB Select
use EIMDW
use EIMDW_CXOH
*/
--Alter Index Query Generator
select 'ALTER INDEX ALL ON ['+object_schema_name(ps.object_id)+'].['+object_name (ps.object_id)+'] REBUILD ;', object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id
where avg_fragmentation_in_percent > 15 and page_count > 200 and ps.index_id > 0 order by page_count desc
/*
--Alter index
ALTER INDEX ALL ON [dbo].[(SQList.ChangeLog)] REBUILD ;
ALTER INDEX ALL ON [dbo].[(SQList.ChangeLog)] REBUILD ;
*/
No comments:
Post a Comment
If you have any doubt or question, please contact us.