Quickly Pinpoint SQL Server Performance Issues with SolarWinds Database Performance Analyzer


By:   |   Updated: 2020-04-29   |   Comments (1)   |   Related: More > Monitoring

Problem

My team needs to quickly pinpoint the performance issues in my SQL Server environment. With all of the demands at work, our team needs to find and understand the issues, then review the code and take corrective actions. We need to ensure our production SQL Server environment is running smoothly, because when it is not, the entire company feels it.

Our team is growing and we have SQL Server Professionals with a variety of skill levels. Everyone on the team needs to pitch in and help address the performance issues, not just our senior staff. For some team members, often it is very difficult to determine the root cause of the performance issue. Even the most senior performance focused DBAs struggle with intermittent problems consuming large amounts of their time. Sometimes it is a single process, but more often there is a chain of events that needs to be reviewed and correlated to find the answer. We are also faced with supporting multiple versions of SQL Server with a slightly varying tool set. Some of the servers are on premises and others are in the cloud. Here too the challenge is the need to manage both physical and virtual machines, which adds another wrinkle to the equation.

Based on these issues, do you have any suggestions to help us solve this problem?

Solution

Time is certainly of the essence when there are SQL Server performance issues that impact the entire organization. Many environments are similar to yours with numerous versions of SQL Server, physical and virtual servers in multiple locations as well as having the need for everyone on the team to be able to quickly pinpoint, dissect, correlate and correct the issue at hand.

There are a number of ways to address your SQL Server performance issues. SQL Server ships with a number of native tools such as the Dynamic Management Objects, Extended Events, Performance Monitor and more to help identify the issues. These tools are very helpful, but can become overwhelming with large amounts of data. The performance issues are in the data; it is just a matter of being able to train the team to find them in a rapid manner and communicate with your peers.

In some respects, there is a build vs. buy decision as well as an investment decision in training to manage a diverse environment. The small, yet growing team needs to support core business needs of the organization and conquer the performance issues. Trying to accomplish both with time constraints is a large challenge.

Based on the time constraints, business needs, build vs. buy decision and building your team, selecting a performance monitoring and tuning product may make the most sense. One product to consider is SolarWinds Database Performance Analyzer for SQL Server. Here is how this product can help you:

  • Response Time Analysis – Pinpoint the code causing the most issues and focus on the highest wait types in the code to work towards resolving the issue
  • Correct Database Design and Coding Issues – Do not just throw hardware at the situation to mask the software issues
  • Anomaly Detection – Know when there is a performance issue outside the norm as opposed to focusing on purely high metrics
  • Simplicity – Understand poorly performing code in a graphical manner with supporting metrics to prevent finger pointing, but rather focus the team on solving the problem
  • Recommendations – Valuable recommendations to correct problematic code and indexes
  • Data – Real time and historical data to correct issues and determine trends, with the ability to drill down to data in one-minute increments
  • Comprehensive – Intuitive browser-based interface to dissect performance issues against numerous versions of SQL Server, VMware and other database platforms to share with Developers, Admins, QA and Management without compromising production security
  • Reporting and Alerting – Inherit reporting on each interface with the ability to send the results via email, custom reporting and proactive alerting to the team
  • Low Impact – Database Performance Analyzer (DPA) places less than 1% load on the monitored servers

Let’s demonstrate how SolarWinds Database Performance Analyzer can solve SQL Server performance issues highlighting the following functionality:

  • Enterprise Dashboard
  • Trends
  • Tuning
  • Current
  • Resources
  • Virtualization
  • Reporting
  • Alerts

SolarWinds Database Performance Analyzer Enterprise Dashboard

When you login to SolarWinds Database Performance Analyzer (DPA), you begin with an enterprise wide dashboard providing insight across all of your monitored instances and groups your servers by database platform to help organize large multi-platform environments.  This interface provides high level wait based metrics as well as alerts and warnings on a per server basis to immediately begin prioritizing and troubleshooting issues.

SolarWinds Database Performance Analyzer Enterprise Dashboard

When you click on a server, the next logical place to land is the SQL Tab to begin troubleshooting at the database level.  Let’s check that out next.

SQL Tab of the Trends Interface in Database Performance Analyzer for SQL Server

When you launch the Trend interface, the first item that catches your eye is the bar chart for the T-SQL Statements. Intuitively, the tallest bar chart with the largest single color is the T-SQL code requiring the most attention. Each color corresponds to a single query plan and these are color coded across the days in the chart to easily see the impact. As you hover your mouse the name of the code, wait time, total wait time, percentage of wait time, average executions in seconds, executions and code.

Anomaly Detection is the second portion of the interface which quantifies the impact of the waits from the Top SQL Statements chart.  The anomalies are color coded as red for Critical, orange for Warning and green for Normal Range.  This is where correlating the data from these two charts really shows the value of DPA.  Although the Top SQL Statements chart can have some tall bars, it is a matter of focusing on the Critical data in red from the Anomaly Detection and determining the corresponding code from the Top SQL Statements chart as shown in the SQL Text tab.

SQL Tab of the Trends Interface in Database Performance Analyzer for SQL Server
SQL Text of the Trends Interface in Database Performance Analyzer for SQL Server

The Trends interface provides detailed metrics for Waits, Programs, Databases, Machines, DB Users, Files, Drives and Plans which enable you to troubleshoot the performance issues from a number of different perspectives.

Tuning Tab in SolarWinds Database Performance Analyzer

Database Performance Analyzer includes both Query and Table Tuning advisors, which provide recommendations to improve the performance for specific queries as well as indexes that can benefit numerous queries.  All of these recommendations are prioritized on a single screen with the associated performance benefit.

Tuning Tab in SolarWinds Database Performance Analyzer

For example, if we click on the orders table in the Table Tuning Advisors column, we are presented with the following detailed metrics related to the inefficient SQL statements on this table with the associated performance impact, T-SQL code, wait time, executions, reads, etc. and code you can use immediately to implement the recommended index.

Table Tuning Advisor in SolarWinds Database Performance Analyzer

Learn more about Database Performance Analyzer Query and Table Tuning Advisors.

Current Interface in the SolarWinds Database Performance Analyzer for SQL Server

The Database Performance Analyzer’s Current interface is intended to report on performance data for the last hour and is divided into three main sections:

  • First is the queries data with data from the last hour for the Highest Total Execution Time, Long Running (Average) queries and the Most Executed queries.  In this portion of the interface you can hover your mouse over any of the bars to see performance metrics and the SQL Text.  When you click on any of the bars, you are taken in context to the Trends tab to begin troubleshooting as we demonstrated earlier.
  • The second section relates to the Sessions which includes the Currently Active, Currently Blocked and a URL to review all of the sessions with the spid, login name, application name, status, etc.
  • The third portion of the interface are graphs with the CPU Utilization, Page Life Expectancy, SQL Disk Write Latency and SQL Disk Read Latency.
Current Interface in the SolarWinds Database Performance Analyzer for SQL Server

Resources Interface in the SolarWinds Database Performance Analyzer for SQL Server

The Resources Interface in Database Performance Analyzer consists of five tabs with numerous line graphs to quickly determine the status of your SQL Server instance and be able to compare the results over the last hour, day, week, month or longer. The graphs include the following metrics:

  • CPU
  • Memory
  • Disk
  • Network
  • Sessions
  • Waits
Resources Interface in the SolarWinds Database Performance Analyzer for SQL Server

Hovering your mouse over any of the Warnings or Critical entries will pop-up a secondary screen with an explanation of the entry as well as with the associated percentages for a Warnings or Critical message along with the baseline data.

O/S Memory Utilization in SolarWinds Database Performance Analyzer

Virtualization Performance Tuning in SolarWinds Database Performance Analyzer

As SQL Server DBAs, we are at the intersection of the database, code, operating system, storage, infrastructure, etc. and need to speak the proper language with the associated groups that manage each of these resources. We also need to understand the underlying infrastructure supporting SQL Server. With virtualization being the norm for so many environments, having insight into the VM and Host supporting our SQL Server instance is critical to put the pieces of the performance puzzle together.

With Database Performance Analyzer, the Virtualization and Performance metrics can easily be toggled by pressing either the Virtualization or Performance buttons at the top of the interface as shown below. The functionality of the Virtualization interface is very similar to the Performance interface with the SQL Server Metrics listed above. The performance issue discovery process and correlation is similar to the earlier descriptions with the SQL Server metrics and code serving as the foundation to troubleshoot the performance issues. The Virtualization data is divided into VM Layers, Current, VM Config and Resources on the top right of the interface similar to the SQL Server data in the previous section.

Virtualization Performance Tuning in SolarWinds Database Performance Analyzer

Learn more about Intelligent SQL Server and VMware Performance Monitoring Software - Database Performance Analyzer.

Database Performance Analyzer Reporting

Although a large portion of the SolarWinds Database Performance Analyzer for SQL Server can be considered a reporting tool, there is a reporting module to build custom reports to meet your specific needs. The custom reports can be scheduled on a regular basis for delivery to particular team members. These can help for daily health checks as well as serve as a report to technical management to understand the overall status for the environment. The reports help to focus on the most resource intensive queries, databases, applications, users, machines, plans, etc. There are also options for average waits and typical waits for a day to obtain some trending data.

Database Performance Analyzer Reporting

Below is a sample Top SQLs and Top Waits report, but these reports can be customized to include the detailed code.

Top SQLs Weekly Report in Database Performance Analyzer
Top Waits Weekly Report in Database Performance Analyzer

Alerts in SolarWinds Data Performance Analyzer

Beyond the data collection, monitoring, analysis, recommendations and reporting from SolarWinds Database Performance Analyzer for SQL Server, alerts can also be configured for items outside the scope of just performance.

Alert Status in SolarWinds Data Performance Analyzer
Manage Alerts in SolarWinds Data Performance Analyzer

Summary

With the time constraints your team faces, building an internal tool to match the functionality of SolarWinds Database Performance Analyzer for SQL Server would add a tremendous amount of work to your team’s plate. DPA for SQL Server is an end to end tool to collect, manage, report and recommend corrections based on your performance data. This is accomplished with less than 1% load on the monitored servers for DPA to collect the performance metrics, whether your SQL Server instance is on premises, in the cloud, virtualized or a physical server. The browser-based interface is simple and consistent providing opportunities to slice and dice the data as needed for all of your team members (i.e. Developers, Operations, QA, Management, etc.) not just DBAs without compromising production security to troubleshoot a performance issue. The intuitive interface guides you to the issue and encourages exploration of the data to gain new insights for your environment. Speaking of the data, it is not just raw data; it is aggregated, consolidated and prioritized to see trending over time with the flexibility to be able to break the data down to 1-minute granularity.

Consider SolarWinds Database Performance Analyzer for SQL Server to save your team time to efficiently resolve your SQL Server performance issues.

Next Steps

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



Last Updated: 2020-04-29


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources





Comments For This Article




Tuesday, October 21, 2014 - 12:17:06 PM - Tim Back To Top (35029)

Hi Jeremy,

This post was great for helping us understand the possibilities available without having to create something from scratch or search multiple posts to get the same answers that we can get from DPA.

My manager would like to know what other products are also available that have similar capabilities before we can justify spending the bucks on DPA?

Thanks,

Tim



download


Recommended Reading

How to setup SQL Server alerts and email operator notifications

Reading the SQL Server log files using TSQL

SQL Server Backup Monitoring with PowerShell

Determining space used for all tables in a SQL Server database

Methods to determine the status of a SQL Server database





get free sql tips
agree to terms


Learn more about SQL Server tools