In this article I will not be going through the matter of indexes.The purpose of the post is to provide a quick reference to a manually triggered re-indexing process. What is important to be understood is the fill-factor for every particular index. The fill-factor value defines the percentage of space in every index page to be filled with data and respectively defines the space left as free space for future growth.
Here is the recommended fill-factor values based on the reads-writes balance:
Tables with low number of updates
- ex. 100:1 read to write ratio: 100% fill-factor
Tables with high number of updates
- read to write ratio less than 1 (more writes than read) : 50%-70% fill-factor
Tables with average number of updates - 80%-90% fill-factor
DECLARE @from int
DECLARE @to int
DECLARE @fillFactor int
SET @from=1
SET @to=10000
SET @fillFactor=90
DECLARE @dateFrom datetime
DECLARE @table varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name from(
SELECT ROW_NUMBER() OVER (ORDER BY table_name) AS [SortNum], table_name FROM information_schema.tables
WHERE table_type = 'base table')
AS t1 WHERE sortnum>=@from and sortnum<=@to
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY SET @dateFrom = getdate()
DBCC DBREINDEX(@table,' ',@fillFactor)
print 'Table ['+@table+'] indexed for: '+ CAST( DATEDIFF ( ms , @dateFrom , getDate() ) as nvarchar(10)) + ' ms'
END TRY
BEGIN CATCH
print 'Number of errors: '+CAST(ISNULL( ERROR_NUMBER(),'') as nvarchar)
print 'Error: '+ISNULL(ERROR_MESSAGE(),'')
END CATCH
FETCH NEXT FROM TableCursor INTO @table
END
CLOSE TableCursor
DEALLOCATE TableCursor
