Intelligent SQL Server and VMware Performance Monitoring Software - Database Performance Analyzer
By: Jeremy Kadlec | Updated: 2023-02-16 | Comments (2) | Related: More > Monitoring
Problem
We have a large Microsoft SQL Server environment supported by physical servers and VMware in our datacenter as well as cloud infrastructure (Azure, Amazon AWS and Google Cloud). We believe this architecture will be the reality for our organization for a number of years to come. Unfortunately, we are facing a number of application performance challenges. We have frequent situations where the end user experience degrades. My team of SQL Server DBAs knows that no code changes have been made, so we start hunting for answers pouring over logs, querying the DMVs, etc. Then out of nowhere the end user response time is back to normal with no changes from my team. We have no explanation and we ask the operations, network, storage, etc. teams, no one tells us what changed to degrade the performance or bring the end user experience back to normal. We need to have a single view of our environment, be able to measure the end user response time, understand when changes are made, get alerted on issues, understand the dependencies between the code, servers and storage, then optimize as necessary. We need to be more responsive to business and have predictable SQL Server performance, but we just do not have it together. Can you give us some insight into a monitoring solution that can help us with performance analysis and optimization?
Solution
A predictable end user experience with consistent uptime is not a mistake; it is a product of a coordinated team aligned with the same goals in mind. As SQL Server professionals, a large part of the database performance monitoring equation rests on our shoulders. It is our responsibility to ensure high performance along with numerous operational items. There could be a number of reasons why performance has degraded and returned to acceptable levels, but something would have to changed either on-premises, in the virtual environment or in the cloud. It could be:
- VMware guest moves to a new host
- VMware new guests moves the host supporting SQL Server
- VMware offline guests get powered on the host supporting SQL Server
- VMware resource pool change or allocation of new resources
- Configure and deploy Storage \ SAN changes
- Network traffic or network usage
- SQL Server statistics out of date
- Large data process fragments a key index
- Ad-hoc SQL Server process is running
- Query parameter issues
In all of these situations it is critical to gain insight into your SQL Server environment to understand the root cause of the issue, begin remediation and validate that the end user performance is at acceptable levels. As SQL Server Professionals, we can generally determine if there is an issue internal to the database if we are at work and available. But if the issue occurs when we are in a meeting or no one brings the issue to our attention in time, it is tough to determine the root cause of the issue without monitoring tools. So, what do we do?
In your environment, it sounds like another wildcard is VMware. With physical servers we knew the hardware resources. If the hardware changes, we knew about it because there was some sort of outage. The same is true with the SQL database load. We know our SQL Servers, the general processing schedule and the unique application needs, so the general load placed on the servers and storage was fairly consistent with both direct attached storage and SANs. But what happens when a VMware guest supporting our SQL Server moves to a new host? How do we know if the existing guests and new guest will not impact one another? What do we do?
These types of challenges are common at many organizations. To address these needs, I would like to introduce Database Performance Analyzer from SolarWinds providing insight into both Microsoft SQL Server and VMware performance. Database Performance Analyzer focuses on measuring wait times impacting the end user experience. Wait time data is collected in a low impact, agentless manner to address real time and historical issues with meaningful correlation and drill down. SQL Server and VMware performance monitoring data is consolidated to have a single view of the actionable items across the enterprise for physical, virtualized and infrastructure in the cloud to ensure high performance. Let's check out what functionality Database Performance Analyzer has to offer for SQL Server and VMware performance monitoring, which can be integrated into the SolarWinds Orion Platform.
SQL Server Performance Monitoring
Database Performance Analyzer provides a seamless view of your database environment across physical, virtual and cloud infrastructure to understand resource usage. The main Database Performance Analyzer dashboard has two sections. First, is a prioritized overview of the environment and the second is a listing of servers based on the database platform. The top portion of the interface provides a priority list of servers experiencing issues related to Wait Time, Queries, CPU, Memory, Disk Space and Sessions as well as flagging which instances have triggered an alert. When you click on any of the critical or warning icons in these sections, you can see the servers experiencing these issues and begin to research the issues. The bottom portion of the interface lists all of the servers. The servers are grouped based on database platform with the ability to see any issues related to Waits, Queries, CPU, Memory, Storage IOPS or Sessions. When you click on any of the issues, you go directly to the server with the issue to begin the analysis. Another key point is the Action drop down in the middle of the second interface. With this interface you can immediately navigate to the Current Activity, Virtual Machine Layers, Resources, Logs, Options and more.
After clicking on any of the server names from the main Database Performance Analyzer dashboard, you have the ability to slice the performance data based on Top SQL Statements, Wait Times, Programs, Databases, Machines, Database Users, Files, Drives or Query Plans. Once you have sliced the data to identify the performance issue you are trying to resolve, you can see the wait times associated with the code, the offending code as well as recommendations to correct the code based on the high wait type. This intuitive graphical interface enables you to quickly identify issues and get recommendations to correct the issue minimizing the impact to the organization.
We have just covered the very tip of the iceberg in terms of the SQL Server capabilities for Database Performance Analyzer. For a comprehensive review of the SQL Server performance tuning capabilities with Database Performance Analyzer, check out this tip - Quickly Pinpoint SQL Server Performance Issues with SolarWinds Database Performance Analyzer.
SQL Server and VMware Performance Monitoring Tools
In terms of pure SQL Server performance monitoring, Database Performance Analyzer is very feature rich. But in many environments, the missing piece of the puzzle is the correlation between SQL Server and VMware performance. Database Performance Analyzer has delivered a solution for SQL Server Professionals to bridge this gap and get insight into the VMware stack as it relates to SQL Server. By clicking on the "Virtualization" link at the top of the page, you land on the same style of interface as shown above for the SQL Server monitoring, with prioritized enterprise metrics at the top and detailed metrics listed per server in the bottom section. You have the same capabilities to drill into issues (Wait Time, CPU, Memory, Disk, Network performance and the Actions menu) from the top priority list or from the detailed listing. Another noteworthy piece of data on this interface is the Host column. This is sortable to identify which guests reside on a single host, which is a key piece of information many SQL Server Professionals have not had access to in the past for VM monitoring.
Once you click on a server, you can navigate to the VM Layers interface with two sections in the visualization. The first section includes the Top SQL Statements, Wait Times, Disk Waits, Memory and CPU Waits, Programs and Databases. In this section you can drill into the SQL Server performance details to understand bottlenecks and database latency. The second section is a stacked layered approach with top down data including the Database Instance, Virtual Machine\Windows operating system, ESXi Host and Storage with numerous counters for each chart. As you are troubleshooting and change dates, statements, etc., the data in both sections updates in real time to understand the performance metrics across both the SQL Server and VMware stack.
In the second section, stacked layered data, there are a few items that probably caught your eye:
- Data Selection - Summary data is shown by default, but there are radio buttons to drill into details for CPU utilization, Memory, Disk and Network bandwidth to get more detailed data.
- More Information - By hovering your mouse over any of the counters on the right of the charts initiates a pop-up box with more information about each of the counters to help understand the metric in the chart and whether the value is acceptable or not.
- Arrows with Vertical Lines - Did the arrows with
vertical lines catch your eye? There are actually two types of lines
that can appear on these charts. They are annotations of a change to
VMware. The first vertical line is a gray indicating a change has been made to the host. The second is a blue
line that indicates a change to the guest.
- When you hover your mouse over one of the arrows at the top of the line, a box pops up showing the events that occurred on that specific day. These events include vMotion changes such as moving a guest to a new host, powering up or shutting down guests, etc. Here is a screen shot as a point of reference.
As a SQL Server Professional, Database Performance Analyzer delivers insight into the entire stack which is information that you generally did not have access to before. This gives you new insight into the virtualized environment without having to ask the infrastructure team or be a VMware expert.
Real Time VMWare Monitoring Performance Metrics
In the Current interface for Database Performance Analyzer, you have access to the last 60 minutes of data with a focus on the SQL Server queries causing the most waits in the top section and four key metrics from a VMware perspective. The VMware metrics include:
- VM CPU Usage
- Host CPU Usage
- VM Active Memory Usage
- Host Memory Usage
The SQL Server query data is split between three tabs:
- Highest Total Execution Time
- As you click on any of the values in the top bar graph you drill into the time slice with the VM Layers interface as shown in the previous section. This enables you to get more contextual information about the query and resource utilization down to minute time slices to really dissect the performance issues.
- Long Running (Average)
- This is a listing of code with the ability to drill into the details and see the code causing the performance issues.
- Most Executed
- This is also a code listing with the number of executions and detailed code.
The Current interface in Database Performance Analyzer helps focus on the most recent database activity with the ability to drill into the details from both a SQL Server\VMware resource perspective as well as the actual code causing the issues. The integration with the VM Layers interface enables you to see the full picture and quickly take action to resolve issues impacting the end user experience.
VMware Configuration
With physical servers you are able to see the CPU, memory and disk resources in the Windows and SQL Server tool sets. With VMware you still have access to that information, but not have insight into the VMware infrastructure. SolarWinds recognizes this information is valuable for SQL Server Professionals to troubleshoot performance issues and provides a read-only view of the VMware infrastructure in Database Performance Analyzer including:
- Virtual Machine
- Host (ESX or ESXi)
- Cluster
- Host VMs
- Cluster VMs
- VM Storage
- Host Storage
Database Performance Analyzer delivers insight into the VMware infrastructure to help SQL Server Professionals understand the configuration and resources available to best help the organization maximize the infrastructure investment for the best user experience.
SQL Server and VMware Resource Utilization
As a SQL Server Professional under pressure to address performance issues on a daily basis, sometimes you get lost in the weeds. Often times, taking a step back to review trending overtime can be very beneficial to understand overall resource consumption. Database Performance Analyzer features the Resources tab which provides data trending on a per counter basis. There are more than 60 counters with charts split by CPU, Memory, Disk, Disk Device, Network, Sessions, Waits, TempDB, Backups and Licensing Compliance. Data can be viewed over the last hour, 24 hours, 1 week, 1 month, 6 months or 1 year. This can help to identify usage patterns, processing changes and more.
For more information about each of the counters, click on the "Information" link on the bottom right of the interface to get more insight into the counter as shown below.
Long term performance trends are beneficial to understanding how a platform is meeting user needs, determining available capacity to support new users, addressing unexpected changes in resource utilization and more. The charts in this portion of Database Performance Analyzer are a simple yet effective means to understand how your SQL Server and VMware platform is performing.
How do I improve my performance monitoring?
- Go check out all of the performance monitoring resources available for SQL Server and VMware from Database Performance Analyzer.
- Download Database Performance Analyzer to see how it can help you.
- Think about all of the performance monitoring challenges you face: immediate resolution, real time and historical data, intelligent correlation, alerting, reporting, etc. and how Database Performance Analyzer can solve these problems.
- Communicate with your team and management about how you think Database
Performance Analyzer will
help.
- Simplicity - Intuitive interface to drill into servers, databases, code, and more to track down the performance culprit
- Correlation - Global view of SQL Server and VMware performance issues with the ability to easily drill into the issues
- Resolution - Prioritized color coded issues list to quickly drill into the details for root cause analysis
- Data - Access to real time and historical data for both SQL Server and VMware for the complete picture to ensure high performance
- Comprehensive - Central browser based interface for insight and resolution across all major database platforms (SQL Server, Oracle, DB2, SAP ASE, AWS, etc.)
- Put Database Performance Analyzer through its paces in your environment, share the results with your team then determine your next steps.
Download Product Trial - SolarWinds DPA
Next Steps
- Learn more about SolarWinds Database Performance Analyzer for SQL Server and get your free evaluation version.
- Learn how to pinpoint SQL Server Performance issues with Database Performance Analyzer.
- Database Performance Analyzer supports your performance monitoring needs
across multiple platforms. Learn more:
- SQL Server
- Azure SQL Database
- Oracle
- DB2
- SAP ASE
- Amazon AWS \ RDS
- Amazon Aurora
- PostgreSQL
- MariaDB
- MySQL
- 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-16