Query On Database Taking Up Resources

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

One or more of the following symptoms are experienced:
-Blank page after login where it browser trace shows indefinite pending status
-There is a report showing long-running (high elapsed time) database query 
-Job taking longer to complete, often with inconsistent runtimes.

Resolution

Have the DBA team provide the following reports. For each supported vendor, please provide the following for review:

Oracle
Provide a one-hour snapshot of the AWR (in HTML format) or statspack from the time the issue occurred.

SQL Server

Provide the following (Performance - TOP) reports.

It has to be ran during the time of performance as it is not a historic, but a real-time report.  

1. Go to Object Explorer > right-click SQL Server (top node) > Reports > Standard Reports > 

a. Performance - Top Queries by Average IO

b. Performance - Top Queries by Total CPU TIME

c. Performance - Top Queries by Total IO

d. Performance - Top Queries by Average CPU TIME

2. Export-to-excel by right-clicking anywhere in the report and choose export > excel
This way, it can be properly analyzed

PostgreSQL
Provide the badger report or the pg_stat_activity results