Applications Systems

DPA crashes on large SQL server plan collection

When running index analysis on a SQL Server instance which has "big" plan XMLs can cause memory issues and DPA crashes. The meaning of "big" depends on the environment but in general if the plan is getting close to 1MB in size it can be a problem.

First published date

7/16/2020 9:32 PM

Last published date

12/9/2025 11:48 PM

Overview

An issue can happen when in the  running index analysis on a SQL Server instance with a large plan where DPA has a memory heap dump and crashes or shuts down. The main symptoms of this issue are DPA shutting down and having a heap memory dump when it is monitoring SQL server and the heap memory settings are scaled appropriate to the amount of server. 

See this article. 
https://support.solarwinds.com/SuccessCenter/s/article/Scale-DPA-for-number-of-monitored-databases?language=en_US

The normal locations for the logs in IDC.log may not show the cause of the crash for DPA. If this is the case get from the customer the most recent .HPROF heap memory dump file. 

Product section

Database Performance Analyzer

Cause

The Index Analysis items on very large SQL plans has some memory issues. 
 

Resolution

Workaround 1: Exclude the problematic SQL hashes with big plans from IA 
steps are here. Turn off DPA table tuning advice for a table

Note: one issue with this is that new SQL items that have not been observed could still cause the issue with memory. 

Workaround 2: 
Decrease PLANPOLL_SQL_SERVER_PLAN_CHARACTER_LIMIT (by default it's 1MB) to limit the length of plans saved in the repository. 
Steps: 

  1. Click on options
  2. advanced options 
  3. DB instance options tab 
  4. selecting the "support options" check box in the upper right hand corner
  5. Select the instance in the drop down
  6. search for  PLANPOLL_SQL_SERVER_PLAN_CHARACTER_LIMIT change this value from 100000 to 10000 or some other smaller value. 


Note: even plans which are less than this limit can case problems for small (1GB of heap memory and <10 instances) environments.

Workaround 3: Disable index analysis. 
Steps: 

  1. Click on 
  2. options 
  3. advanced options
  4. DB instance options tab
  5. Select the instance in the drop down
  6. search for INDEX_ANALYSIS_ENABLED and click that text 
  7. set this value to false. 
  8. Restart the DPA monitoring for the instance