Quickly Pinpoint SQL Server Performance Issues with SolarWinds Database Performance Analyzer
By: Jeremy Kadlec | Updated: 2023-02-15 | Comments (1) | Related: More > Monitoring
Problem
My team of Database Administrators (DBA) needs to quickly pinpoint the performance issues in our Microsoft 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 to optimize performance. We need to ensure our production SQL Server database instances are running smoothly, because when they are 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 (Azure, Amazon AWS, Amazon RDS, Azure SQL Database, etc.). Here too the challenge is the need to manage both physical and virtual machines, which adds another wrinkle to the equation when it comes to identifying the bottlenecks within the stack and spend our time wisely on query optimization.
Based on these issues, do you have any suggestions to help us solve our application performance problems?
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 with performance analysis:
- 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 in the database engine
- Correct Database Design and Coding Issues – Do not just throw hardware at the situation to mask the software issues (poor database design, incorrect indexes, table scans, inefficient execution plan, deadlocks, etc.), actually solve the performance problems
- Anomaly Detection – Know when there is a performance issue outside the norm as opposed to focusing on purely high metrics for query performance
- 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 (stored procedures, subqueries, WHERE clause, etc.) and indexes in the SQL database
- 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 based on the agentless architecture requiring minimal configuration
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 DPA 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.
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 DPA 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 for the most impactful SQL queries. 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 based on machine learning is the focus for 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.
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 DPA for Query and Table Optimization
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.
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.
Learn more about Database Performance Analyzer Query and Table Tuning Advisors.
Current Interface in the SolarWinds DPA 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.
Resources Interface in the SolarWinds DPA 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
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.
Virtualization Performance Tuning in SolarWinds DPA
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.
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.
Below is a sample Top SQLs and Top Waits report, but these reports can be customized to include the detailed code.
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.
Summary - SQL Server Performance Tuning with DPA
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.
Download Product Trial - SolarWinds Database Performance Analyzer
Next Steps
- Learn more about SolarWinds Database Performance Analyzer for SQL Server and get your free evaluation version to address your SQL Server needs.
- From a single installation of DPA you can also monitor VMware, Oracle, DB2, Sybase and AWS. Consider DPA as your first step to address these issues.
- Additional resources:
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Database Performance Analyzer for SQL Server.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor’s degree from SSU and master’s from UMBC.
View all my tips
Article Last Updated: 2023-02-15