Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
Performance problems are rampant at our organization. Our team works hard to try to address the issues daily, but we seem to be spinning our wheels. We have invested in numerous tools in our environment by the various technology groups – Application, Systems, Storage, Database Administration, etc. Unfortunately, we are unable to see the entire application from the end user experience all the way down to the storage to really see the true picture of the problems we are facing. Trying to connect the dots from numerous applications is a time consuming manual process, very frustrating with differing time slices, units of measure, etc. and leaves a tremendous amount of analysis open to interpretation with different versions of the truth. As an organization how can we see the complete picture of our application performance at each tier?
Numerous organizations faced issues with performance problems that directly impact the bottom line. Both internal and external customers expect sub-second response time for applications. Poor performance is a bad representation of your company and can paint the wrong picture to your clients. This leaves the technology groups in a precarious situation. IT Management and their team knows the criticality of providing high performing systems, but struggle to come together to find a single solution to address the performance issues from the user all the way down to the storage.
IT Management really has the need to see all of the performance data for an application in a single interface, such that each of their teams can have input on key metrics for their group, reliable data collection that does not inhibit performance and the ability to collaborate as a larger team from one version of the truth. SolarWinds has recognized this challenge from numerous customers and has introduced PerfStack to meet this need.
PerfStack is an intuitive browser-based dashboard to troubleshoot performance issues at each tier in the application. Consider it your “Enterprise in One Interface”. This visual and intuitive dashboard is fully customizable including key metrics for each layer of your application that are stacked on top of one another to correlate metrics, rapidly troubleshoot issues and share data among teams. PerfStack reports data as collected by the SolarWinds Orion platform to manage IT infrastructure and applications from A to Z.
PerfStack’s value to the organization:
- Intuitive interface to drag and drop metrics from each tier in your application for a complete picture
- Ability to stack graphs with multiple counters for each tier of your enterprise
- Discovery of upstream and downstream resources from any tier in your application
- Intelligent data correlation with real time and predefined interval-based data across all SolarWinds modules
- Historical analysis with the ability to zoom into specific time periods
- Share fully customized templates with your team – all permissions based
Figure 1 – PerStack Introduction
Getting Started with PerfStack
PerfStack starts with a blank slate or you can use a predefined template to begin your analysis. With the blank slate, select the application, server or resource you are familiar with in the Metric Palette then PerfStack will auto discover all of the dependent upstream and downstream resources (See Call Out 1), which is called the Relationship Mapper. From there you can select the metrics that apply to the resources and drag and drop them from the Palette (See Call Out 2) to the graphs (See Call Out 3) to begin analysis and troubleshooting. Some metrics report data in real time and others are on a predefined schedule. You have the ability to hover your mouse over the graphs to get metrics for that time slice (See Call Out 4).
Figure 2 – PerStack Dashboard with Call Outs
Data Sharing with PerfStack
Once you have your PerfStack templates defined, you can save the template for re-use and time savings, share the template across the IT organization and have your IT experts refine the templates and counters for their area of expertise to streamline addressing future issues. As you troubleshoot issues it is possible to export the results or email out the URL to the team in order to access report to have everyone on the same page.
Time Slicing in PerfStack
Since troubleshooting performance issues is generally very time specific. PerfStack enables you to see real time metrics as well as historical data to get to the root cause of an issue. There are two options to drill into a specific time frame. First you can highlight a particular portion of the graphs to zoom into a specific time frame. A section option is use the interface in Figure 3 for common or custom options to further drill into a specific time frame such as the last 2 hours, 24 hours, 7 days, etc.
Figure 3 – PerfStack Time Selection
Change is constant in business and IT needs to best support the changes needed by the organization. Understanding changes and the associated performance impact from one release to the next is absolutely critical. Whether the platform change is software related such as a code change, OS patch, driver updates or hardware related such as replacing spinning disks with a flash solution, knowing the impact is essential to determine if new issues need to be addressed or if the change results in a positive return on investment. With PerfStack you have the ability to review your key performance metrics before and after the change to easily determine the impact across the application.
This situation is also applicable to companies moving from on-premises resources to the cloud to understand the impact to the user community. With PerfStack you have the ability to compare two systems side by side to see the performance differences in order to determine if the cloud-based resources can properly support the user community. In the screen shot below, there is a simple comparison of resources between applications supported on-premises and in the cloud.
This extends PerfStack’s ability to not only demonstrate the performance of a single platform end to end, but also compare the same counter across multiple servers in order to see which platform is performing the best from an end user perspective. This comparison analysis is necessary to justify the return on investment.
Figure 4 – PerfStack On-Premises vs. Cloud Performance Comparison
Inherent to the PerfStack dashboard is the ability to see usage patterns with applications. This can help address the critical need of capacity planning to properly support the organization. With the data from PerfStack it is easy to understand the application peak usage in terms of resource utilization, number of sessions, users, etc. and the associated demands on the infrastructure as shown in Figure 5 below. PerfStack enables IT Management to make strategic decisions to invest in tuning particular portions of an application or purchase infrastructure to scale to the needs of the organization.
Figure 5 – PerfStack Trending
Root Cause Troubleshooting
The biggest need when a performance issue arises is the ability to bring the entire IT team together to troubleshoot the issue. By focusing the team’s efforts on the true issue, this minimizes the impact to the overall organization. With PerfStack you are easily able to line up the entire stack and resources in one interface to have the full picture with data normalized across multiple vendors. But PerfStack’s value does not end here.
As a SQL Server DBA, do not just see PerfStack as a bunch of pretty graphs. You have the ability to drill down from a particular time period to see the highest counters with the associated code to be able to focus your tuning efforts. PerfStack moves from enterprise monitoring down to the SQL Server code that needs to be optimized, if SQL Server is in fact the issue.
In Figure 6 below, we can see the initial data set is for a 12-hour period. This is further refined by selecting a two-hour window where the SQL Server Wait Time by Wait Type counters are displayed to move from the macro to micro level to best understand the workload during that time period as shown in the Data Explorer. At this point the counters can be compared against corresponding time frames or drilled into further to determine the T-SQL code that needs to be addressed.
Figure 6 – SQL Server Wait Time by Wait Type Counters
From the SQL Server Wait Types shown in Figure 6, we can drill down into specific SQL statements to begin the correction process. Based on the same time slice, PerfStack will display the top SQL statements (stored procedures, ad-hoc statements, prepared statements, etc.) by Total Wait Time and Percentage of the Total Wait Time to prioritize your troubleshooting and bring the issue to resolution.
Figure 7 – Top SQL Statements by Total Wait Time in PerfStack
How do I get started with PerfStack?
- Check out the free resources available for PerfStack.
- Download PerfStack to see how it can help you.
- Think about all of the challenges you face with troubleshooting performance across your enterprise and how PerfStack can solve these problems.
- Communicate with your team and management about how you think PerfStack
- Single dashboard to resolve real time and historical performance issues
- Correlate metrics across each tier of your application and infrastructure
- Enable your entire IT team to collaboratively provide the best performance for your organization
- Put PerfStack through its paces in your environment, share the results with your team and determine your next steps.
- Learn more about SolarWinds PerfStack and get your free evaluation version
- Additional Resources
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of PerfStack.
Last Update: 2018-03-08
About the author
View all my tips