As part of a weekly database job that runs out-of-hours, I re-index all the tables in a particular reporting database. After playing around with index fillfactor I arrived at a fill factor of 90 as the tables are mostly being read and only being updated a couple of times a month. For my key tables, I made sure I set up clustered indexes on the fields that were being used most commonly in my queries (which of course speeds data access up considerably)
I discovered a simple script that would rebuild indexes on all tables in a database to have a fill factor of 90 (see http://www.sql-server-performance.com/rebuilding_indexes.asp), but I wanted more customisation so wrote a script to rebuild all indexes with their original fill factor (some of may tables are set higher as the table is never updated, and some are set lower as the table is updated more often).
So, here's the script, which doesn't actually do the re-indexing, just PRINTs the relevant statements to the Messages window (if using Query Analyzer):
--table name variable
DECLARE @TableName VARCHAR(255)
--index name variable
DECLARE @IndexName VARCHAR(255)
--original fill afctor variable
DECLARE @OriginalFillFactor TINYINT
--override fill factor (can be NULL)
DECLARE @OverrideFillFactor TINYINT
--set override fill factor for indexes here, if required. This WILL overwrite the original value
--for each index! (If commented out, original fill factor will be used)
--SET @OverrideFillFactor = 90
--temporary table for holding all indexes
CREATE TABLE #tempIndexes
(
index_name VARCHAR(255),
index_description VARCHAR(210),
index_keys NVARCHAR(2048)
)
--temporary table for holding table name, index name, index id and fillfactor
DECLARE @temp TABLE (
TableName VARCHAR(255),
IndexName VARCHAR(255),
IndexId INT,
OriginalFillFactor TINYINT
)
--set NOCOUNT ON
SET NOCOUNT ON
--get all the tables using INFORMATION_SCHEMA.TABLES into a cursor
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--open the cursor
OPEN TableCursor
--get the first row
FETCH NEXT FROM TableCursor INTO @TableName
--if we got a valid row, continue
WHILE @@FETCH_STATUS = 0 BEGIN
--clear out the #temp table, containing indexes for the passed table
DELETE FROM #tempIndexes
--get indexes into #tempIndexes. This may print a warning message "The object does not
--have any indexes.", which can be ignored
INSERT INTO #tempIndexes EXEC sp_helpindex @TableName
--insert into outer table, the table name, the index name
INSERT INTO @temp ([TableName], [IndexName])
SELECT @TableName, index_name
FROM #tempIndexes
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
--drop the temp index table
DROP TABLE #tempIndexes
--now get the index id and original fill factors
UPDATE @temp
SET IndexId = i.[indid], OriginalFillFactor = i.[OrigFillFactor]
FROM sysindexes i, sysobjects o, @temp T
WHERE i.[id] = o.[id] AND o.[name] = T.TableName AND T.IndexName = i.[name]
--lastly loop through the @temp table and re-index
DECLARE DBREINDEXCursor CURSOR FOR
SELECT TableName, IndexName, OriginalFillFactor
FROM @temp
--open cursor
OPEN DBREINDEXCursor
--get first row into local variables
FETCH NEXT
FROM DBREINDEXCursor
INTO @TableName, @IndexName, @OriginalFillFactor
--loop through cursor while there are rows remaining
WHILE @@FETCH_STATUS = 0
BEGIN
--if we've been given an override fill factor, apply it here
IF NOT (@OverrideFillFactor IS NULL) SET @OriginalFillFactor = @OverrideFillFactor
--do the re-index operation here
PRINT 'DBCC DBREINDEX(' + @TableName + ',' + @IndexName +
',' + CONVERT(VARCHAR(3), @OriginalFillFactor) + ')'
--get the next row
FETCH NEXT
FROM DBREINDEXCursor
INTO @TableName, @IndexName, @OriginalFillFactor
END
--close and deallocate the cursor
CLOSE DBREINDEXCursor
DEALLOCATE DBREINDEXCursor
GO
Some indexing resources I found helpful are at http://www.c-sharpcorner.com/Code/2004/March/SQLPerformanceChecklist06.asp, http://www.extremeexperts.com/sql/articles/BestPractices.aspx and http://www.sql-server-performance.com/rebuilding_indexes.asp.