Keeping an Eye on Things
You need to be aware of some restrictions to invoking the REORGANIZE option. Certainly, if you've disabled an index (a new SQL Server 2005 feature), it can't be defragmented. Also, because the process of removing fragmentation needs to work on individual pages, you'll get an error if you try to reorganize an index for which the ALLOW_PAGE_LOCKS option is set to OFF.You can't reorganize an index when a concurrent online index build exists on that index or when another process is concurrently reorganizing the index.
SQL Server 2005 doesn't return a report at the completion of the reorganization, like SQL Server 2000 does. (The report is a row of output that contains the number of pages in the table, the number of pages moved, and the number of pages removed.) However, you can observe the progress of each index's reorganization by observing the complete_ percentage and estimated_completion_time columns in the sys.dm_exec_requests Dynamic Management View (DMV). The sys.dm_exec_requests view is one of the DMVs that you should use as a replacement for the SQL Server 2000 sysprocesses table.The value in the complete_percentage column reports the percentage completed in one index's reorganization; the estimated_completion_time column shows the estimated time (in milliseconds) required to complete the remainder of the index reorganization. If you're reorganizing multiple indexes in the same command, you might see these values go up and down as each index is defragmented in turn. (Both columns also keep track of progress for commands other than index defragmenting. These commands include the DBCC CHECK commands, DBCC SHRINKDATABASE, DBCC SHRINKFILE, BACKUP/RESTORE, and ROLLBACK. In a future article, I'll go into these progress-report values in more detail and explain how to make use of all the DMVs that contain data similar to the sysprocesses table.)
Get It Together
The internal process of removing fragmentation in SQL Server 2005 is similar to the process in SQL Server 2000. However, the command for invoking the defragmentation operation (the ALTER INDEX command with the REORGANIZE option) is different. This syntax presents options that aren't available in earlier SQL Server versions, such as the option to remove fragmentation from LOB data columns. SQL Server 2005 also provides more options for keeping track of the defragmentation's progress, using the new sys.dm_exec_requests DMV.
Once again, keep in mind that fragmentation isn't always bad (it's rarely good, but not good isn't the same thing as bad in this case).You can compare it to a messy desk on which you nevertheless know where everything is.When you're looking for one particular paper, you can find it quickly, but finding all the papers relating to a project you're working on will take much longer. In that situation, a nicely organized desk is a better option.
Kalen Delaney (kalen@solidqualitylearning.com) is a principal mentor of Solid Quality Learning and provides SQL Server training and consulting to clients around the world. Her most recent book is Inside Microsoft SQL Server 2000 (Microsoft Press).