Submit a ticketCall us

Have You Auto Renewed? If not, you're missing out.
The SolarWinds Renewal Program comes with a host of benefits including the most recent product updates, 24/7 technical support, virtual instructor-led training and more. Experience all of this with the convenience of Auto Renewal, and never worry about missing any of these great benefits. Learn More.

Home > Success Center > Network Performance Monitor (NPM) > Manual index defragmentation using Microsoft SQL Management Studio

Manual index defragmentation using Microsoft SQL Management Studio

Table of contents

Updated May 19, 2017

Overview

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

 

There are scenarios were 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.

Environment

All Orion Platform versions

Steps

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 (© 2017 Microsoft, available at https://docs.microsoft.com, obtained on May 16, 2017).

 

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.

     
  7. After rebuilding, right-click the index and click Properties > Fragmentation. The total fragmentation value is displayed.

 

Screenshots property of © 2017 Microsoft.

 

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third-party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.

 

 

Last modified

Tags

Classifications

Public