Move the Find SQL indexes

DPA indexes the SQL statements that run on each monitored database instance. The Find SQL feature uses this indexed data. By default, the indexes are created within the DPA directory.

Indexed data is retained for 30 days. In a large or busy DPA deployment, the Find SQL indexes can grow significantly, and in some cases large indexes can affect DPA performance. If the Find SQL indexes are in the default location and the storage requirement reaches 5 GB, DPA displays a message. If you receive this message, you have the following options:

• If the DPA server has enough storage capacity and DPA is not experiencing performance problems, you can increase the recommended storage limit. To do this, change the value of the advanced configuration option FIND_SQL_INDEX_SIZE_LIMIT.

• Move the indexes out of the DPA directory to a location with enough storage capacity for your indexed data.

To move the indexes to a custom location, complete the following steps:

1. Stop DPA.
2. Create a folder for the indexes in the new location, and set the privileges to allow the DPA service to read and write to the folder.
3. Configure DPA to write index data to this folder:

   1. Open the system.properties file in a text editor. This file is located in the following directory:

      <DPA-home>\iwc\tomcat\ignite_config\idc

      In Windows, the default location is:

      C:\Program Files\SolarWinds\DPA\iwc\tomcat\ignite_config\idc

      In Linux, the default location is:

      /home/solarwinds/dpa_V_v/DPA/iwc/tomcat/ignite_config/idc

   2. Add one of the following lines:

      • If you are going to move all indexes to the custom location, enter:

        com.solarwinds.dpa.findSql.index.location=<your_custom_location>

      • If you are going to move only indexes for specific database instances to the custom location, enter a line for each database instance, where db_id is the database ID:

        com.solarwinds.dpa.findSql.index.location.<db_id>=<your_custom_location>

        For example:

        com.solarwinds.dpa.findSql.index.location.5=/home/dpa/custom_location

4. Move indexes from the default location to your custom location. The default location is:

   <DPA_home>/iwc/tomcat/ignite_config/lucene-index/<RepoID>/<db_id>

   where:

   • <RepoID> is the DPA repository ID.

     If you have more than one folder in this path (for example, because you changed the database used as the repository), open the <DPA_home>/iwc/tomcat/ignite_config/idc/repo.properties file. The value of the repo.guid.hash property is the currently used repository ID.

   • <db_id> is the database ID.

   An example of the command to move all indexes is:

   mv <DPA_home>/iwc/tomcat/ignite_config/lucene-index/ /home/dpa/custom_location

   An example of the command to move an index for a specific database instance is:

   mv <DPA_home>/iwc/tomcat/ignite_config/lucene-index/<RepoID>/5 /home/dpa/custom_location/<RepoID>/

5. Start DPA.

If you selected the option in the installer to prevent Microsoft Defender from scanning the default Find SQL index folder, or if you manually excluded that folder after installation, you must add an exclusion for the custom location.

Troubleshooting tip

If you followed the instructions to move the Find SQL indexes but DPA still displays a yellow banner with the message about the index size, verify that the indexes have been moved to the custom location and that the system.properties file has the correct location. Then check the default index location in the DPA directory and verify that the indexes were removed. It is possible that the indexes were copied instead of being moved. If the custom location is configured and the indexes were copied there, remove the index folders from the default location.

For example, if index for the database instance with the ID of 5 is configured in system.properties, remove the following folder:

<DPA_home>/iwc/tomcat/ignite_config/lucene-index/<RepoID>/5

If all indexes are configured in system.properties, remove the following folder:

<DPA_home>/iwc/tomcat/ignite_config/lucene-index