Why is my database consuming so many resources?

Initial assessment

Symptoms

• When I monitor my database server resources, I see high CPU or memory usage, or both.

Impact or scope

• Does it affect one CLM application or multiple applications?
• Are you noticing a negative performance impact when CPU usage is high?

Timing (When?)

• When did the problem start to occur? (Has this always been the case, or is it a relatively new observation?)
• Does this happen at any particular time of the day?
• Do you have a separate data warehouse database and is the data warehouse on a separate database server?
• Are builds being run when you see the spikes.

Environmental changes (Where?)

• Has anything changed in the environment (outside the products) that could cause the problem?
• Has the hardware, software, network, or client changed?
• Are other applications using the same database server?
• Is other software running on the database server?

Recommended data gathering and subsequent analysis steps

• There are many commands the database administrator (DBA) can use to monitor the performance of your database. Keep in mind that excessive monitoring can lead to performance degradation of the database software. Limit monitoring initially to the amount of data (inbound and outbound), the number of queries over time, and some measure of the amount of time from the receipt of a SQL request to the time a response is generated.

Monitoring DB2

For more information on configuring and tuning the DB2 database, see Configuring and tuning DB2.

For more information on optimizing table space performance when data is on RAID devices for DB2 11.5, see Optimizing DB2 on RAID devices.

When there is a specific issues with a an ELM operation causing considerable stress on the database server, then we need an Explain plan of what is occurring. Creating explain tables is detailed in the Db2 documentation on Explain tables.

CONNECT TO database-name
db2 -tf EXPLAIN.DDL

This should accompany standard application logs from the same timeframe as the issue being reported.

Monitoring Oracle

For more information on monitoring and tuning the Oracle database, see Configuring and tuning Oracle. There are specific settings when upgrading which are neither necessary or desirable for production run-time. During the upgrade from DOORS Next 6.x to DOORS Next 7.x, it is necessary to tune Oracle with specific settings.

In terms of troubleshooting Oracle errors, then typically IBM Support will request an Explain plan and the output of the SQL Monitor. This is explained in Oracle Must Gather.

Monitoring SQL Server

For more information on monitoring and tuning the SQL Server database, see Configuring and tuning SQL Server.

Possible causes

• Make sure that your database has sufficient physical memory and enough allocated memory to perform a lot of data transfers. All of the REST-based objects need to be stored and retrieved here, and the rest of the solution architecture depends on the repository. Do not try to cut costs; make sure that the foundation for your solution is solid.

• Your database should be on a dedicated server. While it is technically possible to install the database software onto the same machine as other software, the load on the database is heavy because almost every user action implies multiple database events (like queries, inserts, or modifications). Deploy the database on a dedicated database server.

• If you are running reports and have not configured a data warehouse, consider creating a separate data warehouse and locating it on a separate database server.

• If you have been able to correlate reports running (ETLs) at the time of the spikes, consider moving your data warehouse to another database server.

• Builds have several phases. During the first phase, data is being requested from the database as the source files are returned and populated into the build workspace. During this phase, there is a large load of communications and database traffic. A caching proxy placed between the build machine and the Jazz repository will cache copies of the source files that do not change often, thus eliminating the need to retrieve these files from the repository. Consider using a content caching proxy server to minimize the load on the database. For more information on content caching proxy servers, see Using content caching proxies for Jazz source control.

• Your database administrator should be able to run an analysis on the database to help identify issues and provide steps to help optimize the database. These might include poorly formed queries, missing indexes, table locks, and even calls being made to the database outside of the application server. Use the above links to specific database vendor tools to help identify some of these issues.

Related topics: None

External links:

• None

Additional contributors: None