Tackle SQL Server Performance Issues for Free with DPA Free
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,
- 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.
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.
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.
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.
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.
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.
Figure 6 - DPA Free Virtualization
Download Product Trial - SolarWinds DPA
- Where can I learn more about DPA Free?
- Download DPA Free
- DPA Free Home Page
- For any additional product questions, contact SolarWinds Sales @ +1-866-530-8100 or email@example.com
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of DPA Free.
Last Updated: 2020-10-05
About the author
View all my tips