Find, Analyze and Optimize SQL Server Performance with SolarWinds Database Performance Analyzer
By: Jeremy Kadlec | Updated: 2023-02-15 | Comments (1) | Related: More > Monitoring
Problem
As a SQL Server Professional, application performance is one of my top priorities. I know our applications have numerous inefficiencies and we are constantly firefighting to keep our internal and external users productive. Our team collects real-time data all day long and get lots of metrics, which is great, but it is really difficult to pinpoint the issues that will yield the highest performance gain. Often times we are shooting from the hip with ideas. I need to know that spending time to work through our code release process will yield measurable performance gains. How can I correct the inefficiencies in our applications and have our SQL Server DBA team focus their efforts on the most important issues?
Solution
With large amounts of performance metrics at the server, database and object level, correlating, aggregating and trending data is difficult with an ever-increasing number of items to address on a daily basis. To take this a step further and truly prioritize the Microsoft SQL Server performance issues often becomes very difficult. Often times the performance issue that gets the most attention is generally based on the loudest complaints. This is not necessarily the biggest issue; it could be a symptom of another problem.
At SolarWinds, they have been helping companies around the globe for decades by finding and analyzing SQL Server performance issues by focusing on response time analysis. With the latest release of Database Performance Analyzer (DPA), the team at SolarWinds has taken this a step further by analyzing the overall workloads to help Database Administrators optimize the database engine workload. This optimization is based on analyzing the overall workload on a daily basis to determine the biggest issues at the query and table levels then providing tangible recommendations with the estimated performance benefit to justify the changes. Let’s dive into Query Performance Analysis and the Table Tuning Advisors to learn how you can streamline your database performance optimizations.
Query Performance Analysis in SolarWinds Database Performance Analyzer
Query Performance Analysis provides a means to streamline improving your query performance by focusing on the queries with the highest waits, drilling into the portions of the query that are most problematic and getting recommendations to improve the query including the code. This puts the most important information in front of a DBA as quickly as possible in an intelligent manner to troubleshoot performance issues such as:
- Most expensive SQL queries and stored procedures for the overall SQL database workload
- Highest wait types for each SQL query to begin query optimization
- Inefficient query plan steps to focus on
- Correlating performance metrics at numerous tiers in the stack to determine bottlenecks
- Code executions on a daily basis to determine the impact on the wait types
This process starts when logging into DPA and checking out the 30-Day Trend Page where you are able to drill into individual queries for the associated code, their metrics and recommendations.
When you click on a query hash from the 30-day trend view, all of the performance metrics for that individual query are shown for the last 30 days – keeping the timeframe consistent. The new query performance analysis view contains multiple sections that are collapsible/expandable so that emphasis can be drawn to the most important data. The data in this view is intelligently displayed in a couple ways:
- If there are resource metrics associated with the predominant waits for the query, those will be pulled into the metrics section.
- If this query was involved in significant blocking (either as the blocker, the blockee or a deadlock), it will expand the blocking section.
- If the optimizer used multiple plans for this query in the timeframe you have set, it will expand the plan section.
The top two sections are reserved for any findings during analysis of this query:
- Query Advisors – missing index recommendations (clustered, non clustered or covering indexes), significant wait types, unusual usage patterns (periods with high executions), missing where clauses, full table scans, etc.
- Table Tuning Advisors – indicates that this query was causing inefficient workload against one or more table (more on this in a bit).
If the query itself didn’t change, yet is accruing more wait time, then one of the other sections provided in this view can help answer why, such as:
- Increased executions or physical reads vs logical (statistics)
- Plan change (plans)
- System resource pressure (metrics)
- Concurrency (blocking)
The example below shows the predominant activity for the chosen query is Memory/CPU. Because of this, memory and CPU metrics are pulled into our view in the metrics section. However, had the predominant wait been disk related (say pageiolatch_ex), disk latency and other related metrics would have been pulled into the view.
As we click through the Plan link on the right side of the screen, we are able to see the entire query plan with the associated costs as well as the Plan Advice at the bottom of the page. The Plan Advice also includes the associated impact to justify the benefits associated with following the recommendation.
To continue the query analysis, you have options at the chart level to add relevant counters based on the specific report. The top chart is also sticky and hovers above each section to better correlate the data across the 30-Day Trend chart.
Beyond the ability to add statistics for specific charts, you also have the ability to add 66 individual metrics to truly customize the charts based on your workload.
Further, the performance metrics and associated recommendations at the query level, with Query Performance Analysis you also have the ability to click on the SQL TEXT tab to see the T-SQL code associated with the query as well as the SUPPORTING DATA tab with the ability to see the Database, User, Files, Machines, Programs, etc. corresponding to the query plan.
How can Query Performance Analyzer help me?
Query Performance Analyzer helps SQL Server Professionals by:
- Intelligently displaying the most important performance information to streamline the performance tuning process by the DBA
- Ability to customize interface to review the most relevant metrics for your workload
- Viewing 30-day trend of the query performance with the ability to drill down into the query plan details
- Copying, pasting and minimally updating code to fast track the performance improvement
- Justifying performance recommendations:
- Query inefficiencies
- Index tuning
- Missing indexes
- Predicate issues
- Data type mismatches
- Outdated statistics
- Large tables
- Providing a holistic view of the query performance with customizable visuals
Table Tuning Advisors in SolarWinds Database Performance Analyzer
Based on the data that DPA is already collecting, a second new feature in the latest release is called Table Tuning Advisor. These recommendations are a by-product of the vast amount of data that DPA is already collecting. However, now wait types are being analyzed daily to understand the performance implications at the table level and how particular recommendations can simultaneously benefit numerous processes in your environment. All of this is accomplished with no additional load on your monitored SQL Server instances to understand index issues, data type mismatches, WHERE clause predicate issues and more via a proprietary algorithm aggregated at the object level. As a DBA, this enables you to proactively address performance issues prior to users being significantly impacted.
DPA’s Table Tuning Advisors can help with:
- Identifying the most inefficient queries for the table
- Correlating issues at the table level that impact numerous queries
- Determining missing indexes
- Uncovering queries with a large number of reads and a low percentage of data returned which could be an issue with conditions in the WHERE clause
- Focusing on the predicates in the WHERE clause to determine patterns and inefficiencies
- Highlighting out of date statistics
- Detecting the percentage of insert, update and delete transactions referred to as “churn”
- Making you the proactive DBA in your organization having the ability to solve performance problems before they negatively impact the user experience
To access the Table Tuning Advisor in DPA, from the 30-Day Trend Page, click on the TUNING tab on the top right of the screen.
On the DPA TUNING tab, Query Advisors are presented on the left and Table Tuning Advisors are on the right. On the top left of the screen you can review recommendations for prior days by clicking on the drop-down box to select a date in the last 30 days. The Query Advisors match the new functionality demonstrated above, but are just a new means to access the data in a prioritized manner. On the right is the new Table Tuning Advisors with tables listed in priority order with Alerts listed first followed by Informational messages.
In this example, we click on the LineItem recommendations and see a prioritized listing of the inefficient SQL statements on the left with the details per statement on the right. With this table, a single query plan is table scanning multiple times at steps 32, 17 and 62 and would benefit from an index on l_orderkey and l_tax. We can also see 7 additional SQL statements that would be improved with the same index. By analyzing the workload at the table level, parsing the query plans, and breaking down the inefficiency steps to justify the recommendations, we have the opportunity to create one set of indexes to correct numerous queries yielding improvements across numerous applications and processes.
On this interface we are also review historical recommendations for the table to understand the changes over time which can be selected by the date drop down box on the top left of the interface.
On the right side of the index recommendations, you can click the “Show index DDL” option which will pop-up the following screen with the corresponding code. The code can be copied to the clipboard to begin testing the index recommendation to validate the Projected Impact from DPA corresponds to the improvement you see in your testing environment.
To round off the analysis on a table basis, below the performance recommendations and justification, is the Current Table Information including general meta data, Average Data Churn (the percentage of insert, update and delete transactions on the table), Existing Indexes and Columns with their data type, null configuration and Defaults. This information is helpful when reviewing the table rather than having to go to another tool.
How can Table Tuning Advisors help me?
Table Tuning Advisors helps SQL Server Professionals by:
- Proactively addressing performance issues before the user community experiences major problems
- Prioritizing alerts to focus performance improvements
- Aggregating inefficiencies at a table level for a single correction to potentially improve numerous processes
- Estimate performance improvements to justify code changes
- Copying, pasting and updating code to streamline the testing and implementation process
- Understanding the percentage of data churn to determine the benefits associated with the index as compared to regularly scheduled index rebuilds
How do I get started with Database Performance Analyzer?
- Think about the hours it takes to identify, diagnose, validate, test and
deploy SQL Server code changes to your applications.
- With the new Query Tuning Advisors and Table Tuning Advisors, DPA can prioritize and streamline this process significantly to focus on core business initiatives.
- If you are experiencing performance problems with third party applications
and concerned you have no options to help your users, don’t give up.
- DPA identifies inefficiencies in Third Party Applications as well providing you with a means to work with your vendor to provide solutions to problematic performance problems plaguing the users.
- Put DPA through the paces in your environment and see how quickly DPA can
help identify then help you correct performance problems in your production
environments and help identify performance problems in QA environments before
impacting the organization.
- Learn more about all that DPA has to offer with these resources.
- Get your free download of DPA and put it through the paces in your environment.
- Communicate how
Query Performance Analyzer and Table Tuning Advisor in DPA will help your
team:
- Prioritize performance issues across the entire workload of the instance based on waits your system is experiencing
- View 30-day performance metrics trending which is fully customizable for your environment
- Justify the performance improvement with the proposed corrections to address the root cause issue
- Plug and play code to streamline the implementation
- No performance overhead with an agentless architecture for these new features, simple new algorithms to put the data in the hands of the DBA to make fast and informed decisions
- After working with DPA Share the results with your team and determine your next steps to radically improve the performance of your SQL Server applications.
Download Product Trial - SolarWinds DPA
Next Steps
- Learn more about Database Performance Analyzer and get your free evaluation version
- Additional Resources
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of Database Performance Analyzer.
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