Documentation forDatabase Performance Analyzer

Searches on the Find SQL tab do not return any data

If no results are returned when you search for a SQL statement, it could be for one of the following reasons:

Data summarization has not had time to run

The Find SQL functionality was recently enabled, and data summarization has not run yet. Data summarization indexes the SQL statements to make them available for searching.

Resolution: Wait for data summarization to run. For a newly registered database instance, data summarization runs after at least one hour of data is collected. For a database instance with historical data, the process runs in 10 minute intervals.

DPA was not restarted after the Find SQL index files were moved

The Find SQL index files were moved, but DPA was not restarted afterward.

Resolution: Restart DPA and wait for data summarization to run.

DPA cannot create or modify the index

If DPA cannot create or modify the index, the following error can be found in the error.log file:

ERROR (2020-09-15T01:58:54,059-0700) [findSqlProcess-thread-358] PeriodicLogger:190 - Error when getting index writer or index directory for text index and database ID 64.

Check for the following issues:

  • Check the amount of available space in the location where the Find SQL index files are stored. By default, the indexes are stored in the following location:

    DPA-install-dir/iwc/tomcat/ignite_config/lucene-index

    If you moved the files to a custom location, the location is specified by the com.solarwinds.dpa.findSql.index.location property in the following file:

    DPA-install-dir/iwc/tomcat/ignite_config/idc/system.properties

    Resolution: Increase the disk size or move the Find SQL indexes to a different location.

  • Verify that the DPA service user has read and write permissions for the folder and files that store the Find SQL indexes. The DPA service user is LOCAL_SERVICE on Windows, and the user that runs DPA on a Linux-based OS.

    Resolution: Grant read and write permissions to the DPA service user.

  • If there is enough space and permissions are correctly set, the index might be corrupted.

    Resolution: Rebuild the index:

    1. Stop DPA.

    2. If only one database instance is identified in the error message, delete the Find SQL index folder for that database instance. The default location is:

      DPA-install-dir/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-install-dir/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.

      If you moved the index files for all database instances to a custom location, the location is specified by the com.solarwinds.dpa.findSql.index.location property in the following file:

      DPA-install-dir/iwc/tomcat/ignite_config/idc/system.properties

      If you moved the index files for only some database instances to a custom location, the location is specified by the com.solarwinds.dpa.findSql.index.location.db_id property in that file.

    3. If you receive error messages for all database instances, delete this folder to remove all Find SQL indexes:

      DPA-install-dir/iwc/tomcat/ignite_config/lucene-index/RepoID

    4. Restart DPA.

The search criteria violates the Find SQL search rules

If you enter search terms that you believe should return data, but the Find SQL search does not show any results for that search, verify that your search terms follow the Find SQL search rules. For example, if you try to use wildcards in Simple mode (sel*) or you enter a partial term without a wildcard in either mode (sel), the search does not return any results.