Thursday, February 10, 2011

SQL Server Reorganizing and Rebuilding Indexes

"The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly."

"You can remedy index fragmentation by either reorganizing an index or by rebuilding an index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or on a single partition of an index."