RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Keep Your SQL Server Indexes Fragmentation Free

Routine maintenance can keep index fragmentation to a minimum—with some help from an index-defragmenting script.

ou risk a great deal by neglecting your database. Without routine maintenance, a growing database becomes lethargic. As performance degrades, the potential for errors or a system crash increases. At the very least, your users will lose a bit of time when it comes to a screeching halt. More likely, your system will choke and you'll lose data—or possibly even your job.

Database maintenance involves several tasks. Some you schedule via built-in utilities (daily, weekly, and so on). Others, such as maintaining indexes, require a more hands-on approach. Luckily, in SQL Server you can manage index maintenance rather easily with a bit of code and a Dynamic Management View (DMV). The script in this article uses the index physical statistics DMV to glean information about indexes and determine when an index needs updating.

Defragmenting Indexes

For small databases and small tables, an index may offer very few advantages. However, if you expect your database to grow, you should use indexes. Without getting too technical, indexes are simply bookmarks (smaller file sets) that allow the database engine to quickly access data. Typically, an index contains the smallest organized set of data.

To keep your database's access to indexes as fast as possible, you should defragment your indexes—just as you would occasionally defragment files. Which tables are good candidates for defragmenting? Here are a few guidelines:

  • Tables with 100,000 rows or more
  • Tables with fewer records, but the number of columns and data type matter (The specifics are beyond the scope of this article.)
  • Tables fragmented more than 5%

You can rebuild or reorganize an index to ensure good performance, but determining which method will work best for this task takes a bit of analysis. First, you need to find out which indexes are mostly fragmented. SQL Server's DMV dm_db_index_physical_stats is useful for acquiring this information. The DMV dm_db_index_physical_stats works with a few parameters (database ID; table object ID; index ID; partition number; and scan level) to collect the necessary data for auto-defragmenting a database.

When you have determined which indexes are mostly fragmented, here are the guidelines for how to proceed:

  • If an index is 5-30% fragmented, reorganize the index.
  • If an index is over 30% fragmented, rebuild the index.

There is one catch: even if an index is more than 30% fragmented, a rebuild may not help if the database contains small tables with small indexes. In that case, you may have to manually drop and rebuild the index.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date