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


Keep Your SQL Server Indexes Fragmentation Free : Page 3

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


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.

Francisco Tapia is a database administrator working for a large manufacturing company. He has been working with SQL Server since version 6.5. You can read more from Tapia at sqlthis.blogspot.com.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date