Queries Dragging? Try Defragging

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.

Discuss this Article 6

Philip (not verified)

on Aug 2, 2007

Very useful. I worked in a check for Edition that added " with (online = on)" for Enterprise and Developer edition; also error checking for blowups (online updates on some indexes involving blob-type columes). Couldn't figure out how to make it work as a "master.dbo.SP_ " procedure, so it has to sit in every user database (grumble). How does this work with XML indexes?

This stored Proc has been very useful. Good Job !!! But I have been getting an error with the execution 'N' option: the error is : Line 135, Ambiguous name column 'Type_Desc'. The SQL version is 2005 SPK2 64bit and the OS is windows 2008 64 bit. everything runs well on the same table of info/indexes in SQL 2005 SPK2 32bit and OS of windows 2003 R2. Any Ideas ? Bad data or indexes?

Sorry that I didnt include a 2k version. All of my servers are using SS2005 and the system view sys.dm_db_index_physical_stats dont exist in SS2000. I will have to look around and see if I can re-create the dm_inded_physical_stats using SS2000 system tables.
I couldnt figure out how to use it only in the master database either. I havent tried it on XML indexes. I would love to see the version for enterprise version with the online = on. Please send it to eric@petersonamerican.com

From the Blogs

Duplicate records clutter databases and render the data within them unclear. This kind of problem is very common, and it’s the main reason that deduping software exists. But there’s another benefit to deduplication software: the ability to infer connections between individual records from various data sets....More

Companies looking to grow and extract value from their data are increasingly turning to Chief Data Officers (CDOs) to execute their data strategy. The role is new, and a playbook is necessary to address the many challenges CDOs face....More

After spending 20 years building analytics, BI and database solutions, I've focused on Cloud data solutions over the past 2 years. I've chosen 5 common challenges that I face every day with Cloud migrations and that you'll face in your Cloud BI projects....More