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. 

 

betabuttonWithNoVer.png

 

In this 3 part series, I'm going to tell you about :

 

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.

 

FeaturesDisplay

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 :

  1. Click the "Settings" link in the upper right hand of Orion.
  2. Click the "DPA Settings" link in the "Product Specific Settings" section.
  3. Click "Set Up Integration" to begin.

wizard-1.JPG


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 :

  • Server name or IP address of your DPA repository
  • Port number of DPA's SSL URL i.e. HTTPS://my.domain.local:8124
  • User name of an administrative account
  • Password


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.


wizard0.JPG

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.


  • Take a moment to review the mappings DPA and SAM found.
  • As long as SAM and DPA know your database instances by the same IP address, you can just click next.
    • Possible Exceptions where SAM and DPA know a database instance by different IP Addresses:
      • DPA or SAM knows a database instance by a Cluster VIP and the other knows that instance by individual node IPs.
      • DPA has registered an Availability Group listener and SAM has registered individual nodes.
      • SAM and DPA are using DNS aliases that each have different IP addresses for the same database instance.
      • A database instance is using DHCP and both SAM and DPA have different DNS servers resolving to different IPs.


  • If you wish to exclude one of DPAs monitored instances, un-check it.

  • When a link exists, the status column will show a chain link.ChainLinkIcon.JPG
  • When a link does not exist, the status column will show a slashed circle.SlashedCircleIcon.JPG
  • If you need to add a mapping, click the add link icon in the actions column.AddLinkIcon.JPG
  • If you need to edit a mapping, click the pencil icon in the actions column.PencilIcon.JPG
  • If you need to remove a mapping, click remove link icon in the actions column.RemoveLinkIcon.JPG
  • Click Next when you are happy with the configuration.
wizard1.JPG

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.  :-)


  • Take a moment to review the mappings DPA and SAM found.
  • This should automagically discover most use cases.  But possible exceptions include:
    • Your applications don't connect directly to the database instance but rather proxies through something else like a middle tier service.  In this case, DPA knows the proxy or middle tier service as the client of the database.
    • Your applications use DHCP and SAM and DPA are resolving names through different DNS servers.

  • Since you can have multiple applications on a host, you can have multiple rows for a host.

  • Un-check any applications that you don't want a DPA resource added to.

  • If you need to edit a mapping, click the pencil icon in the actions column.PencilIcon.JPG
  • If you want to add an application view to SAM, click the Add Application Views button. AddApplicationViewsButton.JPG
  • After you click Next, you will see a confirmation screen with a "Finish" button.


wizard2-1.JPG

 


So now you're ready to find needles in the haystacks!  This is where "the magic" happens.

 

FeaturesDisplay

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.

 

  • Advisors : The Instance View has a version of the Advisors resource similar to the one on the Summary View.  However, this one targets the instance as opposed to aggregating several like the one on the Summary View.  We discussed Advisors in my last post.  This one is similar but provides advisors for this instance only.

  • The Spark Line resources are intuitive.  You may be familiar with them from other products like Server Application Monitor (SAM) or Virtualization Manager.  These spark lines show resource metrics obtained by DPA.  Notice that while many of the metrics appear similar as ones you can get in SAM, they are usually not the same.  For the few are also collected by SAM, DPA polls more frequently, often once a minute as opposed to every 5.  So this data is more granular and helps you find needles in the haystack.  For example, your storage admins may typically only review metrics polled at 5 or 10 minute intervals.  Guess what, they won't see needles in the haystack like DPA does.  This is so important for sysadmins responsible for database performance because a 30 second disruption might be devastating to end users and be washed out in a 5 minute poll.
AppInsightAndDpaInstance.png

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:

  • By Individual SQL queries
  • SQL Queries Aggregated by Wait Types
  • SQL Queries Aggregated by Applications


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)

  • What the wait type means
  • Who typically resolves this wait type
  • Options for reducing waits of this type

 

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:

  • If your most significant wait types are related to storage sub-system performance, don't jump to the conclusion that you need faster storage.  Optimizing queries for storage may resolve performance issues. Or maybe a single database file needs lower latency, but the rest of the files are OK.
  • If a single application is suffering but other applications using the same database instance are not, then maybe your application's database needs special attention, not the whole instance.
  • Maybe a single query is causing grief to everyone but it's application is happy and thus off your radar.  For example, maybe someone has used Excel to query your database instance and allowing it to dominate database resources for an hour.


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.

pivot1-1.png

pivot2-1.png

waittypeinfo2.JPG

pivot3-1.png

pivot4-1.png

HistoricalChart.png

 

Stay tuned!  My favorite features are coming up in the next post.

betabuttonWithNoVer.png