Database Management

Limit the size of CONSPT_ tables on DPA monitoring SQL server

Often some of the largest tables that exist on a repository in DPA are the CONSPT_X tables, where _X is the ID of the monitored instance ID in COND.

First published date

10/22/2020 5:03 PM

Last published date

12/9/2025 11:49 PM

Overview

DPA stores plan detail data in a table in the repository called CONSPT_X, where the X is replaced with the ID for the monitored instance in the table COND.

These tables can often be the largest tables in the repository. This article will discuss some support options that can be used to limit the table size when monitoring a SQL server in DPA. 

Product section

Database Performance Analyzer

Cause

This is as designed. DPA will gather plans for any SQL statement that DPA sees as active. It can happen that on servers that are very active or servers that have large plans or plans that are changing often  ("plan churn"). these detail tables can be quite large.

DPA versions prior to 2022.2 also kept plans for up to five years. This was done to match the retention setting CLEAN_DAYS_OF_TREND and some of these plans were only visible if not in use in reports.

In DPA version 2022.2 and later, the option CLEAN_CONPT_RETENTION is set with the default of "Hourly", which means that plans are kept the same as DPA's hourly data retention of 90 days, not to "Daily" to match DPA's retention of day data that was the precious 5 years. This setting can also be set to "Detail" and plans would only be kept to match the detail retention in CLEAN_DAYS_OF_DETAIL. default of 30 days.

Any instance registered in a DPA release that was prior to 2022.2 does not change to this new default and so will continue to keep plans for 5 years. This often leads to growth of the CONSPH_X or CONPT_X tables. 

Resolution

The main item that impacts DPA repository size is the setting for CLEAN_DAYS_OF_DETAIL. This setting controls all data in DPA detail sections that is collected down to the second. The default for this is 30 days.

If you want to keep this settings to retain the history for this number of days but, with reduced plan collection and retention, consider changes to the following settings.

  1. Log in to DPA as an admin.
  2. Click Options.
  3. Click Advanced Options in the Administration section.
  4. Click the DB instance tab.
  5. Select the instance in the drop-down menu.
  6. Select Support Options.
  7. Press Ctrl+F in your browser or scan the options, which are listed alphabetically. Click on the text of an option to change the value. You can change any of the options below.

To limit the number of days plans are kept by DPA, adjust CLEAN_CONPT_RETENTION. Set this to "HOURLY" to keep the TREND_HOURLY_DAYS default of 90 days or "DETAIL" to keep same as CLEAN_DAYS_OF_DETAIL default of 30 days . 

To limit the number of plans polled by DPA, adjust PLANPOLL_SQL_SERVER_TOP_LIMIT (for example, change to 50 from the original 100 queries).

To limit the number of characters per plan. adjust PLANPOLL_SQL_SERVER_PLAN_CHARACTER_LIMIT (for example, change to 50,000 from the original 1,000,000 characters).

To minimize the amount of detailed/plan data, adjust TREND_HOURLY_DAYS (for example, change to 30 from the original 90 days).