Page tree

The SQL Defrag Manager Analyze Fragmentation window allows you to investigate the fragmentation levels of tables and indexes in the registered databases and instances. 

Access the Analyze Fragmentation window

To open the Analyze Fragmentation window, click Analyze on the Fragmentation Explorer tab of the SQL Defrag Manager Console.

Selecting the instances and databases to analyze

The SQL Servers and Databases field lists all the registered instances and databases. When you select an instance, SQL Defrag Manager selects all the databases in that instance. You can manually select specific databases to analyze tables and indexes in those individual databases.

If the instance or database you want to analyze 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 fast and thorough analysis

Fast analysis provides base-level analysis information, including statistics about logical order fragmentation. Logical order fragmentation occurs when the physical order of pages does not match the logical order of the pages. A fast analysis runs five to eight times quicker than a thorough analysis because SQL Defrag Manager does not scan the leaf pages of an index. Therefore, SQL Defrag Manager does not gather page density information during a fast analysis. You can perform a fast analysis on a database that is in use because it consumes less time and resources than a thorough analysis.

Thorough analysis provides comprehensive analysis information, including page density statistics. This analysis type requires more time and resources than a fast analysis and may block active processes while it collects analysis information. Therefore, you should run a thorough analysis only during times when the databases are not needed.

The time required to perform an analysis depends on the number of pages used by the databases, the activity level on those databases, the performance of the computer where the database resides, and the type of analysis you choose to perform. In general, an analysis can require from one minute to several hours.

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. Since an analysis can use system resources needed for other common database processes, you may want to schedule this analysis during periods of low database activity or demand.

2-Steps analysis

The Fragmentation Explorer tab allows you to easily analyze 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 analyze to open the contextual menu, click the Analyze all/entire... option and select the type of analysis 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.


Need more help? Search the IDERA Customer Support Portal