Applications Systems
How to navigate the DPA UI to diagnose database performance issues - Video
This video demonstrates how to quickly diagnose database performance issues using wait time analysis. Learn more about Database Performance Analyzer (DPA).
First published date
Last published date
Overview
This video demonstrates how to quickly diagnose database performance issues using wait time analysis.
Product section
Resolution
Related Resources
- DPA getting started guide
Video Transcription
Solarwinds® Database Performance Analyzer (DPA, for short) is changing the way you monitor, analyze, and resolve performance issues. By using wait-time analysis (that is, how much time you are waiting on the database to respond), DPA reveals the root cause of performance issues in generally four clicks or less.
From the main dashboard, you get a high-level overview of database performance. Starting from the top left, you see the top five instances with the highest wait times. The top right has a line chart depicting wait-time trends where you can toggle between trending up and trending down.
Next are the Real-Time Monitoring alerts, displaying critical situations across the environment, including SQL statement, database, and server performance. You can hover over any alert, and click on the Question Mark to get a basic description of each alert metric.
Lower on the page, you can access any registered instance in your environment to drill into performance data. You can see which are VMs, cloud deployments, on-premises, Oracle® pluggable databases, and more.
To the right, the Actions pull-down menu lets you instantly navigate to a specific functionality inside DPA, such as reports, alerts, and log views.
You can also see system metrics at a glance by instance.
Now that you’ve seen the dashboard, let's click on an instance and dig in!
The Top SQL Statements view shows queries causing the most user wait time (try looking for the consistent big bars), or queries causing more wait time than usual. (That's the abnormally big bar in the chart.) From here you can:
- Hover over the bars to see SQL details
- Click the legend to name the SQLs that are the most important issues affecting performance
- View Historical Charts for SQL to see detailed history for a SQL Statement
- Or even drill into a time interval for deeper analysis
From this view you can also:
- Annotate an event
- Email this information to a team member
- Analyze a query
- Go back in time and run analysis
- And see much more
But right now, your end-users are screaming about slow application performance, so let's click into the time frame where they are experiencing significant waits.
The day view provides an hourly view of the top SQL statements. You can select a specific time slice by drilling into an hour, or use the Interval Drop Down menu to change the time interval.
Let's click into the hour with the most amount of waits to see the top SQL statements by wait time.
Hover over a URL to view the entire statement.
To determine root cause, review the type of wait the query is primarily waiting on, and get recommendations on how to optimize performance. Remember, you may need to tune the SQL statement, add an index, fix the application code, or address resource contention, such as storage, memory, or network.
Next, you can click on the Wait tab to see which resources are being used or waited on the most. Click on the legend to get more information on how to resolve or reduce the wait time. In this case, CPU is the resource taking up the most time. However, this doesn’t necessarily mean that there is a problem with your CPU configuration. It could indicate that an inefficient query is doing a full table scan, or reading too much memory.
If you click on the time slice, you can access all the resource metrics and correlate them to database performance. During the 7:30 time slice, you can see that there was a spike in CPU utilization that caused a slowdown for several SQL statements.
The tabs at the top reveal different areas of context in the time slice, such as: Databases, Programs, Users, Drives, Blockers, and Execution Plans.
Database Performance Analyzer:
- Finds database performance issues in a few clicks
- Correlates query performance to physical and virtual resources
- Delivers expert query tuning advice
- Reveals missing indexes, and
- Manages small and large database environments for SQL Server®, Oracle, MySQL®, DB2®, and ASE
DPA does the heavy lifting so you don’t have to!
To learn more about how to use Database Performance Analyzer, visit the Solarwinds Success Center.