I created a maintenance plan for our warehouse management system SQL Server database that included a rebuild of the indexes on all the tables. All went well except that the size of the database file just happened to triple after my plan ran! Not good. Since I'm learning to be a DBA by the seat of my pants it took me a while to figure out what was going on.
Before implementing the plan, I spent a lot of time researching the correct way to rebuild the indexes. I wanted to make sure we were doing things the way they should be done. I found an blog post about rebuilding the indexes with the correct Fill Factor so that the performance wouldn't suffer due to page faults and such. So after doing a little analysis and asking some questions to the boss, I came up with a Fill Factor percentage that I thought was suitable for the indexes on our tables.
Using the built in tool in SQL Server Management Studio to create my rebuild task I entered the percentage of free space that I wanted in my index pages just like the dialog says. THE AMOUNT OF FREE SPACE which to me means that THE FILL FACTOR WILL BE 100 MINUS THE VALUE I ENTER. Makes sense yes?
So I enter 30% because I want a Fill Factor of 70%. Well, that's not what happened! The T-SQL that was generated when the task ran gave me a Fill Factor of 30% and a free space of 70%. That made the size of the database file grow! I looked in sysindexes table to confirm my findings and saw that the OrigFillFactor values were indeed 30. I'm not sure what happened but when I checked the T-SQL myself by clicking the View T-SQL button on the dialog it showed a Fill Factor of 70. Because of this, I no longer trust the tool so I decided to write my own SQL to rebuild the indexes.
So I guess the purpose of this post is to help anyone else who might be having trouble with this. It took me quite a while to find the answer to this problem so hopefully this post will get picked up by Google and help someone out.
My code to rebuild the indexes is listed below. I ran this manually in SQL Management Studio and then had to shrink the database to get my database file size back to normal. Now, I know that shrinking the database fragments the indexes I just rebuilt but I'll do another rebuild after shrinking to get rid of the fragmenation.
USE [DatabaseName]
GO
DECLARE @currentTable VARCHAR(50)
DECLARE @cmd VARCHAR(1000)
DECLARE tableNameCursor CURSOR
FOR SELECT [name] FROM sysobjects WHERE sysobjects.[type] = 'U' ORDER BY [name] ASC
OPEN tableNameCursor
FETCH NEXT FROM tableNameCursor INTO @currentTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON [' + @currentTable + '] REBUILD WITH (FILLFACTOR = 95, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)'
EXEC (@cmd)
FETCH NEXT FROM tableNameCursor INTO @currentTable
END
CLOSE tableNameCursor
DEALLOCATE tableNameCursor
Make sure you type the database name in the first line and enter your own Fill Factor. I have 95 which equates to 95% and leaves 5% free space.