Database maintenance and optimization tips

To ensure optimal performance and prevent severe system issues, such as Out of Memory (OOM) errors, systematically manage and maintain saved query tasks within the Dynamic Workload Console.

About this task

An excessive accumulation of obsolete or unused query tasks in the database can lead to significant performance degradation, extended login times, and potential system crashes.

Establish a regular maintenance schedule to identify and remove query tasks that are associated with inactive users and temporary tasks that are no longer required. You can remove all unused query tasks associated with an inactive user and prevent memory degradation, run the following SQL statement directly against the database with the following command:
DELETE FROM <schema>.TDWC_QUERYTASK WHERE USERNAME = '<inactive_user_id>';
where:
<schema>
is the database schema name
<inactive_user_id>
is the specific user identifier.

You can also reorganize the database to reclaim fragmented space and restore peak performance.

Deleting large volumes of query tasks generates fragmented, unused space within the database. Starting with Dynamic Workload Console 10.2.7, database-specific reorganization scripts for all supported databases are included directly within the installation. Use these provided scripts to maintain your database health.

For more information, see the specific topic for your database: