This article gives a brief overview of the SQL Server instance index defragmentation operations presented as activities within the SQL index software.
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
To see the activity information in full detail, click the “+” expand button next to its checkbox:
This action will show database, instance, and index names that were the target of this SQL Server instance index defragmentation activity:
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
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:
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:
Upon saving the file, a dialog message is initiated notifying that the report is successfully generated. Click the Yes button to open the file:
The generated HTML file of the SQL Server instance index defragmentation activities will be opened in the web browser:
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:
To delete a sub activity of the selected activity, expand it, make the desired selection and then repeat the above-described steps.