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
-Page or project slow to load content

Resolution

1. Generate an action trace with instructions here

2. Additionally, 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