Rebuilding SQL Server indexes
- Last UpdatedOct 29, 2024
- 2 minute read
Periodically, you will need to perform a database reorganization on the indexes and tables in your database. This rebuilds the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads and slows down performance of the SQL Server.
If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table are automatically rebuilt.
Use Maintenance Wizard or SQL Server Agent
Database reorganizations can be done with the Maintenance Wizard, or by running your own custom script through the SQL Server Agent.
Fill Factor
When you create or rebuild an index, you can specify a fill factor, which is the amount the data pages in the index that are filled. A fill factor of 100 means that each index page is 100% full, a fill factor of 50% means each index page is 50% full. If you create a clustered index that has a fill factor of 100, and it is not based on a monotonically increasing key, that means that each time a record is inserted (or perhaps updated), page splits occur because there is no room for the data in the existing pages. Numerous page splits can slow down SQL Server's performance.
The ideal fill factor depends on the ratio of reads to writes that your application makes to your SQL Server tables. Follow these guidelines to determine fill factor:
-
Low Update Tables (100-1 read to write ratio): 100% fill factor.
-
High Update Tables (where writes exceed reads): 50%-70% fill factor.
-
Everything In-Between: 80%-90% fill factor.