Page Compression in repository database

You might also consult a companion article discussing row vs. page compression written by Thomas LaRock and whether/how to configure them.

However, I do not recommend altering the DPA repository schema unless being advised to do so by SolarWinds DPA Support. While no schema is perfect, the DPA Schema choices are tradeoffs between space, time, and other application factors. Collecting query information every second from multiple monitored DB instances and rapidly storing that information is a primary concern of DPA software and its schema design. The computational overhead of applying additional compression algorithms (while probably slight) hasn’t been tested under different loads and resources and the wrong combination of data and a compression algorithm can sometimes result in more storage space overhead. Further, the operational and DPA upgrade/maintenance software of each version assumes a specific schema and may unexpectedly override your changes or run into subtle/difficult to resolve problems (now or seemingly unrelated ones in the future). For all these reasons and to maintain supportability, I’d only update the DPA if advised to so by SolarWinds Technical Support. 

However there are recommended ways to reduce the storage overhead of DPA.

(1) Setting Proper Expectations

First off, make sure your expectations of storage requirements for the DPA Repository match those of the DPA guidelines found in the System Requirements for your DPA Release (Current and Previous DPA Version Documentation). 

The amount of repository storage is related to the number of monitored DB instances AND the query activity level of those instances. The DPA 2021.1 release  system requirements documentation (look for the section “Self-managed repository database server requirements”) recommends either 1GB, 3GB, or 5GB to monitor EACH DB instance depending on whether it is a low, medium, or high query activity instance.  

These sizings are rules of thumb for planning purposes. For example, monitored database instances with a very high query load may require additional storage space.

(2) Understand/adjust DPA Data Retention Policy

Understand DPA’s data retention policy and which of the 6 configuration options to adjust. About 90% of storage usage occurs within the first 30 days of collection per instance. Reducing the CLEAN_DAYS_OF_DETAIL configuration option is the biggest lever to pull to reduce your repository size. 

However, reducing detail data retention comes at a cost because several analytic features are dependent on detail data and aren’t available for time intervals beyond the detail retention limit. 

The adjusted retention options won’t take affect until after the DPA cleaner has run overnight (i.e., your detail data will removed by tomorrow). Make sure DPA stays up and active overnight and can connect to its repository.

(3) Reduce VM Option Storage of VMWare information

If DPA is also monitoring VMWare metrics for Database Instances on VMs having a large number of disks, consult “Reduce the size of the CONV_MERIC_DETAILS tables in the DPA repository” to reduce this feature’s repository storage requirements.

(4) Find SQL Disk Feature's Space Requirements

The Find SQL feature (Available in release 2021.1 and later) for searching for specific queries by SQL text content and multiple dimensions does not stores its query indexes inside the DPA repository. However, you should account for its disk storage overhead per monitored instance in your planning. You can always move the Find SQL location to different locations that have more available disk space or disable this feature entirely. Consult the DPA System Requirements documentation for size estimations and more details. Consult your version's DPA Administrator Guide for information on how to problem solve using the Find SQL feature.

You might also consult a companion article discussing row vs. page compression written by Thomas LaRock and whether/how to configure them.

However, I do not recommend altering the DPA repository schema unless being advised to do so by SolarWinds DPA Support. While no schema is perfect, the DPA Schema choices are tradeoffs between space, time, and other application factors. Collecting query information every second from multiple monitored DB instances and rapidly storing that information is a primary concern of DPA software and its schema design. The computational overhead of applying additional compression algorithms (while probably slight) hasn’t been tested under different loads and resources and the wrong combination of data and a compression algorithm can sometimes result in more storage space overhead. Further, the operational and DPA upgrade/maintenance software of each version assumes a specific schema and may unexpectedly override your changes or run into subtle/difficult to resolve problems (now or seemingly unrelated ones in the future). For all these reasons and to maintain supportability, I’d only update the DPA if advised to so by SolarWinds Technical Support. 

However there are recommended ways to reduce the storage overhead of DPA.

(1) Setting Proper Expectations

First off, make sure your expectations of storage requirements for the DPA Repository match those of the DPA guidelines found in the System Requirements for your DPA Release (Current and Previous DPA Version Documentation). 

The amount of repository storage is related to the number of monitored DB instances AND the query activity level of those instances. The DPA 2021.1 release  system requirements documentation (look for the section “Self-managed repository database server requirements”) recommends either 1GB, 3GB, or 5GB to monitor EACH DB instance depending on whether it is a low, medium, or high query activity instance.  

These sizings are rules of thumb for planning purposes. For example, monitored database instances with a very high query load may require additional storage space.

(2) Understand/adjust DPA Data Retention Policy

Understand DPA’s data retention policy and which of the 6 configuration options to adjust. About 90% of storage usage occurs within the first 30 days of collection per instance. Reducing the CLEAN_DAYS_OF_DETAIL configuration option is the biggest lever to pull to reduce your repository size. 

However, reducing detail data retention comes at a cost because several analytic features are dependent on detail data and aren’t available for time intervals beyond the detail retention limit. 

The adjusted retention options won’t take affect until after the DPA cleaner has run overnight (i.e., your detail data will removed by tomorrow). Make sure DPA stays up and active overnight and can connect to its repository.

(3) Reduce VM Option Storage of VMWare information

If DPA is also monitoring VMWare metrics for Database Instances on VMs having a large number of disks, consult “Reduce the size of the CONV_MERIC_DETAILS tables in the DPA repository” to reduce this feature’s repository storage requirements.

(4) Find SQL Disk Feature's Space Requirements

The Find SQL feature (Available in release 2021.1 and later) for searching for specific queries by SQL text content and multiple dimensions does not stores its query indexes inside the DPA repository. However, you should account for its disk storage overhead per monitored instance in your planning. You can always move the Find SQL location to different locations that have more available disk space or disable this feature entirely. Consult the DPA System Requirements documentation for size estimations and more details. Consult your version's DPA Administrator Guide for information on how to problem solve using the Find SQL feature.