Did you ever have a user tell you a query is taking a lot longer to compete than before, even though nothing in it has changed? If so, there's a good chance that the indexes in the table that the query ran against have become fragmented. Fixing this problem is a two-step process. First, you need to first determine which indexes have become fragmented. Second, you need to defrag those indexes. I wrote a stored procedure, cspDefragIndexes, that automatically performs both steps. You can use cspDefragIndexes to analyze all the indexes in a single table or a whole database to determine whether they're fragmented. You can also use cspDefragIndexes to defrag that table or database. The stored procedure even updates all the statistics.
You can download the cspDefragIndexes stored procedure using the "Download the Code" link at the top of the page. To run it, you need to provide two parameters. The first parameter is the table name. Or, you can specify 'ALL' to work with all the tables in the database. The second parameter tells the stored procedure to either display the indexes and their percentage of fragmentation (specify 'N') or defrag the indexes (specify 'Y').
For example, if you want to check the Customer table to see how badly its indexes are fragmented, you use the command
cspDefragIndexes 'Customer', 'N'
Table 1 shows sample results. As you can see, most of the indexes are highly fragmented—even the clustered index is more than 80 percent fragmented. This table's indexes need to be defragged, so you run the command
cspDefragIndexes 'Customer', 'Y'
The csp_defragIndexes stored procedure rebuilds indexes whose fragmentation is 30 percent or higher, reorganizes indexes whose fragmentation is between 29 percent and 5 percent, and bypasses indexes whose fragmentation is less than 5 percent. An update of the statistics completes the process. Figure 5 shows the report that csp_defragIndexes displays on screen. As you can see, the report specifies the action taken for each index.
A quick rerun of csp_defragIndexes in display mode shows the improvements made by the defrag operation. As Table 2 shows, the percent of fragmentation is significantly less, which means the queries against the Customer table will run significantly faster.
I wrote csp_defragIndexes for use on the SQL Server 2005 Standard Edition. (It won't work on SQL Server 2000.) This stored procedure will incur table locks unless you have Enterprise Edition and you modify the procedure to do online rebuilds.
—Eric Peterson, President, Peterson American Consulting
