Keep Your SQL Server Indexes Fragmentation Free

Keep Your SQL Server Indexes Fragmentation Free

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.

The Defragmenting Code

The index-defragmenting code is composed of one script in two parts. The first part scans the indexes to find tables that you need to defragment. The second part, which contains the first part, generates all the statistics for the specified database’s tables and indexes, and e-mails them to an administrator.

Figure 1. The Initial Run: This first part of the script reports the tables that have fragmented indexes.

You run the first part to locate the tables that require defragmenting. Choose a time when the system is mostly idle. To run the script in Management Studio (as shown in Figure 1), do the following:

  1. Choose Open from the File menu.
  2. Select File and locate the script.
  3. From the Database dropdown control, choose the database you want to check. Alternatively, you can add a USE mydatabase statement at the top of the script.
  4. Press F5 or click the Execute button.

Figure 2 shows the retrieved statistics from the resulting defrag report (against a local database). In addition, the script sends an e-mail containing the status and time of each occurrence to a specified recipient (see Figure 3). Be sure to update the script so that it sends the e-mail to an appropriate person within your organization.

When you’re ready to defrag the indexes, you run the second part.

Figure 2. Defrag Statistics: The script generates a table named db_defrag for reporting the statistics on the specified database’s tables and indexes.
Figure 3. E-mail to Admin: The script e-mails the specified administrator.

Inside the Defrag Script

The first script uses SAMPLED scan mode, which means that the function scans all upper pages in the index and provides an estimated count of the compressed pages (actual pages in the index). If you have a table with less than 10,000 pages, the function will automatically run in DETAILED scan mode. However, a detailed scan may be too time-consuming for a large database.

The second script creates a backup and then turns off backup operations. Doing so avoids the problems that crop up when a transaction log grows. Next, the script identifies the table with the most rows and begins to defrag or reorganize its indexes. If the fragmentation level is over 30%, the script attempts to rebuild the index.

The MAXDOP=2 option limits the number of processors to two. You can change this number; the default uses all available processors as designated in your SQL Server configuration. Consider setting this option to use at least one fewer processor than your system’s total number. This will leave at least one processor for the operating system.

The ONLINE=ON option makes the index and table available if a user logs on during the rebuild (for the Enterprise version of SQL Server 2005 and 2008). The try-catch error handling allows the rebuild to occur offline in case the index rebuild is not possible with the ONLINE option. This can happen when you have text or other BLOB type fields that need compacting. When this is the case, SQL Server must rebuild the index offline. That means users can’t use the table or the index.

When the rebuild is complete, the code updates statistics so the query engine optimizer can make use of the index immediately. The code also updates the report table and clears the variables for the next item in the initial defrag report.

Figure 4. Comparison Report: The script reports fragmentation improvements for each table.

If the fragmentation is between 5% and 30%, the code runs a reorganization, followed by an update of the statistics and final cleanup. When the loop finishes, the code enables all disabled backup jobs.

The final part of the code reruns the defrag report and compares it with the initial report in order to report the level of fragmentation improvement on each table (see Figure 4).

The second script contains the first script, so running the second script will generate all the statistics and e-mail. However, the first script allows you to generate all the statistics, without actually committing to do anything to the indexes.

Defrag for Optimal Performance

Use the index-defragmenting script described in this article to keep informed of the defragmentation process. If you don’t have database e-mail set up, refer to Sending E-mail from SQL Server 200X for help. In addition to defragging, consider setting an alert condition that reviews the log file and auto-truncates the log when it reaches 40% of its full size. Also consider performing a differential backup before you run the script, just to be safe. (Differentials are a bit faster than full backups.)

Remember, maintenance shouldn’t be an afterthought; it’s your daily bread.


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist