Page History

...

You should regularly monitor fragmentation levels on indexes and schedule SQL Defrag Manager to analyze database fragmentation. Use the scheduled analysis to identify specific fragmentation levels before defining your policies to automatically reorganize or rebuild indexes.

Access the Fragmentation Explorer

...

You can mouse over any icon to display a tip that identifies what that icon represents. The fragmentation statistics include:

Average FragmentationImage Added Average Fragmentation 

Displays the total fragmentation of all indexes on the associated object and the objects contained divided by the total number of indexes on those objects. This calculation is weighted based on the size of each index. For example, if you have three similar-sized indexes with 10%, 10%, and 70% fragmentation levels respectively, this value is 90% divided by three, which is 30%. The logical fragmentation level for each index is the percentage of out-of-order pages in that index when the index was last analyzed.

The Average Fragmentation value can help identify critical fragmentation levels as you drill-down to details on individual tables and indexes. You can compare logical fragmentation levels across indexes to identify hot spots. You may see performance gains after defragmenting indexes with as little as 10% fragmentation. In general, consider defragmenting indexes with 20% or more fragmentation.

Pages ReadImage Added Pages Read 

Displays the total number of pages used by the associated object and the objects it contains. For example, when you view this value for a database, it displays the total number of pages used by the database, including all pages used by all the tables and indexes in that database.

Fragmentation impacts disk I/O. Since larger indexes are less likely to be cached by SQL Server, fragmentation impacts the performance of large indexes more than small indexes. In general, focus on indexes with 1,000 pages or more.

Pages Image Added Pages Per Fragments

Displays the average number of pages per fragment. This value is the total number of pages identified by the Pages Read value divided by the number of those pages that are not in logical order.

Page Image Added Page Density

Displays how much data is stored in the pages, identified by the Pages Read value, compared to the maximum amount of data that can be stored in those pages. Only a thorough analysis collects or updates this information. If no page density information has been collected for these pages, this value is set to N/A (not available).

Low page density can cause SQL Server to read more pages than necessary when using an index, such as when performing a query. When data is initially stored on the pages, SQL Server uses the fillfactor fill factor value to determine how much data to store on each page. At this point, page density is relatively high, but the page density becomes lower as data is inserted and pages are split to make room for the additional data. SQL Defrag Manager can reorganize or rebuild indexes to increase page density and reduce the number of reads when using the index.

IndexesImage Added Indexes

Displays the total number of indexes on the associated object and the objects it contains.

...

The Show/Hide filter options allow you to refine which objects SQL Defrag Manager displays on the Fragmentation Explorer tab. When you clear the check box for a group of objects, such as the Excluded check box, SQL Defrag Manager does not display those objects and updates the displayed fragmentation statistics, such as Average Fragmentation and Relative Fragmentation to not include those objects.

The Criteria option defines which objects are represented by the Excluded group. If you clear the Excluded check box, SQL Defrag Manager does not display objects that match the specified criteria.

Anchor
contextualfilter contextualfilter

Use the

...

contextual menu filter option

Filters You can be applied apply filters to either the complete SQL Defrag Manager environment (all registered instances), an instance, a database, or a table. Right From the navigation tree, right-click the item object you want to filter from the tree pane and select Filter to open the dialog window.

Use the text field in the dialog to enter the exact name of the instance, database, or table you are searching. You can also use '%' as a wildcard to filter the results by keywords. Separate filtering terms with Use a comma to apply separate multiple filtersfiltering criteria.

Click Find Click Find to apply the filter. SQL Defrag Manager displays the results in the Fragmentation Explorer.

To remove the applied filters, right-click the instance, database, or table you applied the filters to and choose Clear filter, or click the Clear Filters icon Image Added above the navigation tree.

Anchor
rediscoverobjects rediscoverobjects

Rediscover Objects

Use this feature to update the view of objects found on a specific server, a database, or a table. Right-click the specific server, database, or table and select the Rediscover Objects option.

Performing actions on individual tables or indexes

In addition to analyzing and defragmenting objects from the database or instance level, this window allows you to analyze and defragment individual objects, such as specific tables and indexes. When you right-click an object in the navigation tree, the context menu provides several options.

...

Anchor
navigationtree navigationtree

Performing actions from the Explorer navigation tree

You can perform several actions to on either the entire SQL Defrag Manager environment, to a specific instance, database, or table from the navigation tree pane. Right click the target for the action -click an object to open the contextual menu options. The actions available depend on the object selected.

Actions on the entire SQL Defrag Manager environment

• • Analyze all registered SQL Servers: Allows you to select the type of analysis - Fast, Thorough-Sampled, or Thorough-Detailed
  • Defragment all registered SQL Servers: Allows you to select the type of defragmentation - Reorganize, Rebuild,
and
• • or Rebuild (online)
  • Add another SQL Server: Opens the Register SQL Servers configuration window
  • Filter:
By
• •  Allows you to filter the objects view by name or keywords

Actions on a specific instance

• • Analyze entire server:
 Fast
• •  Allows you to select the type of analysis - Fast, Thorough-Sampled, or Thorough-Detailed
  • Defragment entire server:
 Reorganize
• •  Allows you to select the type of defragmentation - Reorganize, Rebuild,
and
• • or Rebuild (online)
  • Rediscover server objects: Updates the view of objects found on the server
  • Edit configuration for this server: Allows you to modify authentication settings
  • Remove this server: Allows you to remove the instance from SQL Defrag Manager
  • Add another server: Allows you to register an instance
  • Filter: Allows you to filter the objects view by name or keywords

Actions on a specific database

• • Analyze entire database: Allows you to select the type of analysis - Fast, Thorough-Sampled, or Thorough-Detailed
  • Defragment entire database: Allows you to select the type of defragmentation - Reorganize, Rebuild,
and
• • or Rebuild (online)
  • Rediscover database objects: Updates the view of objects found on the database
  • Filter: Allows you to filter the objects view by name or keywords

Actions on a specific table

...

• • Analyze entire table: Allows you to select the type of analysis - Fast, Thorough-Sampled, or Thorough-Detailed
  • Defragment entire table: Allows you to select the type of defragmentation - Reorganize, Rebuild,
and
• • or Rebuild (online)
  • Rediscover table objects: Updates the view of objects found on the table
  • Filter: Allows you to filter the objects view by name or keywords

Performing actions from the objects view

1. In the navigation tree, click the database that contains the table you want to analyze or defragment.
2. In the right pane, click the Analyze or Defragment option in the panel for the table you want to analyze or defragment.
   Image RemovedImage Added
3. Select the analysis or defragmentation type you want to perform.

...

When an activity is running, SQL Defrag Manager displays displays a Cancel Activity in  option in the lower left-hand corner of the status bar. To cancel an activity, click Cancel Activity while the activity runs.

Need more help? Search the IDERA Customer Support Portal

...

Click the option to stop the activity from running.

Image Added

...