SQL Server instance index defragmentation activities

Applies to

ApexSQL Defrag

Summary

This article gives a brief overview of the SQL Server instance index defragmentation operations presented as activities within the SQL index software.

Description

There are two ways of lowering SQL index fragmentation level percentage: by using index reorganization and index rebuild operations.

The mentioned defragmentation operations are presented as activities in the SQL index application and the mentioned activities history is stored in one single place, within the Activities tab. Here, the SQL index defragmentation activities are presented in a comprehensive visual display and can be further maintained.

Activities grid view

To see an index defragmentation operation, head over to the Activities tab. All details about an activity are displayed in the following grid columns:

  • Action – the name of the performed defragmentation operation
  • Initialization – manual or automatic via index policies
  • Instance – the name of the SQL Server instance the activity is performed against
  • Database – the name of the defragmented database
  • Table – the name of the defragmented table
  • Index – the name of the defragmented index
  • Started – the exact time index defragmentation activity has started
  • Ended – the exact time index defragmentation activity has ended
  • Duration – the duration time of the performed index defragmentation activity
  • Status – the outcome result of the performed activity
  • Message – a message displayed based on the activity hard fail outcome

The Activities tab in the SQL index tool

Quick tip icon

Quick tip:

To see the activity information in full detail, click the “+” expand button next to its checkbox:

Expand the activity

This action will show database, instance, and index names that were the target of this SQL Server instance index defragmentation activity:

Expanded SQL index defragmentation activity

Activity filters

The activities grid view can be refined to show the activities on the selection of the following filters:

  • Succeeded – shows only the activities that were completed successfully
  • Warning – displays the activities that were completed with warnings
  • Failed – shows only failed activities
  • Analyse – shows the analyse, fast or deep scan, activities
  • Reorganize – shows the performed index reorganize activities
  • Rebuild – displays only the offline rebuild index activities
  • Rebuild online – shows the activities of indexes that were rebuilt online
  • Policy initiated – shows only the activities that were initiated by the scheduled policies
  • Manually initiated – displays the activities that were manually initiated

SQL Server instance index defragmentation activity filters

Export activities

To keep track of the activity history, and to create a reporting archive, the SQL Server instance index defragmentation activities, within the SQL index software, can be exported in three different formats: CSV, XML, HTML.

To export all presented activities, or any specific one, to the HTML file format, make a selection from the main grid by ticking the desired activity(es) and click the Export drop-down menu and choose the HTML option:

Export  SQL index defragmentation activity history

This action will initiate the Save As window, where the name for the exported document along with its location are given. Choose to give a custom name and location or leave the default settings and click the Save button:

Save As dialog

Upon saving the file, a dialog message is initiated notifying that the report is successfully generated. Click the Yes button to open the file:

Open file dialog in the SQL index tool

The generated HTML file of the SQL Server instance index defragmentation activities will be opened in the web browser:

Generated SQL index defragmentation activity history

Quick tip icon

Quick tip:

To delete an activity, mark its checkbox and click the Delete button from the Data category. When asked to confirm the action in the emerged dialog window, click the Yes button:

Deleted SQL index defragmentation activity in the SQL index tool

To delete a sub activity of the selected activity, expand it, make the desired selection and then repeat the above-described steps.