Submit a ticketCall us

AnnouncementsFace your biggest database issues head-on

Our new eCourse helps you navigate SQL Server performance blocks by teaching you how to recognize and deal with the three DBA Disruptors: Performance Hog, Blame Shifter, and Query Blocker. Register today to learn how to defend your environment and fend off menacing disruptions.

Register for your free eCourse.

Home > Success Center > Database Performance Analyzer (DPA) > DPA Training > Free SolarWinds Training Videos - DPA > How to navigate the DPA UI to diagnose database performance issues - Video

How to navigate the DPA UI to diagnose database performance issues - Video

Updated 9-28-2016

Overview

This video demonstrates how to quickly diagnose database performance issues using wait time analysis.

 

 

Environment

  • Database Performance Analyzer

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.

 

 

Last modified

Tags

Classifications

Public
/*]]>*/