SQL Server Monitoring Two Ways

SolarWinds customers and prospects have asked (be it at trade shows, phone calls, demos, etc.) what the difference is between SolarWinds products Server & Application Monitor (SAM)  and Database Performance Analyzer (DPA) and if they can co-exist with one another. Although both products monitor SQL Server, their purpose and persona are unique and different. Each has its own benefits in serving various database support roles, but at the end of the day, you’re monitoring two sides of the same coin.

Many of you already enjoy using SAM’s AppInsight for SQL monitoring template, which provides a systems-focus view into SQL Server. Using the template, you can view who’s connected and for how long, be notified of database and system storage issues, dig into the SQL error logs, and numerous other SQL Server, Windows, and hardware/hypervisor counters and performance metrics. SAM also goes beyond pure system monitoring by answering “What queries are slow?” but what if you need to continue troubleshooting deeper into the query performance? Is this performance normal? Why is it running slow? Is something causing blocking? Is the user (most important success metric), application, or client experiencing performance issues elsewhere? What is ultimately causing this issue? DPA can step in and help answer those questions.

DPA helps pinpoint the most significant database performance issues by using response time analysis analyzing the time applications, and ultimately the end user, wait for data to return from the database. DPA breaks down response time into distinct wait types and allows you to identify the exact cause of the slowdown, down to a specific query from a specific source in a particular slice of time (down to the second if you want to get nitty-gritty). This information provides actionable insights into why your queries are slow and how best to resolve them.

Here are a couple of examples of how I like to think of it. Suppose you and buddy/friend/significant other head over to Target, and you drop them off at the front door to buy one item, and it’ll be one minute (who hasn’t been there). You used Google Maps to confirm they’re open, you pull into the parking lot and see the lights on, the parking lot is semi-full, and the front door is open. From your point of view, the store is open and running (similar to how SAM sees a SQL Server.) On the other hand, your buddy is inside the store, and maybe the item is at the far end of the store and not upfront. Maybe when they get there, it’s misplaced and not on the shelf. When they check out, someone pulls out a checkbook and takes forever to write it, etc. From the outside, you’re left wondering what’s taking so long, but on the inside, you see all the details and reasons why it’s taking so long.

For those familiar with our networking products, you can think of it as a difference between network performance monitoring like Network Performance Monitor (NPM)  and flow analysis like NetFlow Traffic Analyzer (NTA) —NPM tells you which ports/interfaces are slow; NTA tells you who and why. Without NTA, you’re left either guessing or pulling up desktop tools to diagnose the packets manually. Without DPA, you lack the same visibility into your queries—so break out a cup of coffee for a late night of one-off query analysis.

So you may be asking yourself, which product or side of the coin do I need? If I already have SAM, isn’t that good enough? Databases are the backbone of modern applications, and slow performance affects your business, costing you money and creating end-user frustration. In a converging infrastructure and hybrid environments, slowness can come in many forms: under-allocated resources, misconfiguration, resource contention, poorly constructed queries, poor indexing strategies, and bad query plans, to name a few. Without visibility to both sides, you won’t have a complete analysis of the problems you’re working on every day.

Here are a few use cases of where SAM and DPA can work together:

SAM and DPA complement each other by providing two views of the database. Some views and metrics are used more frequently by system administrators, and others are used more by DBAs (or those system administrators who find themselves responsible for the database) and developers. The combination of the two provides the broadest and most complete monitoring solution.

SAM’s AppInsight for SQL monitoring ·SQL Server infrastructure monitoring ·Visualize database, application, operating system, and hardware dependencies ·Quickly visualize database status, capacity, logs, and job status ·Monitor the entire application stack from virtualization to storage ·Correlate between virtualization (VMware and Hyper-V) and server resource provisioning ·Identify top slow SQL queries Database Performance Analyzer ·Quickly identify performance anomalies based on past query response time behavior ·Identify which queries are driving inefficient workload and if indexing can improve performance for SQL Server and Oracle ·Database response time analysis for SQL Server, Oracle, MySQL, Postgres, DB2, and Sybase ·Identifies top issues causing slow response times in typically four clicks ·Detailed wait type analysis of SQL queries · Correlates with VMware resource metrics (OS, physical host, and storage disk devices) ·Integrates with the Orion® Platform and allows deeper correlation with other Orion Platform modules ·Analyze performance from the perspective of programs, sessions, databases, and more.

There has been significant development in the last couple of years to help blend the two products, adding use cases and helping simplify the troubleshooting process.

If you own just DPA (without any Orion Platform products), you can now download a standalone DPA Integration Module (DPAIM) from your customer portal as part of your existing license. This gives you the power, usability, and interface of the Orion Platform, and it’s completely free. However, you’ll be limited to DPA data only (anomaly detection, total wait time, wait type, SQL statements, etc.) since no other modules are running to collect application, server, storage, network data, etc.

If you use SAM or any other Orion Platform product and are on the latest release, DPAIM may be installed (it comes with Server & Application Monitor, for example), and see DPA data in features such as AppStack and PerfStack. Using AppStack, you can quickly view a database instance and its related software and hardware dependencies. See everything from what applications are connected to the underlying storage. With PerfStack, you can create custom metric views to help isolate and correlate performance data. Stack network availability and performance, on top of system and application performance on top of database waits to see where the real problem is (it’s always the network .)

Putting SAM and DPA together, you’ll see both sides of the SQL Server coin... and this will only get better as DPA moves forward.