The Defragment and Optimization window allows you to defragment one or more instances and databases. The SQL Servers and Databases field lists all the registered instances and databases. You can select the databases you want to defragment, and then select the type of defragmentation approach you want SQL Defrag Manager to use. SQL Defrag Manager can simply reorganize indexes for optimal performance, or defragment indexes by completely rebuilding them.
Access the Defragment and Optimization window
To display the Defragment and Optimization window, click Defragment on the Fragmentation Explorer tab of the SQL Defrag Manager Console.
Selecting instances and databases to defragment
The SQL Servers and Databases field lists all the registered instances and databases. You can select an instance, which then selects all the databases in that instance. You can also select specific databases to defragment indexes in those individual databases.
If the instance or database you want to defragment is not listed in the SQL Servers and Databases field, you may not have registered it with SQL Defrag Manager. To register an instance with SQL Defrag Manager, click Register on the Fragmentation Explorer tab of the SQL Defrag Manager Console.
Difference between reorganize and rebuild
The type of defragmentation you choose depends on many considerations:
- Database availability
- Logical fragmentation level
- Parallel processor availability
- Page compaction
- Can you stop and restart the process without losing data?
- Can you untangle interleaved indexes?
- Is additional disk space available in the data file?
- Is the index large?
- Do you need to rebuild statistics?
- Should busy pages be skipped?
Reorganize: Directs SQL Defrag Manager to defragment the tables and indexes online. This defragmentation can be stopped without losing progress. The amount of log space used varies with the amount of work completed. During reorganization, SQL Defrag Manager skips pages that are in use. In general, select this defragmentation type when the instance or database must be available during defragmentation or when you defragment a small index.
Rebuild (SORT_IN_TEMP): Directs SQL Defrag Manager to defragment the tables and indexes offline by completely rebuilding the indexes. This defragmentation type requires sufficient free space on your server and the amount of log space used can be extensive in full recovery mode. On large, fragmented indexes, this defragmentation type is significantly faster than a reorganization. In general, select Rebuild when it is acceptable that the database is unavailable during the time that the indexes are rebuilt.
Rebuild - ONLINE (SQL 2005 Enterprise and later): Directs SQL Defrag Manager to defragment the tables and indexes online by completely rebuilding the indexes. This defragmentation type requires greater resources such as disk space, memory, and processing. Because of the additional operation time and resource consumption, rebuilding online is only recommended when availability is important.
The time required to defragment a database depends on the number of pages used by the database, the activity level on that database, the performance of the computer where the database resides, and the type of defragmentation you choose to perform.
Automation allows you to define a custom schedule for analyzing and potentially defragmenting databases during periods of low activity. You can also set SQL Defrag Manager to analyze databases on a regular schedule, such as daily or weekly. Because the database is unavailable while SQL Defrag Manager rebuilds one or more indexes in the database, you should run this defragmentation during periods when the database can be offline and unavailable. Click here to learn more about Automation.
2-Steps defragmentation
The Fragmentation Explorer tab allows you to easily defragment individual objects from the navigation tree, such as a specific instance, database, table, or index, as well as the entire SQL Defrag Manager environment. Right-click the object you want to defragment to open the contextual menu, click the Defragment all/entire... option and select the type of defragmentation you want to perform. Click here to learn more about actions you can perform from the contextual menu.
Canceling an activity
When an activity is running, SQL Defrag Manager displays a Cancel Activity option in the lower left-hand corner of the status bar. Click the option to stop the activity from running.
