INDEXDEFRAG questions

[LawnBoy]

Please be gentle with a lowly SysAd who doesn't know enough about SQL to fill a thimble...

Users of one of my databases have been complaining about slow response when searching. One particular report is taking over 10 times as long as it did 6 months ago. I ran

DBCC SHOWCONTIG WITH ALL_INDEXES

against the database in question and it shows some serious fragmentation (one table's index has 337272 pages and shows 64.41% Logical Scan Fragmentation).

In researching BOL I think my best option would be to run an INDEXDEFRAG since I cannot take the database offline. However, BOL has an interesting statement:

DBCC INDEXDEFRAG is not supported for use on system tables.

The DB I want to defrag happens to have 20 tables listed as system (out of 412 tables). Does this mean that INDEXDEFRAG simply does not work on system tables or does it mean INDEXDEFRAG could screw up the system tables?

Will I have to run INDEXDEFRAG against each of the non-system tables one at a time? (if so, please just shoot me).

 

[LawnBoy]

With a little more research on TechNet I've learned that I'll have to perform INDEXDEFRAG against each index of each table.

Plans for the weekend...

 

[sqlcasey]

Just fyi, you may also want to consider DBCC DBREINDEX instead of DEFRAG, IF you can afford to have the tables locked up, say you are doing this over the weekend when no one is accessing the tables.

DBREINDEX has the added advantage of actually reordering the rows in the table to match the keys of the index when you run the DBREINDEX on the CLUSTERED index of the table. That's because the clustered index keys and the table rows are one and the same.

Be sure to set the fillfactor to something less than 100% on tables that are updated frequently.

From BOL:

Unlike DBCC INDEXDEFRAG, or ALTER INDEX with the REORGANIZE option, DBCC DBREINDEX is an offline operation. If a nonclustered index is being rebuilt, a shared lock is held on the table in question for the duration of the operation. This prevents modifications to the table. If the clustered index is being rebuilt, an exclusive table lock is held. This prevents any table access, therefore effectively making the table offline. Use the ALTER INDEX REBUILD statement with the ONLINE option to perform an index rebuild online, or to control the degree of parallelism during the index rebuild operation.