Selective Defrag/Reindex and Log

SQLServerCentral.com Script Library: Selective Defrag/Reindex and Log

USE master
GO

----------------------------------------------------------
-- This creates a table to log all activity and results --
----------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fragreport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[fragreport]
GO

CREATE TABLE [dbo].[fragreport] (
	[fid] [int] IDENTITY (1, 1) NOT NULL ,
	[timestamp] [datetime] NULL ,
	[ObjectName] [sysname] NOT NULL ,
	[ObjectId] [int] NULL ,
	[IndexName] [sysname] NOT NULL ,
	[IndexId] [int] NULL ,
	[Level] [int] NULL ,
	[Pages] [int] NULL ,
	[Rows] [int] NULL ,
	[MinimumRecordSize] [int] NULL ,
	[MaximumRecordSize] [int] NULL ,
	[AverageRecordSize] [float] NULL ,
	[ForwardedRecords] [int] NULL ,
	[Extents] [int] NULL ,
	[ExtentSwitches] [int] NULL ,
	[AverageFreeBytes] [float] NULL ,
	[AveragePageDensity] [float] NULL ,
	[ScanDensity] [float] NULL ,
	[BestCount] [int] NULL ,
	[ActualCount] [int] NULL ,
	[LogicalFragmentation] [float] NULL ,
	[ExtentFragmentation] [float] NULL ,
	[DBName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PrePost] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[fragreport] WITH NOCHECK ADD 
	CONSTRAINT [PK_fragreport] PRIMARY KEY  CLUSTERED 
	(
		[fid]
	)  ON [PRIMARY] 
GO


-------------------------------------------------------
-- Run this from master and supply any database name --
-------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ap_IndexDefragAndRebuild]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ap_IndexDefragAndRebuild]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE ap_IndexDefragAndRebuild
	@dbname varchar(100)
AS

SET NOCOUNT ON

CREATE TABLE #tables(
	rid int identity (1,1),
	tabid int,
	[name] varchar(100)
)

CREATE TABLE #indexes(
	rid int identity (1,1),
	indid int,
	[name] varchar(100)
)

CREATE TABLE #fragreport(
	fid int identity (1,1),
	[timestamp] datetime default getdate(),
	ObjectName sysname,
	ObjectId int,
	IndexName sysname,
	IndexId int,
	[Level] int,
	Pages int,
	[Rows] int,
	MinimumRecordSize int,
	MaximumRecordSize int,
	AverageRecordSize float,
	ForwardedRecords int,
	Extents int,
	ExtentSwitches int,
	AverageFreeBytes float,
	AveragePageDensity float,
	ScanDensity float,
	BestCount int,
	ActualCount int,
	LogicalFragmentation float,
	ExtentFragmentation float,
	DBName varchar(100) NULL,
	PrePost varchar(20) NULL
)

CREATE TABLE #reindex(
	rid int identity (1,1),
	ObjectName sysname,
	IndexName sysname
)

DECLARE @numtables int,
	@numindexes int,
	@numreindexes int,
	@tabcount int,
	@indcount int,
	@recount int,
	@currtable int,
	@tabname varchar(100),
	@currind int,
	@indname varchar(100)


SET @tabcount = 1

INSERT INTO #tables([tabid], [name])
EXEC ('SELECT [id], ltrim(rtrim(su.[name] + ''.'' + so.[name])) FROM ' + @dbname + '.dbo.sysobjects so INNER JOIN ' + @dbname + '.dbo.sysusers su ON su.uid = so.uid WHERE so.xtype = ''U'' AND so.[name] <> ''dtproperties''')

SELECT @numtables = count(*) FROM #tables

WHILE @tabcount <= @numtables
BEGIN
	SET @indcount = 1

	SELECT @currtable = tabid,
	       @tabname = ltrim(rtrim([name]))
	FROM #tables
	WHERE rid = @tabcount 
	
	INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
	EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH ALL_INDEXES, TABLERESULTS')

	DELETE FROM #fragreport
	WHERE IndexID IN (0,255)

	UPDATE #fragreport
	SET PrePost = 'PRE'
	WHERE PrePost is NULL

	INSERT #indexes([indid], [name])
	EXEC ('SELECT indid, [name] FROM ' + @dbname + '.dbo.sysindexes WHERE [id] = ' + @currtable + ' AND [name] not like ''_WA%'' AND indid NOT IN (0, 255)')

	SELECT @numindexes = count(*) FROM #indexes

	WHILE @indcount <= @numindexes
	BEGIN
		SELECT @currind = indid,
		       @indname = ltrim(rtrim([name]))
		FROM #indexes
		WHERE rid = @indcount
		EXEC ('DBCC INDEXDEFRAG (''' + @dbname + ''',''' + @tabname + ''',''' + @indname + ''')')
		SET @indcount = @indcount + 1
 	END

	INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
	EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + ']) WITH ALL_INDEXES, TABLERESULTS')

	DELETE FROM #fragreport
	WHERE IndexID IN (0,255)

	UPDATE #fragreport
	SET PrePost = 'POST'
	WHERE PrePost is NULL

	SET @tabcount = @tabcount + 1
	TRUNCATE TABLE #indexes
END

INSERT INTO #reindex([ObjectName],[IndexName])
SELECT #tables.[name], #fragreport.[IndexName]
FROM #fragreport
INNER JOIN #tables on #tables.tabid = #fragreport.objectid
WHERE #fragreport.IndexId NOT IN (0, 255)
AND (#fragreport.ScanDensity < 90 OR #fragreport.LogicalFragmentation > 10)
AND #fragreport.PrePost = 'POST'

SELECT @numreindexes = count(*) FROM #reindex

SET @recount = 1

WHILE @recount <= @numreindexes
BEGIN
	SELECT @tabname = ObjectName,
	       @indname = IndexName
	FROM #reindex
	WHERE rid = @recount
	
	EXEC('DBCC DBREINDEX([' + @dbname + '.' + @tabname + '],[' + @indname + '])')

	INSERT INTO #fragreport([ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation])
	EXEC('USE ' + @dbname + ' DBCC SHOWCONTIG ([' + @tabname + '],[' + @indname + ']) WITH TABLERESULTS')

	SET @recount = @recount + 1
END

UPDATE #fragreport
SET PrePost = 'REINDEXED'
WHERE PrePost is NULL

UPDATE #fragreport
SET DBName = @dbname

INSERT INTO [master].[dbo].[fragreport]([timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost])
SELECT [timestamp], [ObjectName], [ObjectId], [IndexName], [IndexId], [Level], [Pages], [Rows], [MinimumRecordSize], [MaximumRecordSize], [AverageRecordSize], [ForwardedRecords], [Extents], [ExtentSwitches], [AverageFreeBytes], [AveragePageDensity], [ScanDensity], [BestCount], [ActualCount], [LogicalFragmentation], [ExtentFragmentation], [DBName], [PrePost] FROM #fragreport

DROP TABLE #tables
DROP TABLE #indexes
DROP TABLE #fragreport
DROP TABLE #reindex


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO