Enable Automatic Index Defragmentation in the SolarWinds Platform

10/27/2018 3:03 AM

3/5/2025 12:35 AM

To defragment the SQL server hosting your SolarWinds Platform Database, enable automatic index defragmentation.

• In SolarWinds Platform 2017.3 and later, the automatic index defragmentation is enabled by default, and you can verify and change the setting from the Orion Web Console.
• In earlier SolarWinds Platform versions, use SQL queries.

The Automatic Index Defragmentation runs at 2:15 AM server time (default setting), together with the SolarWinds Database Maintenance.
 

Orion Platform

SolarWinds Platform 2017.3 and later 

1. Log in to the SolarWinds Platform Web Console using an administrator account.
2. Click Settings > All Settings > Polling Settings.
3. Scroll down to Database Settings, and select Index Defragmentation.

   

   The index defragmentation now runs regularly with the specified timeout at the set time.

SolarWinds Platform 2017.1 and earlier 

Note: Make sure that the account you're using has enough SQL Admin Privileges to make changes in your SolarWinds Platform Database.

1. Log in to the server hosting your SolarWinds Platform installation.
2. Run the following query on the SolarWinds Platform Database

   -- Scripts are not supported under any SolarWinds support program or service.
   -- Scripts are provided AS IS without warranty of any kind. SolarWinds further
   -- disclaims all warranties including, without limitation, any implied warranties
   -- of merchantability or of fitness for a particular purpose. The risk arising
   -- out of the use or performance of the scripts and documentation stays with you.
   -- In no event shall SolarWinds or anyone else involved in the creation,
   -- production, or delivery of the scripts be liable for any damages whatsoever
   -- (including, without limitation, damages for loss of business profits, business
   -- interruption, loss of business information, or other pecuniary loss) arising
   -- out of the use of or inability to use the scripts or documentation.
   
   UPDATE dbo.Settings SET CurrentValue = 1 WHERE SettingID LIKE 'dbm-defragmentation-enabled%'

   To run the query, use the Database Manager.

   1. Use Database Manager to view the SolarWinds database
   2. Right-click your SolarWinds Platform Database, and select New query.
   3. Paste the query and select Execute Query to ENABLE Automatic Index Defragmentation.

   The index defragmentation now runs regularly at the set time.

Notes:

• If this is the first time you are turning on Index Defragmentation, SolarWinds recommends you increase the timeout for Index Fragmentation. Default is 3600s (1hr), Maximum is 50000s (~14hrs).
• You can reset it to default after you have completed the Database Maintenance (with Index Defragmentation turned on).

Increase the timeout for Index Fragmentation to 50,000 s 

Run the following query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

UPDATE dbo.Settings SET CurrentValue = 50000 WHERE SettingID LIKE 'dbm-defragmentation-timeout%'

 

Set the timeout for Index Fragmentation to default 

Run the following query:

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

UPDATE dbo.Settings SET CurrentValue = 3600 WHERE SettingID LIKE 'dbm-defragmentation-timeout%'

 

 Check that Automatic Defragmentation is enabled 

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT * FROM dbo.Settings WHERE SettingID LIKE 'dbm-defragmentation-enabled%'

Run the following query against the [dbo].[IndexDefragmentationHistory] table, and verify if the CurrentValue column has a value of 1 (1 = Enabled / 0 = Disabled):

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.

SELECT * FROM dbo.Settings WHERE SettingID LIKE 'dbm-defragmentation-enabled%'

Related Article: Disable Automatic Index Defragmentation