Best practices and troubleshooting for the SolarWinds Platform database

This topic applies to all SolarWinds Platform products.

As your SQL database matures, or after adding SolarWinds Platform products, your database may become larger than you originally estimated or might slow unexpectedly. Most common database issues are related to storage capacity and database performance. These best practices and troubleshooting tips provide preventive steps to take to ensure your database stability and performance.

Adjust how long you want to keep historical data

Consider how long you need to archive monitored data. You can reduce the amount of data in the database by shortening retention periods.

Log in to the SolarWinds Platform Web Console as an administrator.
Click Settings > All Settings in the menu bar.
In the Thresholds & Polling section, click Polling Settings, and scroll down to Database Settings.
Adjust the retention settings for containers, interfaces, wireless, or UnDP.
When adjusting a data retention period, make small changes, and examine the impact on size and performance.
Click Submit to apply the changes.

How does altering data retention affect database size?

In the SolarWinds Platform database, detailed data is summarized into hourly data increments and then into daily data increments.

The shorter the data interval, the greater the effect the setting will have on the database size.

Extending the detailed data retention will have the largest potential impact on database size and performance.
Extending hourly retention will have a lesser effect.
Extending daily retention will have the least effect.

Design a database maintenance plan

The database maintenance should include:

A tested backup and restore plan. Use the Microsoft SQL Studio to back up and restore your database.
Database maintenance within your Orion. Specify an off-peak hour to run the maintenance. See Database maintenance in the SolarWinds Platform.
An analysis of white space within the database files. This is analogous to data fragmentation.
A general data integrity check.
A re-indexing routine
Detection of index fragmentation. This causes index searches to slow or fail.

Prevent fragmentation problems

Do not use auto grow or auto shrink when possible. Auto grow and auto shrink can result unnecessary database tasks and index fragmentation.
Do not manually shrink your database to recover disk space. If you shrink a database with insufficient space to update index files, the index may become fragmented and incomplete so that indexed searching is not possible. SolarWinds Platform Web Console will time out constantly.
Include re-indexing in your maintenance routine.

Troubleshooting

Bad performance

In the SolarWinds Platform database, the most important SQL server performance measurement is disk queue length.

Queue length is a measurement of the SQL writes that are waiting to be written to disk. When disk queues start lengthening and there is a steady load on the SQL writes, the queues may grow so large that write requests get dropped. This may lead to gaps in data and will affect the overall performance of the SQL server.

The disk queue length should not exceed two times the number of effective spindles in the SQL storage. The effective spindle count is the number of striped spindles.
For a RAID 10 direct attached storage unit with eight total disks, the effective spindle count is four. Four of the spindles in this array are the primary striped array and the other four are a secondary striped mirror of the four primary spindles. Since no performance gain is achieved by mirroring disks, only the primary striped set is used to measure performance.

Lost connection to the database

Ping the SQL server from the SolarWinds Platform server to check network connectivity.
Open SQL Server Management Studio or the SolarWinds Platform database Manager, and attempt to connect to the database.
If both of the above are successful, start the Configuration wizard in the SolarWinds Orion program folder, and complete the wizard. Make sure you are using the proper database credentials.
Open the SolarWinds Platform Web Console to test connectivity again.
Test opening an ODBC connection from the SolarWinds Platform server using a Microsoft utility, such as ODBCPing.