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 from the SQL Server
Magazine Web site. 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
End of Article
