Tackle SQL Server Performance Issues for Free with DPA Free


By:   |   Updated: 2020-10-05   |   Comments   |   Related: More > Performance Tuning


Do You Feel the Needů the Need for Speed in your SQL Server database? Are you frustrated with your tuning efforts?

Free MSSQLTips Webinar: Do You Feel the Needů the Need for Speed in your SQL Server database? Are you frustrated with your tuning efforts?

Learn how to tune your SQL Server queries for peak performance using Foglight's multi-dimensional Performance Investigator and SQL Optimizer for faster and more efficient queries.


Problem

We have SQL Server performance problems that have been plaguing particular applications with unexplainable slowdowns along with unhappy users and customers. Our environment consists of both physical and virtual servers, which often presents some challenges to determine the exact SQL Server performance issue. Unfortunately, on a daily basis I am very busy trying to manage our SQL Server environment and without a tool, it is difficult to capture enough data to determine the problem. Are there any options I can research to tame our SQL Server performance problems for free?

Solution

SQL Server performance problems can be evasive and truly plague an application so much that employees dread using the application. It is an unfortunate set of events that all too often results in employees losing faith in the application and impacting morale.

There are a number of alternatives to consider for addressing your SQL Server performance issue. Let's dig into these to see which one could make sense for you:

  • Hardware - Generally considered the "quick and dirty option" to patch a short-term problem. Often times this does not resolve the issue, only delay an actual resolution. Other times the hardware is insufficient for the workload and does need to be sized appropriately. It is imperative to understand the issue and avoid throwing hardware at a software problem.
    • Upgrade hardware - Add CPU's, memory, SSD's, new hardware appliances or add resources to a VM
    • New hardware - Server or upgrade a component such as your disk drives with solid state solution or SAN
  • Training - Opportunity to learn more about performance needs and the SQL Server engine, but your specific issue may not be covered in the training. Further, there are a lot of situations where environment\application specific factors can lead to a better answer, which is difficult to cover in a training class.
    • Free options - Webcasts, tutorials, articles, etc.
    • Paid training - Traditional classroom, online, conferences, etc.
  • Consultant - If you do not have the time, expertise or are unable to get training, an external resource can be contracted to help remediate a one-time issue, but fixing all of the issues are not always possible based on time frames, budget, politics, external dependencies, etc.
    • Consultant - Identify, correct, code, test and deploy high performing code
  • Products - There are a number of products on the market to choose from to help identify SQL Server performance issues. Some are included natively with SQL Server and others are built by another company. Some of this boils down to a build versus buy decision with limited resources. Which option is the best to support your business?
    • Scripts for point in time collection and analysis - Time sensitive to collect data which generally builds an incomplete picture
    • Home grown solution - Consider your total cost of ownership to build and maintain a product. How much time will it take to build and maintain a solution across all of your network devices, storage, hardware, operating system, SQL Server and code? Can that time be better used to focus on core business needs?
    • Purchase a paid product - Need to validate prior to purchase and be trained on the product.
    • Free products - Concern generally is "you get what you pay for", but is that always the case?

With all of your responsibilities, the severity of your SQL Server issue as you describe it and a mixed environment of physical and virtual servers, you may want to take a look at Database Performance Analyzer Free (DPA Free). This is a free version of SolarWinds Database Performance Analyzer for SQL Server. DPA Free provides insight into real time metrics with the ability to drill down into the code on your SQL Servers to identify and correct the issues. Let's take a look at DPA Free to see if it can help you.

 

How can DPA Free help to monitor SQL Server instances?

In a nutshell, DPA Free is focused on wait based analytics to identify performance problems on your SQL Server instance. This is achieved with very low overhead and no software installed on the monitored instance. These metrics provide a clear-cut set of data to focus on for troubleshooting performance issues. With DPA Free you have a visual representation of the performance issues over the last hour with the ability to drill into the details to understand your code, the performance bottlenecks and work towards resolving the issue. DPA Free also includes real time monitoring.

DPA Free includes the following:

  • Top SQL Statements
  • Wait based analytics
  • SQL Server session data
  • Application and User data
  • Locking and Blocking
  • Real time monitoring
  • Server Metrics - CPU, Memory, Disk, Network
  • Virtualization Metrics
  • Visualization of SQL Code to Hardware Utilization
  • Inherent Reporting

 

How does DPA Free work?

One great aspect of DPA Free is that it is the same architecture and interface of SolarWinds Database Performance Analyzer for SQL Server, not a separate tool. So, when you install DPA Free, you install the full product for free and have a baseline set of functionalities.

Although installing DPA Free is quick, some planning is recommended:

  • Find a utility server where you can install a web server. DPA Free is completely web based and needs a web server to interact with the product.
  • You need a SQL Server (which can be SQL Server Express Edition) where you can install a repository database to store the performance data collected by DPA Free.
  • You need a machine where you can work with the product. This is most likely your desktop or laptop, although since DPA Free is browser based, you should be able to use just about any computer, tablet, smartphone, etc.

 

How can I get value from DPA Free?

Let me demonstrate a few of the core sets of functionality with DPA Free:

  • Performance Dashboard
  • Performance Details
  • Resources Graphs
  • Virtualization Metrics

Please note: this is just a subset of the functionality available with DPA Free. Click here for a full feature set.

DPA Free SQL Server Performance Dashboard

Many of the interfaces in DPA Free could be considered summary dashboards with prioritized metrics. From these dashboards you can access lower level data to actually work towards solving your SQL Server Performance issue. One favorite is the Top SQL Statements interface where data can be viewed for a particular period of time based on a specific interval, to visually determine particular queries to review and see some of the corresponding high-level metrics. Hovering a mouse over the bar charts provides additional performance metrics and a preview of the SQL code.

SolarWinds DPA Free Performance Dashboard

Figure 1 - DPA Free Performance Dashboard

DPA Free Performance Details

In the bar graph above, each individual slice is a different query and by clicking on any of the individual slices of Figure 1, we can see the corresponding performance metrics as well as the associated databases, waits, users, query plans and more. This provides the critical metrics to understand the issue and to begin to tune and test alternatives to resolve the issue.

SolarWinds DPA Free SQL Server Wait Stats

Figure 2 - DPA Free SQL Server Wait Stats

Current SQL Server Activity in DPA Free

DPA Free includes real time analysis of SQL Server workload. Rather than fumbling around to find and run scripts, just run DPA free to get a snapshot of the activity in the environment.

Current SQL Server Activity in DPA Free

Figure 3 - DPA Free Current Activity

SQL Server Blocking in DPA Free

SQL Server blocking can be a menace. DPA Free includes insights into blocking in your databases.

SQL Server Blocking in DPA Free

Figure 4 - DPA Free Blocking Metrics

DPA Free Resource Graphs

DPA Free has six sets of resource graphs (CPU, Memory, Disk, Network, Sessions and Waits) to provide a visual representation of the performance metrics over a period of time. These graphs provide value to understand the trends, outliers and are even color coded with yellow and red to serve as warnings and problems. In this situation, the graph paints the picture to understand the key metrics at a high level, then research the issues to begin tuning.

SolarWinds DPA Free Memory Resource Graph

Figure 5 - DPA Free CPU Resource Graph

DPA Free SQL Code to Virtualization Metrics

With the large number of SQL Server instances running on VMWare, SQL Server DBAs have little to no insight into the underlying resources. DPA Free resolves that issue and even correlates SQL code with CPU, Memory, Disk and Network utilization to determine code running during high utilization periods.

DPA Free Virtualization

Figure 6 - DPA Free Virtualization

Next Steps
  • Where can I learn more about DPA Free?

MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of DPA Free.



Last Updated: 2020-10-05


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





download





Recommended Reading

How to find out how much CPU a SQL Server process is really using

Different Ways to Flush or Clear SQL Server Cache

UPDATE Statement Performance in SQL Server

Fastest way to Delete Large Number of Records in SQL Server

SQL Server Query Tuning with Statistics Time and Statistics IO








get free sql tips
agree to terms