SQL Server 2005/ SQL Server 2008 - Rebuild or Reorganize ALL Indexes in a Database

I found this gem while looking at a database that needed all indexes rebuilt.

I actually had to create the programmatic way of doing rebuilding all indexes in a database based on the information in these posts:

See the fragmentation:

USE __REPLACE_WITH_DATABASE_NAME__
GO

Print 'Selecting Index Fragmentation in the database.'

SELECT 
  DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
 ,OBJECT_NAME(DPS.OBJECT_ID) AS TableName
 ,SI.NAME AS IndexName
 ,DPS.INDEX_TYPE_DESC AS IndexType
 ,DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
 ,DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI 
    ON DPS.OBJECT_ID = SI.ID 
    AND DPS.INDEX_ID = SI.INDID
ORDER BY DPS.avg_fragmentation_in_percent DESC
GO

Fix the fragmentation with a rebuild:

Print 'Rebuilding indexes on every table in the database.'

EXEC sp_MSforeachtable @command1="print 'Rebuilding indexes for ?' ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)"
GO

Fix the fragmentation with reorganize:

Print 'Reorganizing indexes on every table in the database.'

EXEC sp_MSforeachtable @command1="print 'Reorganizing indexes for ?' ALTER INDEX ALL ON ? REORGANIZE"
GO

Notice how we have items that are at 99% fragmentation and how they are gone once we run this rebuild.

image

Awesome?

If you are fuddy-duddy DBA, you would say only to rebuild indexes with greater than 30% fragmentation and reorganize the rest.  That's fine, show me some code on how to programmatically do this! :D

Or put the Ayende way, send me a patch!

kick it on DotNetKicks.com

Print | posted @ Tuesday, June 9, 2009 10:38 PM

Comments on this entry:

Gravatar # re: SQL Server 2005/ SQL Server 2008 - Rebuild or Reorganize ALL Indexes in a Database
by Dave Johnson at 1/17/2010 11:13 PM


SELECT
CASE
when avg_fragmentation_in_percent between 5 and 30 then
'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REORGANIZE;'
when avg_fragmentation_in_percent > 30
then 'ALTER INDEX ' + i.name + ' ON ' + t.name + ' REBUILD with(ONLINE=ON);' --- enterpise version obly
else 'PRINT ''INDEX ' + i.name + ' ON TABLE ' + t.name + ' ' + cast(avg_fragmentation_in_percent as varchar(15))+ '%'''
END
Gravatar # re: SQL Server 2005/ SQL Server 2008 - Rebuild or Reorganize ALL Indexes in a Database
by Senguttuvan at 7/13/2010 10:35 AM

checking the status overall DB
===============================

SELECT object_id AS ObjectID,

index_id AS IndexID,

avg_fragmentation_in_percent AS PercentFragment,

fragment_count AS TotalFrags,

avg_fragment_size_in_pages AS PagesPerFrag,

page_count AS NumPages

FROM sys.dm_db_index_physical_stats(DB_ID('Your DBName'),

NULL, NULL, NULL , 'DETAILED')

WHERE avg_fragmentation_in_percent >40

ORDER BY ObjectID, IndexID
====================================
Rebuild the index==T-SQl
====================================

DECLARE @MyTable VARCHAR (255)
DECLARE myCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @MyTable
ALTER INDEX ALL ON (@MyTable, '', 80) 'with REBUILD (online=on)'

or(offline=on)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
======================================
Reorganize the index
======================================
DECLARE @MyTable VARCHAR (255)
DECLARE myCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table: ' + @MyTable
ALTER INDEX ALL ON (@MyTable, '', 80)'with REORGANIZE(online=on)'
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
Comments have been closed on this topic.