Network Management

Manual index defragmentation using Microsoft SQL Management Studio

This article shows an example of how to manually perform defragmentation on an index using SQL Management Studio. When auto-index defragmentation is enabled in Orion, databasemaintinence will sometimes fail to defragment highly fragmented indexes will get defragmented during the Orion database maintenance, which runs at 2:15 AM by default.

First published date

10/19/2018 10:07 PM

Last published date

11/26/2021 3:33 AM

Overview

There are scenarios where a highly fragmented index needs to be defragmented manually.

When this happens, an event will appear in Orion Events showing which index is highly fragmented, and it should be defragmented manually. See the following example event:

Index IX_InterfaceID on table InterfaceTraffic_Daily_20160928 has fragmentation 90.91% and should be defragmented manually.

Index IX_InterfaceID on table InterfaceErrors_Daily_20161005 has fragmentation 90.00% and should be defragmented manually.

 

These events can also be seen in the Orion database maintenance log swdebugmaintenance.log".

In this log, you may see errors such as ALTER INDEX REBUILD statement failed because the ONLINE option is not allowed when rebuilding a columnstore index. Rebuild the columnstore index without specifying the ONLINE option"

Product section

Orion Platform

Resolution

Before starting, ensure the automatic index defragmentation option is enabled in the Settings> All Settings > Orion Polling Settings > Database settings under "INDEX DEFRAGMENTATION" is checked and enabled. This could save the work of manually having to perform this. If this option was already checked, manual defragmentation may need to be performed.

Manual index defragmentation must be performed by a DBA on SQL Management Studio because this is a Microsoft SQL troubleshooting task. See the steps for rebuilding an index in Reorganize and Rebuild Indexes.

 

The following is an example for index IX_InterfaceID on table InterfaceTraffic_Daily_20160928 that has a fragmentation value of 90.91% and should be defragmented manually.

  1. Connect to the SQL server using Microsoft SQL Management Studio.
  2. Expand Databases and locate the Orion database.
  3. Expand the Orion database, and then expand Tables.
  4. Expand the table and locate the index mentioned in the event message.
  5. Right-click the index and select Rebuild. 
  6. Click OK to rebuild the selected index, then choose reorganize.
  7. After rebuilding and reorganizing, right-click the index and click Properties > Fragmentation. The total fragmentation value is displayed.