SupportSQL

When I need to defrag large databases or tables with high rates of fragmenting without affecting performance on the server for too long I use this proc to incrementally defrag tables, getting the worst fragmented tables done first.

i.e. run using maxfrag = 90, then 80, then 70, etc… The first run will only defrag tables over 90% fragmentation levels (which also points to a serious maintenance need if any tables fall into a level higher than 70% fragmentation.)

CREATE procedure [dbo].[gp_DBA_DefragIndexesOverMaxFragmentation] @maxfrag DECIMAL = 10.0 as
—this needs to be created in the database that will be defragmented
—Uses DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
—defragment all indexes in a database that is fragmented above a declared threshold.
—DBCC SHOWCONTIG WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS —debug

—example - this will defrag any tables in the current database with fragmentation levels over 20%:
—EXEC gp_DBA_DefragIndexesOverMaxFragmentation @maxfrag=20

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @msg       VARCHAR(MAX)

— Decide on the maximum fragmentation to allow
if @maxfrag < 5 set @maxfrag = 5.0 — do not defragment if under 5% fragmentation for overuse protection
— Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_SCHEMA + ‘.[’ + TABLE_NAME +’]’
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = ‘BASE TABLE’
— Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)
— Open the cursor
OPEN tables
— Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
— Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC (‘DBCC SHOWCONTIG (”’ + @tablename + ”’)
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’)
   FETCH NEXT
      FROM tables
      INTO @tablename
END
— Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
—select * from #fraglist
— Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, ‘IndexDepth’) > 0
— Open the cursor
OPEN indexes
— loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
   select @msg = @msg + char(13) + char(13) + ‘Executing DBCC INDEXDEFRAG (0, ’ + RTRIM(@tablename) + ‘, ’ + RTRIM(@indexid) + ‘) - fragmentation currently ’ + RTRIM(CONVERT(varchar(15),@frag)) + ‘% — —time is: ’ + convert(varchar(30),getdate())
   PRINT ‘Executing DBCC INDEXDEFRAG (0, ’ + RTRIM(@tablename) + ‘, ’ + RTRIM(@indexid) + ‘) - fragmentation currently ’ + RTRIM(CONVERT(varchar(15),@frag)) + ‘% — —time is: ’ + convert(varchar(30),getdate())
   SELECT @execstr = ‘DBCC INDEXDEFRAG (0, ’ + RTRIM(@objectid) + ‘, ’ + RTRIM(@indexid) + ‘)’
   EXEC (@execstr)
   FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END
— Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes

            EXEC msdb.dbo.sp_send_dbmail    
                @profile_name = ‘some_profile_name’
                ,@recipients = ‘some_person@some_company.com’
                ,@subject = ‘Database Maintenance Report - DEFRAG job log included.’
                ,@body = @msg
                ,@query = ‘select ObjectName, ObjectId, IndexId, LogicalFrag from #fraglist’
                ,@attach_query_result_as_file  = false
                ,@width = 240
                ,@execute_query_database = master
                ,@importance = ‘High’ —or ‘Normal’ or ‘Low’

— Delete the temporary table

DROP TABLE #fraglist

-by Alek Kirstein – SupportSQL.com