Reindex Procedure with a Twist (or two)

,

We’ve all seen reindex procedures that rebuild all indexes in a database or all indexes in a database which have less than a specified ScanDensity. This one goes a few steps further. First, the DBA can specify a ScanDensity_Threshold (a min value for ScanDensity), plus a Limit (qty or %). If you specify a ScanDensity_Threshold of 80, and a Limit of 50%, then only the worst 50% of all indexes in the database with a ScanDensity < 80 will be rebuilt. It determines the “worst” n% of all indexes by calculating a Reindex_Factor based on ScanDensity and PageCount. An index with a ScanDensity of 70 and 2000 pages will be reindexed before an index with a ScanDensity of 25 and 2 pages. After it finishes, you can query the DBA_Index_History table to see which indexes were rebuilt and why. The DBA has the option to specify a FillFactor_Age_Limit. If an index is rebuilt a second time within the FillFactor_Age_Limit timeframe, the FillFactor will be reduced by 5, to a minimum of 70. (If this is not desired, simply set FillFactor_Age_Limit = 0). An alert can be sent to the DBA when an index’s FillFactor reaches 70 (commented out. You may insert your own alert method). This procedure is written to take advantage of a “DBA” database which I have on each of my SQL Servers. If you store your maintenance procedures in the master database, you will need to take this into account, but I highly encourage you to move them into a “DBA” database. It greatly simplifies management! There is much documentation in the code. Please examine the procedure carefully understand how it works before you implement it. This version of the procedure is only tested in SQL 2000. With that, I hope you find this procedure as useful as I have.

/*
NOTE: A colleague recently asked me to implement reindex history in this process.  In working on that, I discovered that the procedure was NOT reindexing the way I thought it was.  The procedure was designed with the assumption that rebuilding a clustered index would automatically reindex all non-clustered indexes.  I have 
learned that this is only true for NON-UNIQUE clustered indexes.  Because of this, the procedure has been significantly modified.  If you have downloaded and implemented this procedure, please stop using it and download the new version.  The new version adds a history table, and a history load procedure.  Eventually, I will incorporate the history load logic into the reindex procedure.  Also, we are 
not currently using the fill factor logic in my shop, and I have other fires to fight, so the fill factor logic is untested in this version.  If you are, or want to use it, please test it first! 

Steve
*/

-- DBA_dbReindex (2k)


USE DBA
GO


--  Verify existence of DBA_dbReindex_showcontig table.  If it does not exist, create it.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_dbReindex_showcontig]') and 
	OBJECTPROPERTY(id, N'IsTable') = 1)
	print 'DBA_dbReindex_showcontig table exists.  it will not be created.'
else
	begin
		print 'creating DBA_dbReindex_showcontig_table.'
		CREATE TABLE [DBA_dbReindex_showcontig] (
			[DBName] [char] (128) NULL ,
			[ObjectName] [char] (128) NULL ,
			[ObjectId] [int] NULL ,
			[IndexName] [char] (128) NULL ,
			[IndexId] [int] NULL ,
			[Lvl] [int] NULL ,
			[CountPages] [int] NULL ,
			[CountRows] [int] NULL ,
			[MinRecSize] [int] NULL ,
			[MaxRecSize] [int] NULL ,
			[AvgRecSize] [int] NULL ,
			[ForRecCount] [int] NULL ,
			[Extents] [int] NULL ,
			[ExtentSwitches] [int] NULL ,
			[AvgFreeBytes] [int] NULL ,
			[AvgPageDensity] [int] NULL ,
			[ScanDensity] [decimal](18, 0) NULL ,
			[BestCount] [int] NULL ,
			[ActualCount] [int] NULL ,
			[LogicalFrag] [decimal](18, 0) NULL ,
			[ExtentFrag] [decimal](18, 0) NULL 
		) ON [PRIMARY]
	end
GO




-- Verify existence of DBA_Index_Stats table.  If it does not exist, create it.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_Index_Stats]') and 
	OBJECTPROPERTY(id, N'IsTable') = 1)
	print 'DBA_Index_Stats table exists.  it will not be created.'
else
	begin
		print 'creating DBA_Index_Stats table.'
		CREATE TABLE [DBA_Index_Stats] (
			[DbName] [varchar] (128) NULL ,
			[TableName] [varchar] (128) NULL ,
			[OwnerName] [varchar] (128) NULL ,
			[IndexName] [varchar] (128) NULL ,
			[ClusteredYN] [tinyint] NULL ,
			[FillFactor] [tinyint] NULL ,
			[LogicalFrag] [decimal](18, 0) NULL ,
			[ScanDensity] [numeric](5, 0) NULL ,
			[Rows] [int] NULL ,
			[Pages] [int] NULL ,
			[Reindex_Factor] [int] NOT NULL CONSTRAINT [DF__DBA_Index___Reind__6E01572D] DEFAULT (0),
			[Process_Flag] [tinyint] NULL ,
			[FillFactor_upd_flag] [tinyint] NULL ,
			[Process_Date] [datetime] NULL 
		) ON [PRIMARY]
	end
GO


-- Verify existence of DBA_Index_History table.  If it does not exist, create it.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_Index_History]') and 
	OBJECTPROPERTY(id, N'IsTable') = 1)
	print 'DBA_Index_History table exists.  it will not be created.'
else
	begin
		print 'creating DBA_Index_History table.'
		CREATE TABLE [DBA_Index_History] (
			[DbName] [varchar] (128) NULL ,
			[TableName] [varchar] (128) NULL ,
			[OwnerName] [varchar] (128) NULL ,
			[IndexName] [varchar] (128) NULL ,
			[ClusteredYN] [tinyint] NULL ,
			[FillFactor] [tinyint] NULL ,
			[LogicalFrag] [decimal](18, 0) NULL ,
			[ScanDensity] [numeric](5, 0) NULL ,
			[Rows] [int] NULL ,
			[Pages] [int] NULL ,
			[Reindex_Factor] [int] NULL ,
			[Process_Flag] [tinyint] NULL ,
			[FillFactor_upd_flag] [tinyint] NULL ,
			[Process_Date] [datetime] NULL ,
			[Run_Date] [datetime] DEFAULT getdate()
		) ON [PRIMARY]
	end
GO



-- DBA_dbreindex_history_load

USE DBA

if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_dbReindex_history_load]') and 
	OBJECTPROPERTY(id, N'IsProcedure') = 1)
	begin
		print 'dropping existing DBA_dbReindex_history_load procedure.'
		drop procedure [dbo].[DBA_dbReindex_history_load]
	end
else
	print 'DBA_dbReindex_history_load does not currently exist.'

print 'creating DBA_dbReindex_history_load procedure.'
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE  PROCEDURE  DBA_dbreindex_history_load

	(	@dbName sysname = NULL, 
		@retention int = 1,
		@retention_unit varchar(10) = 'month')

AS                        
SET NOCOUNT ON

/*************************************************************/                                                                                                                                                      
--                                                   
-- Module Name: DBA_dbreindex_history_load   
--                                                
-- Description: 
--   3 input parm(s). 
--		 @dbName sysname : Name of the database that reindex data will be archived for.                 
--     @retention int : history will be held for this number of retention units.
--     @retention_unit varchar(10) : units for @retention (for the DATEADD function).
--				Valid @retention_units are day, week, month, quarter, year.
--
-- Procedure description.
--	This procedure migrates DBA_index_stats data to the DBA_index_history table, and deletes
-- the old history from the table.  Retention defaults to one month.
                                                  
-- Written By: Steve Phelps            
--                                                  
-- Date: June 02, 2005         
--                                                   
-- Modified :                                       
-- Date: 
-- Change:                                           
--                                                  
-- USAGE:                                            
--                                                  
--   exec DBA_dbreindex_history_load @databasename, @retention, @retention_unit     
--                            
/*************************************************************/   

PRINT 'Version: 2005/06/08 14:30:00
' 

-- the following declare must be removed. its here for testing.
-- DECLARE
--	@dbName sysname,
-- 	@retention int,
-- 	@retention_unit varchar(10),  -- day, week, month, quarter, year
-- SELECT 
--	@dbName = 'RSVP_Production',
--	@retention = 1, 
--	@retention_unit = 'month'
-- end of test logic


DECLARE
	@MinDate datetime

IF @dbName is NULL
	BEGIN
	RAISERROR ('Database name must be entered.', 16, 1)
	RETURN -1
	END

IF @retention_unit NOT IN ('day', 'week', 'month', 'quarter', 'year')
	BEGIN
	RAISERROR ('Valid @retention_units are day, week, month, quarter, year.', 16, 1)
	RETURN -1
	END

SELECT @retention = @retention * -1

SELECT @MinDate = 
	CASE
		WHEN @retention_unit = 'day' THEN DATEADD(day, @retention, getdate())
		WHEN @retention_unit = 'week' THEN DATEADD(week, @retention, getdate())
		WHEN @retention_unit = 'month' THEN DATEADD(month, @retention, getdate())
		WHEN @retention_unit = 'quarter' THEN DATEADD(quarter, @retention, getdate())
		WHEN @retention_unit = 'year' THEN DATEADD(year, @retention, getdate())
	END 

-- delete historical data which is older than the retention period.
DELETE FROM DBA..DBA_Index_History
	WHERE Run_Date < @MinDate
	AND DbName = @dbName

-- Load the current run's data into the history table.
INSERT INTO [DBA].[dbo].[DBA_Index_History]
		([DbName], [TableName], [OwnerName], [IndexName], 
		[ClusteredYN], [FillFactor], [LogicalFrag], [ScanDensity], 
		[Rows], [Pages], [Reindex_Factor], [Process_Flag], 
		[FillFactor_upd_flag], [Process_Date])
	SELECT 
		[DbName], [TableName], [OwnerName], [IndexName], 
		[ClusteredYN], [FillFactor], [LogicalFrag], [ScanDensity], 
		[Rows], [Pages], [Reindex_Factor], [Process_Flag], 
		[FillFactor_upd_flag], [Process_Date] 
	FROM [DBA].[dbo].[DBA_Index_Stats]
	WHERE DbName = @dbName


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO





-- Verify existence of DBA_dbReindex procedure.  If it does exist, drop it, so that it can be re-created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[DBA_dbReindex]') and 
	OBJECTPROPERTY(id, N'IsProcedure') = 1)
	begin
		print 'dropping existing DBA_dbReindex procedure.'
		drop procedure [dbo].[DBA_dbReindex]
	end
else
	print 'DBA_dbReindex does not currently exist.'

print 'creating DBA_dbReindex procedure.'
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE dbo.DBA_dbReindex
	@dbName						varchar(128),
	@limit						numeric,
	@limit_type             varchar(10),
	@ScanDensity_Threshold	int,
	@FillFactor_Age_Limit	int = 0,
	@Test_Run_Flag				int

/********************************************************************/                                                                                                                                                      
--                                                           
--  Module Name: DBA_dbReindex                                
--                                                           
--  Description: Receives 6 parms.                          
--    @dbName  : database for which reindex is run           
--    @limit : max number or percent of indexes to rebuild
--    @limit_type : allows for setting @limit as either 'percent' or
--      'count'.  if the parameter is populated with anything
--      other than percent (or %), count will be used.  please populate
--      with one of these two values, to ease understandability.  If
--      you wish to reindex the entire database, set @limit = 100 and 
--      @limit_type = 'percent'.           
--    @ScanDensity_Threshold: any index with scan density          
--      lower than this value will be candidate for rebuild
--		@FillFactor_Age_Limit: If an index has been rebuilt within the
--			number of days specified by this parameter, the fillfactor will
--			be reduced by 5, to a minimum of 70.  If the limit of 70 has been
--			reached, a pop-up message will be sent to the on-call dba.  With 
--			the default value of 0, no action is taken.   
--		@Test_Run_Flag: This flag controls procedure functionality.
--				values:
--						0: production, no limitations on functionality
--						1: test, execution of dbcc dbreindex is inhibited. 
--							dbcc dbreindex command is printed to the screen.
--							update statistics is inhibited.
--						2: all restrictions included in "1." plus
--							DBA.dbo.DBA_dbReindex_showcontig is not updated.
--                                                            
--    This procedure performs a reindex on a given            
--    database.  It runs DBCC ShowContig to retrieve         
--    information on the indexes so that a decision       
--    can be made which indexes need to be rebuilt and also
--		pulls information from sysobjects and sysindexes.          
--    It sorts the indexes from worst to best, and          
--    then rebuilds the '@limit' worst indexes.  
--
--  Written By:  Steve Phelps              
--                                                           
--  Date: December 27, 2002                                    
--                                                            
--  Modified : modified procedure to only explicitly reindex the clustered index if one exists.
--					Also changed procedure to use pages rather than rows in determining which indexes
--					to rebuild.                                               
--  Date: 6/12/2003                                                    
--                                                            
--  Modified : add LogicalFrag to DBA_Index_Stats.  Set reindex_factor to not null and provide
--          default value of 0.  Don't calc reindex_factor if scandensity < @ScanDensity_Threshold.
--          Add FillFactor_age_limit functionality. Removed FillFactor_Override.
--  Date: 2/05/2004                                                    
--                                                            
--  Modified : added logic to check for existence of table before executing DBCC DBREINDEX.  In
--					particular, Goldmine creates and deletes tables dynamically.  At least once, a table was
--					deleted after the showcontig, and before the reindex, causing the job to fail.                                              
--  Date:	3/4/2004                                                    
--                                                            
--  Modified :  fixed table existence logic.
--  Date: 1/12/2005 sbp                                                    
--
--  Modified : fixed test flag = 1, added "print version:"
--  Date:	3/9/2005    
--
--  Modified : changed logic to reindex only those indexes identified by reindex_factor.  Clustered
--	indexes are no longer treated differently.  removed update stats.  also added the DBA_index_history
--  table and wrote DBA_dbreindex_history_load, which should be run immediately after this procedure.
--  Eventually, DBA_dbreindex_history_load should be incorporated into this procedure.
--  Date:	6/8/2005 sbp   
--                                                            
--  USAGE:                                                    
--                                                            
--    -- Before implementing this procedure, be sure that     
--    -- you understand its usage, and are able to make      
--    -- an educated decision as to the values of the         
--    -- parameters, and the scheduling of the task.  This   
--    -- procedure can have a tremendous impact on the       
--    -- performance of a database, either positive or negative.  
--    --
--    --    You can determine the current fill factor for a particular 
--    --    index by running the following query:
--    --    USE pubs
--    --    SELECT INDEXPROPERTY(	OBJECT_ID('authors'),    <=  table name
--    --				'UPKCL_auidind',         <=  index name
--    --   				'IndexFillFactor')       <=  parameter specifying fill factor  
--
--		Before implementing this procedure, you should verify that the required tables
--		have been created in the DBA database.  This can be done by executing the script
--		DBA_dbReindex_create_tables.sql .  This procedure should also be placed in and
--		executed from the DBA database.  IF the original script is used to create this
--		procedure, this is all taken care of within the script!
--                                                                                                       
-- DECLARE
-- 	@dbName		varchar(128),
-- 	@limit		numeric,
-- 	@limit_type		varchar(10),
-- 	@ScanDensity_Threshold		int,
-- 	@FillFactor_Age_Limit		int,
-- 	@Test_Run_Flag		int
-- 
-- select @dbName = 'PUBS'                                
-- select @limit =  50    
-- select @limit_type = 'percent' -- or '%' or 'count'                             
-- select @ScanDensity_Threshold = 80
-- select @FillFactor_Age_Limit = 3                         
-- select @Test_Run_Flag = 0 -- 0 or 1 or 2
-- 
-- exec DBA_dbReindex @dbName, @limit, @limit_type,                 
-- 	@ScanDensity_Threshold, @FillFactor_Age_Limit, @Test_Run_Flag 
--
-- exec DBA_dbreindex_history_load @dbName, 1, 'month'          
--
/********************************************************************/   


AS
SET NOCOUNT ON
PRINT 'Version: 2005/06/08 14:30:00
'

-- -- The following Declarations are for test purposes only.
-- 
-- DECLARE
-- 	@dbName varchar(128),
-- 	@limit numeric,
-- 	@limit_type varchar(10),
-- 	@ScanDensity_Threshold int,
-- 	@FillFactor_Age_Limit int,
-- 	@Test_Run_Flag int
-- 
-- select @dbName = 'RSVP_Billing'                                
-- select @limit =  100    
-- select @limit_type = 'percent' -- or '%' or 'count'                             
-- select @ScanDensity_Threshold = 95
-- select @FillFactor_Age_Limit = null                         
-- select @Test_Run_Flag = 1 -- 0 or 1 or 2
-- 
-- -- end test declarations

DECLARE	
	@TableName			VARCHAR(128),
	@OwnerName			VARCHAR(128),
	@IndexName			VARCHAR(128),
	@FF_IndexName		VARCHAR(128),
	@FillFactor			INT,
	@Counter				INT,
	@Inner_Counter		INT,
	@Inner_Limit		INT,
	@ClusteredYN		INT,
	@Rows					INT,
	@Pages				INT,
	@Exit					TINYINT,
	@command				NVARCHAR(2000),
	@command2  NVARCHAR(2000),
	@parmlist NVARCHAR(100),
	@flag TINYINT,
	@job_status			TINYINT,
	@Todays_Date	DATETIME,
	@ScanDensity tinyint,
	@Reindex_Factor int,
	@txt varchar(500),
	@string nvarchar(500)

-- create a temporary table to hold data for indexes for which the fillfactor should be re-calculated.
CREATE TABLE #FillFactor_Table
	(
	#TblName varchar(128),
	#OwnerName varchar(128),
	#IndexName varchar(128),
	#FillFactor int,
	#Process_Flag tinyint,
	#Process_Date datetime,
	#FF_Flag tinyint default 0
	)

-- create a temporary table to hold the results of DBCC Showcontig
CREATE TABLE #Showcontig
	(
	[#ObjectName] [char] (128),
	[#ObjectId] [int],
	[#IndexName] [char] (128),
	[#IndexId] [int],
	[#Lvl] [int],
	[#CountPages] [int],
	[#CountRows] [int],
	[#MinRecSize] [int],
	[#MaxRecSize] [int],
	[#AvgRecSize] [int],
	[#ForRecCount] [int],
	[#Extents] [int] NULL,
	[#ExtentSwitches] [int],
	[#AvgFreeBytes] [int],
	[#AvgPageDensity] [int],
	[#ScanDensity] [decimal](18, 0),
	[#BestCount] [int],
	[#ActualCount] [int],
	[#LogicalFrag] [decimal](18, 0),
	[#ExtentFrag] [decimal](18, 0)
	)

set @Todays_Date = getdate()

-- delete records from table DBA.dbo.DBA_Index_Stats for current db after saving fill_factor and processing information.
		insert #FillFactor_Table
			select 
					TableName,
					OwnerName,
					IndexName,
					[FillFactor],
					Process_Flag,
					Process_Date,
					0
				from DBA_Index_Stats
				where	dbName = @dbName
				and Process_Flag in (1,2)

Delete 
	from DBA.dbo.DBA_Index_Stats
	where dbName = @dbName

--  delete records from DBA_dbReindex_showcontig for current db.
if @Test_Run_Flag <> 2
	begin
		Delete 
			from DBA.dbo.DBA_dbReindex_showcontig
			where dbName = @dbName

-- 	Build the command to execute a DBCC SHOWCONTIG on the specified database.  The
-- 	results will be stored in the DBA_dbReindex_showcontig table.
		select @command = 
			'USE ' + @dbName + ' 
			 DBCC SHOWCONTIG 
				WITH	FAST, 
						TABLERESULTS, 
						ALL_INDEXES, 
						NO_INFOMSGS'

-- 	print @command
		INSERT INTO #showcontig
		 	EXEC (@command)
		set @command = null

		INSERT INTO DBA.dbo.DBA_dbReindex_showcontig
			select
					@dbName,
					#ObjectName,
					#ObjectId,
					#IndexName,
					#IndexId,
					#Lvl,
					#CountPages,
					#CountRows,
					#MinRecSize,
					#MaxRecSize,
					#AvgRecSize,
					#ForRecCount,
					#Extents,
					#ExtentSwitches,
					#AvgFreeBytes,
					#AvgPageDensity,
					#ScanDensity,
					#BestCount,
					#ActualCount,
					#LogicalFrag,
					#ExtentFrag
				from #showcontig
	end -- if @Test_Run_Flag <> 2

--  Build the command to populate the DBA_Index_Stats table.  This is the table
--  that is used to determine which indexes should be rebuilt, and in what order.
--	Note:  
--		indid = 0 = Heap
--		indid = 1 = Clustered index 
--		indid = 2 - 249 = nonclustered index 
--		indid = 255 is NOT an index, it is an entry for tables specifying that the table has text
--			or image column(s).
--	This procedure will ignore indid = 0 or indid = 255!
select @command = 
	'SELECT ' + char(39) + @dbName + char(39) + ',
				SO.[name],
				SU.[name], 
				SI.[name], 
				CASE WHEN SI.indid = 1 THEN 1 ELSE 0 END,
				MAX(SI.OrigFillFactor),
				SC.[LogicalFrag], 
				SC.[ScanDensity],
				SI.[RowCnt],
				SC.[CountPages], 
				0,
				0,
				Null,
				Null
		FROM ' + @dbName + '.dbo.sysindexes SI
		LEFT JOIN ' + @dbName + '.dbo.sysobjects SO ON SI.[id] = SO.[id]
		LEFT JOIN DBA.dbo.DBA_dbreindex_showcontig SC ON SI.[id] = SC.[ObjectId]
				AND SI.[IndId] = SC.[IndexId]
				AND SC.dbName = ' + char(39) + @dbName + char(39) + '
		LEFT JOIN ' + @dbName + '.dbo.sysusers SU ON SO.uid = SU.uid
		WHERE SO.[xtype] = ''U'' 
				AND SO.[status] > 0
				AND SI.indid <> 0 
				AND SI.indid <> 255 
				AND SI.[dpages] > 0
		GROUP BY SO.[name], SU.[name], SI.[name], SI.indid, SC.[LogicalFrag], SC.[ScanDensity], SI.[RowCnt], SC.[CountPages]'

-- print @command
insert DBA.dbo.DBA_Index_Stats
 	EXEC (@command)
set @command = null

-- If the fillfactor age limit parameter is populated, calculate a new fill factor for indexes which have been
-- rebuilt within that time limit, and are candidates for rebuilding.  

/*------------------------------------------------------------------------------------------------------------
FLAW!!!  age limit can only work for the previous run!  NOT # of days, because of the way the #FillFactor_Table
is being populated.  

From an e-mail to Lezza -

Modification to fix the fillfactor routine would take care of the history as well.  But its going to require some 
thought to decide on the best way to handle it.  In the mean time, I COULD migrate the DBA_index_stats table to an 
archive table that would give you previous run information.  Could either overwrite it with each run, or put a 
"Run_Date" with current date default and then just do an insert.  Table would grow quickly though, so would need to 
have a process to remove old records.  Would be pretty simple to set up.  Actually, a history table with "Run_Date" 
would be useful in fixing the fill_factor logic as well.  The logic itself isn't a priority as we aren't currently 
using it.

This has been implemented, but the fill factor logic has not been tested!!!
------------------------------------------------------------------------------------------------------------*/

-- Note that we are looking at the DBA_Index_History table for previous run information.  NOT TESTED!!!
if @FillFactor_Age_Limit > 0
	update #FillFactor_Table
		set #FillFactor = 
			case
				when I.[FillFactor] = 0 and I.ScanDensity < @ScanDensity_Threshold then 95
				when I.[FillFactor] between 70 and 75 and I.ScanDensity < @ScanDensity_Threshold then 70
				when I.[FillFactor] between 75 and 99 and I.ScanDensity < @ScanDensity_Threshold then I.[FillFactor] - 5
				else I.[FillFactor] 
			end
		from DBA.dbo.DBA_Index_History I
		join #FillFactor_Table F
			on  I.dbName = @dbName
			and I.TableName = F.#tblname
			and I.OwnerName = F.#OwnerName
			and I.IndexName = F.#indexname
			and F.#Process_Flag = 1
			and		DateDiff(DD, Process_Date, @Todays_Date) <= @FillFactor_Age_Limit

-- The following code calculates a "priority" for reindexing based on Scan Density and the 
-- number of pages in the table.  Tables with more pages receive a higher priority, and 
-- indexes with a lower Scan Density receive a higher priority.  An index with a high Scan
-- density may receive a higher priority than one with an extremely low Scan Density if 
-- it has a much greater number of pages.  This is desirable, as a low Scan Density has 
-- little meaning in a table with few pages.  Also, a Reindex_Factor will not be calculated
-- if the ScanDensity = 100 or is greater than the threshold.
update DBA.dbo.DBA_Index_Stats
	set Reindex_Factor = (((100 - ScanDensity) / 100) * [Pages])
		from DBA.dbo.DBA_Index_Stats
		where ScanDensity < @ScanDensity_Threshold
		and dbName = @dbName

-- Get the number of indexes that are candidates for rebuilding.
select @Rows = count(*)
	from DBA.dbo.DBA_Index_Stats
	where ScanDensity <= @ScanDensity_Threshold
	and dbName = @dbName

-- If @limit_type is percent, calculate @limit percent of @Rows.  If @limit_type is any
-- value other than '%' or 'percent', 'count' is assumed, and @limit is used as a literal.
if @limit_type = '%' or 
	@limit_type = 'percent'
	begin 
		select @limit = (@limit * .01) * @Rows
	end

-- It is possible for @limit to be greater than the number of indexes that are candidates.  
-- If this occurs, reset @limit to be equal to @Rows.
if @limit > @Rows
	set @limit = @Rows

set @Exit = 0
set @Counter = 1
While @Counter <= @limit and @Exit = 0
 Begin
-- Retrieve data from DBA_Index_Stats to build the DBCC dbReindex command.  The table is read in
-- Reindex_Factor descending order.  Once a candidate has been selected, it is reindexed with
-- the DBCC DBREINDEX command, then Process_Flag is set to '1', eliminating it from the pool.  

-- Initiate processing for the current index.
	Select top 1
			@TableName = TableName,
			@OwnerName = OwnerName,
			@IndexName = IndexName,
			@FillFactor = [FillFactor]
		from DBA.dbo.DBA_Index_Stats
		where Process_Flag = 0
		and dbName = @dbName
		Order by
			Reindex_Factor desc,
			ScanDensity Asc,
			Pages Desc,
			LogicalFrag desc

-- Check if a record exists in #FillFactor_table.  If so, set the FillFactor for the 
-- DBCC DBReindex command = the value from #FillFactor_table.
	If exists(select 1 from #FillFactor_Table where #Tblname = @TableName and #IndexName = @IndexName)
		begin
			select @FillFactor = #FillFactor
				from #FillFactor_Table
				where #Tblname = @TableName 
				and #IndexName = @IndexName
			update #FillFactor_Table
				set #FF_Flag = 1
				where #Tblname = @TableName
				and #IndexName = @IndexName
		end

Process_Index:

-- Build the DBCC dbReindex command
	Set @command = 
  		'DBCC DBREINDEX ' + '(' + CHAR(39) + @dbName + '.' + @OwnerName + '.' + @TableName + CHAR(39)
		+ CHAR(44) + SPACE(1) + CHAR(39) + @IndexName + CHAR(39) + CHAR(44) 
		+ SPACE(1) + CONVERT(VARCHAR(3), RTRIM(@FillFactor)) + ')'
	
	print @command

	set @string = @dbName + '.' + @OwnerName + '.' + @TableName

-- Verify that the table still exists before executing the reindex.  Goldmine, in particular, has a nasty habit
-- of deleting tables when it sees fit.  This has caused this procedure to generate errors, leading to this solution.
	set @command2 = N'select @flag = 1 from ' + @DBName + N'.dbo.sysobjects where id = object_id(''' + @string + N''') and xtype = ''U'''
	set @parmlist = '@flag as tinyint OUTPUT'
	EXEC sp_executesql @command2, @parmlist, @flag OUTPUT
	if @Test_Run_Flag = 0 
		BEGIN
		if @flag = 1
			BEGIN
		 	exec sp_executesql @command
		END -- if @flag = 1
	END -- if @Test_Run_Flag = 0

-- Set Process_Flag in DBA_Index_Stats for the index we just rebuilt to 1, eliminating it from the potential
-- indexes to rebuild.  
		update DBA.dbo.DBA_Index_Stats
			set 
				Process_Flag = 1,
				Process_Date = @Todays_Date
			where dbName = @dbName
			and TableName = @TableName
			and	IndexName = @IndexName
	
	-- increment counter
	set @Counter = @Counter + 1

-- if all indexes that are candidates have been rebuilt, get out of the loop.
	if not exists (select 1 from DBA.dbo.DBA_Index_Stats 
		where dbName = @dbName and Process_Flag = 0 and ScanDensity < @ScanDensity_Threshold)
		set @Exit = 1 
 end -- While @Counter

-- Fill Factor notification here...
--/*

-- update DBA_Index_Stats with new fillfactor, and set FillFactor_upd_flag to show that the fillfactor was updated.
update DBA.dbo.DBA_Index_Stats
	set FillFactor_upd_flag = 1,
			[FillFactor] = F.#FillFactor
	from DBA.dbo.DBA_Index_Stats I
	join #FillFactor_Table F
			on I.TableName = F.#tblname
			and I.IndexName = F.#IndexName
			and I.[FillFactor] <> F.#FillFactor
			and I.dbName = @dbName
			and I.Process_Flag = 1


-- send an alert to the on call dba when any fillfactor reaches 70.  this alert should only be sent when
-- the fillfactor CHANGES to 70, not if it was 70 before running DBA_dbReindex.
-- Change the following code to check against DBA_index_history, checking the previous run for fillfactor > 70.  This will 
-- ensure that the alert will only be fired when the fillfactor actually CHANGES to 70.
-- if exists(select 1 from DBA.dbo.DBA_Index_Stats where [FillFactor] = 70 and FillFactor_upd_flag = 1 and dbName = @dbName)
-- 	exec a_AlertCall 15

cleanup:

drop table #FillFactor_Table
drop table #showcontig

-- test stuff 
/*
select * from #fillfactor_table
select * from DBA_Index_Stats order by dbName, Reindex_Factor desc
select * from DBA_dbReindex_showcontig
truncate table DBA_index_stats

select count(*) from DBA.dbo.DBA_Index_Stats

select count(*) from #fillfactor_table
*/
-- end test stuff

job_abend:
if @@error <> 0
	return @@error

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

5 (1)

Share

Share

Rate

5 (1)