In my last post, I told you about some new features that provide a dashboard style summary view of your database environment when you integrate Database Performance Analyzer (DPA) with Server Application Monitor (SAM). I also explained a little bit about database wait time. I'll expand upon that in this post as I tell you about ways DPA can help you inspect your database instance for performance bottlenecks. Please note that we have also just made DPA 9.1 Release Candidate available for download. As you can see, we've been very busy at Solarwinds with back to back releases running in parallel development! I hope you like what I'm about to show you and invite you to consider joining The Beta which is open to customers with both DPA and SAM currently on active maintenance. To try it out, please fill out this short survey.
In this 3 part series, I'm going to tell you about :
- Part 1 - Adding a Databases tab to Orion!
- Part 2 - Database Performance : Finding Needles In Haystacks!
- Part 3 - Database Application Performance Monitoring & End User Experience!
NOTICE : This is BETA, so there is no promise that what you see here will be delivered as is.
In the first part of this post I'll describe the integration process. It's designed to be very automagic but provides flexibility to tweak a configuration to meet the needs of more complex environments. After you've completed the Integration Wizard, Orion implements your integration as follows :
- A Databases tab is added to the top row of tabs.
- A Summary View is populated with SAM health and DPA performance data.
- Instance Views are set up for database instances monitored by only DPA.
- Instance Sub-Views are set up for database instances monitored by both DPA and SAM.
- A special DPA resource is added to application views that monitor database clients.*I'll talk about this more in the next post.
Getting Started: The Integration Wizard is designed to streamline the integration of DPA with Orion. The process of integrating DPA to Orion begins on the DPA Settings screen. *NOTE* that these instructions are assuming you've installed and configured both DPA and SAM to monitor your database instances. Once that is done, to start the integration :
Turning on Integration : For the most part, to integrate, you tell Orion where DPA is, how to log in, and then your job is mostly confirming the mappings we'll suggest but you will have opportunities to manually tweak these mappings.
To turn on integration you need the following information :
Once done, you can test the connection then submit and start the wizard. When you submit, DPA and SAM will compare notes and present you with the first mapping screen.
Mapping Database Instances : In order to attach DPA functionality to all the right nodes in Orion, during the configuration of DPA integration, we ask you to confirm that we have mapped the right nodes in Orion to the database instances registered in DPA. This enables us to add functionality to database instances you're already monitoring by adding a sub-view. If you aren't already monitoring that database instance, the wizard will show no relationship for that database instance and upon completion of the wizard, the same DPA information will be presented in a default view for that database instance. You can see the sub-view and it's benefits below, where I describe The Database Instance View.
Mapping Database Clients : The next step of configuration is to confirm that we have accurately mapped database clients per DPA to the applications you monitor in SAM. We do this by mapping the IP of the database clients DPA sees to the IP addresses of nodes where your applications reside. How do we do this? Well when an application connects to a database, the database obviously knows which host just connected and DPA keeps track of which queries came from that host. It is this database client mapping that enables the 2 powerful features I'll talk about in my next post! Wink, wink... It's pretty cool so check back for my third post. :-)
So now you're ready to find needles in the haystacks! This is where "the magic" happens.
The Database Instance View provides greater detail of what your database is doing and how that's affecting your resources. If you use AppInsight for SQL also, DPA creates a sub-view so it's easy to use both tools to analyze the database instance.
Pivoting Wait Time is one of my favorite troubleshooting techniques. When you click into a Database Instance, you will see a more detailed and powerful analysis of wait time in the Database Response Time resource.
This resource provides multidimensional performance analysis:
This is useful because sometimes the root cause is an individual query and sometimes it's several queries from an application or a resource constraint affecting all.
In the first pivot (1st image) of wait time, I can see that there is no one SQL that is the cause of most of the wait time because the queries with the most wait time are very small relative to all remaining queries.
In the second pivot (2nd image), I've pivoted by wait type. I can see that Memory and CPU are the most significant wait type. That means real work is happening as opposed to waiting on storage or locks. Now you may not know all the hundreds of wait types you'll see, but DPA does.
When it comes to wait types, click on the (2nd image) and DPA tells you (3rd image)
In the third pivot (4th image), I've pivoted by application and see that "Ignite" is the application with the most significant waits and ".NET SQLClient Data Provider" is a distant second. Note that ".NET SQLClient Data Provider" is the default name you will see if your .NET developer did not specify an application name in the database connection string.
Now since I want to find something to tune, I pivot back to SQL and exclude "Remaining queries" by un-checking it (5th image). Now I know the 5 queries causing me the most wait time and that is something we can work on. Of course none of these examples show a lot of wait time. I'm only doing this to demonstrate how I would use this resource. What if you wanted to know more about a query? Click on it and you will see a nice report analyzing historical performance of this one query (6th image).
So with wait time analysis, AKA response time analysis, you can find needles in the haystack:
And just to reiterate, you don't need to know what all of those wait types mean. When you don't know, click on the icon and DPA will tell you what it means, what can be done about it and who would typically do that. This is so huge, I had to say it twice. :-)
And because wait time analysis may be new to you, here's some information on the technique.
Stay tuned! My favorite features are coming up in the next post.