Fragmented Index Advisor

Use the Fragmented Indexes Advisor to defragment indexes ad-hoc.  The better option instead is to schedule a nightly task to defrag indexes when the system load is low.

You get to the Fragmented Indexes Advisor by clicking one of the indexes shown on the Fragmented Indexes Panel.

Defragmentation – Enterprise vs Standard Edition

The method for  defragmentation varies depend on the version of SQL Server you are using, Enterprise or Standard Edition.   If you are using SQL Express follow the instructions for Standard Edition, for SQL Developer follow the instructions for Enterprise Edition.

The general recommendation for defragmenting is the following:

Fragmentation between 5% and 30% then REORGANIZE

ALTER INDEX REORGANIZE

For Fragmentation greater than 30% then REBUILD

ALTER INDEX REBUILD WITH (ONLINE = ON)

The problem that you run into  with these suggested values is Standard Edition or Express Edition of SQL Server where the ONLINE = ON option is not available.  What this means is that if you REBUILD your index in standard edition, then the index will be offline during the rebuild, which could seriously impact the performance of your system.

Standard Edition Defragmentation

With Standard Edition, unless you schedule a downtime, you are always going to want to REORGANIZE, as REBUILDing will take the index off-line.

Enterprise Edition Defragmentation

With Enterprise Edition the cool feature that you get is the ability to REBUILD an index while it is online.  Effectively what this does is it leaves the current index in place while it builds a new one.  One the new one is built, it then switches it out for the old one and deletes the old index.

Manual Defrag vs. Nightly Defrag

Instead of manually defragmenting each index, it may make more sense to install a job to run nightly and defragment the worst fragmented indexes.  More to come on this soon.

DBCC INDEXDEFRAG (Obsolete)

If you have been using DBCC INDEXDEFRAG, stop using it now, and start using one of the methods described above instead.   DBCC INDEXDEFRAG is obsolete, and has been removed from SQL Server 2012.

 Why not just use the Maintenance Plan Wizard to set up a defragmentation script?

The maintenance plan wizard required you to pick to defragment all indexes in a database, or to fragment specific indexes.  On larger database defragmenting all can cause some serious performance problems, and defragmenting one or a small group can be very tedious to pick out just the right indexes to defragment each time it is run.