Using SolarWinds Database Performance Analyzer to Diagnose Performance Issues


By:   |   Updated: 2020-05-11   |   Comments   |   Related: More > Performance Tuning


Problem

As long as relational databases have been around, query performance has always been one of the top issues that every DBA has to spend some amount of time troubleshooting. As much as we would like to make sure the performance of a query is optimal before it goes into production, there are many factors that come into play that can prevent this from happening. Inadequate test environments, changing data in production (data volume or the data distribution/cardinality), lack of testing and even other software running on the server are just a few factors that can lead to poor query performance.

No one likes getting that call that the application is down or is responding poorly and they suspect it is the database that is causing the issue. It would be great that when you do get that call, you could perform a quick check and see if there are issues on any of your SQL Server databases that could be causing this poor performance.

Also, while it's never fun dealing with an ongoing performance crisis, at least with a real-time performance issue it gives you an easy place to start troubleshooting. But what happens if a user notifies you that reports were running slow or they experienced poor application yesterday or even last week? Would you be able to look into if it was at all caused by a database system or query issue?

Having a tool that provides a quick diagnosis of these types of issues is something every SQL Server database professional should have in their toolbox. Could you suggest a solution?

Solution

Having reliable, predictable system performance is the goal of every IT professional. This is even more true for a SQL Server database professional given that the data is the backbone of almost every organization. While you could build your own set of scripts using views, events, auditing, etc. to perform these tasks. Building, and more importantly maintaining, these scripts can require a lot of time. Having a tool that can pinpoint any anomalies with respect to performance is something every database professional would welcome. One product that can do this is the SolarWinds Database Performance Analyzer for SQL Server (DPA). This software is capable of monitoring multiple platforms: Oracle, MySQL, DB2, SAP ASE, Cloud and of course SQL Server. In order to demonstrate some of the product features let's walk through a few typical issues that pretty much every database professional has seen at one time or another and show how this solution can be used to pinpoint and solve these issues.

In each of the following examples we will assume that all we know is someone told us the system was slow at some point in time and they suspect it is/was the database that caused the issue.

  1. SQL Server Blocking
  2. High SQL Server CPU Usage
  3. SQL Server Performance Anomalies

Before we dive into the examples, let's explain at a high level how SolarWinds Database Performance Analyzer for SQL Server (DPA) works. Most of the performance data collected by the tool is based on wait statistics. Once per second, DPA connects to the database and gathers a small subset of metadata related to each of the active queries. Collecting only a small subset of metadata means there is very little overhead for these once per second calls. At a much lower frequency the detailed wait statistics and other performance data is collected and backfilled to provide a complete picture of what was happening on your database instance. Also, important to note is that the tool does all of this without any software having to be installed on your production database server. It instead makes a JDBC connection from a separate server to collect the data and stores the data on a database installed on this server. Performance data is kept for 30 days which gives you the ability to look for any deviations from your baseline performance statistics by comparing what is currently happening in your database with what was going on at any other given time in the last 30 days.

Issue 1 - Finding and Fixing SQL Server Blocking

A user has informed you that they had a simple, yet important report that ran for over 5 minutes a few days ago on April 30. This report usually completes in under 10 seconds. They ask you to take a look to see if there are any issues within the database that could be causing this report to run slowly.

Let's start by opening the web-based UI for the SolarWinds Database Performance Analyzer for SQL Server. Once logged in, you are presented with the home page which provides a high level summary of your monitored database instances. In this example, we have the servers organized by platform with high level metrics per instance related to Waits, Tuning, CPU, Memory, Disk and Sessions.

SolarWinds Database Performance Analyzer Main Dashboard

After clicking the hyperlink for the server, we need to click the "Trends" tab to see what happened on our SQL Server instance for the date in question. This interface gives us an overview Top SQL Statements per day with hyperlinks below the graph to drill into the day or the SQL Statement ID's to the right of the graph. For this issue though we are going to click on the date "Apr 30" to view the data by the hour. Also, by looking at the second chart, Anomaly Detection, we can see an issue occurred on this date as well (Normal Range - Green, Warning - Orange and Critical - Red).

SolarWinds Database Performance Analyzer Top SQL Statements

Here we can see the data per hour for both the Top SQL Statements and Anomaly Detection. When we hover the mouse over the tallest bar at 2:00 PM we can see the code and get statistics on SQL Statement.

SolarWinds Database Performance Analyzer Top SQL Statements Detail

Clicking on this pink bar takes you to a new set of metrics. By clicking on the Blockers tab, we can see the blocker SPID, waiter SPIDs as well as the associate code with Solutions from SolarWinds to perform corrective actions to prevent further blocking.

SolarWinds Database Performance Analyzer Blocker Code

With SolarWinds DPA capturing valuable performance metrics with low overhead, SQL Server DBAs and Developers are able to address business critical needs while uninterrupted monitoring is occurring. SQL Server DBAs and Developers can quickly address historical issues and resolve them with recommendations included directly in DPA.

DPA also includes real time blocking troubleshooting with the Current tab located on the top right of the screen for situations when users report blocking or an alert has been configured in DPA that needs to be addressed.

Issue 2 - Troubleshooting High SQL Server CPU Usage

Although high CPU utilization may not be a problem per se, being able to understand when the issue occurs and the code that is responsible for the CPU spike is often hard to determine. The first example showed how to drill into a particular SQL Server instance for a specific date and hourly time period with the DPA Trends interface. Once on the Timeslice tab for an hourly interval, we can see the corresponding code for each segment of the bars or the corresponding Statement IDs on the right. Although these items include CPU performance metrics, each item is very low level and would need to be reviewed individually to determine a CPU issue.

SolarWinds Database Performance Analyzer Database Trends Interface with Top SQL Statements

The Trends interface has a tremendous amount of information, straddling both the detailed and macro level metrics. In order to address the CPU load question at hand, SolarWinds Database Performance Analyzer for SQL Server provides macro level metrics at a resource level including SQL Server Instance O/S CPU utilization located on the Resources tab. In this example, we can see that the CPU exceeded the set forth threshold around 2:20 PM and 2:45 PM to validate some of the CPU concerns.

SolarWinds Database Performance Analyzer Resources Charts

With DPA's inherent flexibility, you can easily click on the SQL Text tab to see all of the corresponding code for the time period to correlate and troubleshoot the CPU at a code level. This includes clicking on the SQL Hash Name to view the corresponding Wait Type metrics, Query Advisors, Statistics, Blocking and more.

SolarWinds Database Performance Analyzer SQL Commands

The flexible DPA Resources tab is also customizable. By clicking on the Add Resource Chart button, there is an option to add over 20 different charts to the interface to simplify the troubleshooting process depending on the situation.

SolarWinds Database Performance Analyzer Add Resource Chart

Issue 3 - Resolving SQL Server Performance Anomalies

Capturing SQL Server performance is important. But knowing if the application is performing out of the norm is difficult to determine by just trying to visually compare metrics during two periods of time or by contrasting query plans over time. Understanding and reporting anomalies is inherent at numerous levels in SolarWinds Database Performance Analyzer for SQL Server including wait stats, resources, queries and tables.

DPAs Trends interface includes Anomaly Detection and is seen below the main chart for the Top Waits, SQL Statements, Programs, etc. tabs. When you hover your mouse over a bar in the Anomaly Detection chart, you can see the severity of the Wait Time Delta. Just as in the prior examples, you can hover over any segment of the bar for quick information or click through any segment of the bar to view all of the details, as well as customize the time period with the drop down on the top left of the screen. You can also click on a Statement ID's on the right of the main chart to drill into details for the statement as well.

SolarWinds Database Performance Analyzer Anomalies Top Waits

Located at the bottom of the Trends interface is an also additional five tabs (Advisors, Resources, SQL Text, Blocking and Deadlocks) with a variety of valuable information. As for anomalies, the Advisors tab offers informational and warning messages for specific queries related to high percentage of execution time for particular items such as wait stats (PAGEIOLATCH_SH, Memory or CPU), full table scans, blocking, missing WHERE logic and more. With this data rolled up daily, DBAs and Developers are able to focus their performance tuning efforts helping the user community with any queries that needs attention that have a high performance impact.

SolarWinds Database Performance Analyzer Advisors

Along those same lines, SolarWinds Database Performance Analyzer for SQL Server also includes valuable Query Advisors and Table Tuning Advisors on the Tuning interface which aggregates improvements needed for specific objects to improve the overall application performance. Recommendations can include techniques to solve table scans, index scans, index recommendations, extended wait times and more.

SolarWinds Database Performance Analyzer Table and Query Tuning Advisors

When you click through any of the Table Tuning Advisor entries, such as the Orders table, you are presented with the detailed findings with the Inefficient SQL Statements in the left pane with the percentage impact and the detailed corresponding recommendations to correct the code in the right pane. The expected performance gain can help set the correct expectations on the performance gain and also validate the improvement when testing.

SolarWinds Database Performance Analyzer Query Tuning Advisor Details

SolarWinds Database Performance Analyzer for SQL Server offers tremendous value for DBAs and Developers to track down performance anomalies aggregated at the table and query level as well as when reviewing SQL Statements and Wait Stats metrics. This takes the guess work out of determining if a problem really exists, how far from the norm the performance actually is and how to resolve the issue to get the application performance back on track.

Next Steps

MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Database Performance Analyzer.



Last Updated: 2020-05-11


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Comments For This Article






Related Resources

SolarWinds Database Performance Analyzer overview

SolarWinds Database Performance Analyzer article

SolarWinds Database Performance Analyzer article

SolarWinds Database Performance Analyzer article

SolarWinds Database Performance Analyzer webcast

SolarWinds Database Performance Analyzer download

Learn more about SQL Server tools