Tackle SQL Server Performance Issues for Free with DPA Free
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?
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,
- 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.
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, 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.
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
- 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 protected]
MSSQLTips.com Product Spotlight sponsored by SolarWinds, makers of DPA Free.
Last Updated: 2020-10-05
About the author
View all my tips