Finding fragmented indexes and rebuilding indexes

Posted by Jeff Rix on April 28, 2011 under DBA | Be the First to Comment

Find Indexes to Rebuild-find indexes with a page count greater than ten thousand and fragmentation above ninety percent.

 

SELECT ps.database_id, c.name as table_name, ps.OBJECT_ID,

ps.index_id, b.name, ps.avg_fragmentation_in_percent, ps.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

INNER JOIN sys.all_objects as c on ps.object_id = C.object_id

AND ps.index_id = b.index_id where ps.avg_fragmentation_in_percent > 90 and ps.page_count > 10000

ORDER BY ps.avg_fragmentation_in_percent desc

 

 

Before Index is Rebuilt- fragmentation is caused by data modification and can cause leaf pages to be out of order and index pages to not be used to their maximum value.

 

–turn statistics on to get logical reads

SET STATISTICS IO ON

 

–select columns form the composite primary key (clustered index).

SELECT DISTRICT, SECTION_KEY, COURSE_SESSION, STUDENT_ID, ASMT_NUMBER FROM Student_ASMT_Grade

 

Fragmentation at ninety eight percent

 

Logical reads 141,182

Page count: 86,684

Estimated IO Cost 103.782

Estimated Operator Cost 107.59

 

Code to Rebuild Index

 

USE [YESPrep_DW]

GO

ALTER INDEX [PK_Student_ASMT_Grade] ON [dbo].[Student_ASMT_Grade] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )

GO

 

After Index is Rebuilt- rebuilding indexes cuts logical reads in half because index is no longer fragmented. Custs Cost in half.

 

SET STATISTICS IO ON

 

SELECT DISTRICT, SECTION_KEY, COURSE_SESSION, STUDENT_ID, ASMT_NUMBER FROM Student_ASMT_Grade

 

Logical reads 86,998

Page count: 86,684

Estimated IO Cost 64.2128

Estimated Operator Cost 68.2128

 

Fragmentation is now at zero percent