Brad’s Sure Guide to SQL Server Maintenance Plans- P24: SQL Server has a reputation as being a simple database application to install, configure, and maintain. This is a little misleading. SQL Server is a powerful relational database that can handle the needs of the largest organizations and, as such, its proper maintenance almost certainly requires the attention of an experienced DBA. | Chapter 7 Rebuild Index Task issues because it results in index fragmentation the very thing we are trying to eliminate with the Rebuild Index task. In order to mitigate this problem DBAs often decrease the fill factor to perhaps 90 meaning that data pages will be 90 full leaving 10 free space. For more information regarding fill factors and page splitting. .refer to Books Online. A full discussion of these topics is beyond the scope of this book but I needed to include a little background so you could better understand what is happening when you make particular selections within the Wizard. Also don t assume that my example of a fill factor of 90 is appropriate for your indexes. It may be or it may not be. What is really confusing is that the phrase default amount of free space in the Wizard does not mean the same thing as the default fill factor that can be set for the entire server. Some people confuse the two. In the Rebuild Index task default amount of free space refers to the fill factor that was used when a specific index was first built or last rebuilt. In other words if you choose the option Reorganize pages with the default amount of free space what happens is that each index is rebuilt using whatever fill factor value was used the last time it was rebuilt. This may be the same as the server-wide default or it may be a specific value that was specified for that index or it may be a value set using the second Change free space per page percentage to option discussed next . In almost all cases the default amount of free space option is the one you want to use as it means the index will be rebuilt using the fill factor that was originally specified when the index was created. With the second option Change free space per page percentage to you specify a single fill factor value to be used for every index when it is rebuilt. For example if you choose Change free space per page percentage to and set it to 10 this is the same thing as setting all of the indexes
