Tackle SQL Server Performance Issues for Free with DPA Free

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


We have SQL Server performance problems that have been plaguing particular on-premises applications with unexplainable slowdowns along with unhappy users and customers. Our environment consists of both physical and virtual machines, 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?


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 Microsoft SQL Server performance issue. Let's dig into these to see which one could make sense for you to best identify the root cause of your performance bottlenecks:

  • Hardware - Generally considered the "quick and dirty option" to patch a short-term problem to avoid downtime. 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 SAN or add resources to a virtual machine
    • 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 Microsoft 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 or perform database performance auditing. 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
  • SQL Server Monitoring Tools - There are a number of monitoring tools on the market to choose from to help identify SQL Server performance issues to correct query performance, disk latency, etc. 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 monitoring solution - Consider your total cost of ownership to build and maintain monitoring software across your enterprise. 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 for performance monitoring tools?

With all of your responsibilities, the severity of your SQL Server performance monitoring issue as you describe it and a mixed environment of physical and virtual machines, 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 SQL queries on your SQL Servers for proper optimization. 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 a free tool focused on wait based analytics to identify performance problems with SQL queries 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 response time issue. DPA Free also includes real time monitoring of your SQL Server database instance.

DPA Free includes the following database performance monitoring features:

  • Top SQL Statements
  • Wait based analytics
  • SQL Server session data
  • Application and User data
  • Locking and Blocking that cause Deadlocks
  • Real time monitoring
  • Server Metrics - CPU, Memory, Disk Space, Network Bandwidth
  • Virtual Machine Metrics
  • Visualization of SQL Code to Hardware Utilization
  • Customizable 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 database monitoring 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 and configure 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. to begin your performance analysis by seeing the wait times which impact application performance.

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 including support for Oracle, SQL Server, Azure SQL Database, Amazon AWS \ RDS and MySQL.

DPA Free SQL Server Performance Dashboard

Many of the interfaces in DPA Free could be considered summary dashboards with prioritized metrics. From these intuitive 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 optimize 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 usage, Memory, Disk, Network, Sessions and Waits) to provide a visual representation of the performance metrics for your IT infrastructure 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

Download Product Trial - SolarWinds DPA

agree to terms

Next Steps

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 Product Trial

agree to terms