What is query-level performance monitoring?

Overview

SQL query-level monitoring is non-intrusive approach to performance monitoring, designed to eliminate guesswork – without adding overhead to your critical applications.

As a query-level performance monitor, iWatch operates differently, and solves different classes of problems than traditional OS-level or database-level tools.

To understand why this is important, consider what happens in a typical distributed environment – and what happens when we try to use those traditional tools to diagnose the problem:

Three levels of performance monitoring:
Query-level -- SQL Performance from end-user perspective;
Database-level -- Database internal statistics;
OS-level -- Server or file system status.
  • An application residing on one physical host…
  • Sends a request from a user using Structured Query Language (SQL)…
  • Across a network…
  • To a database sitting on another physical host…
  • That’s simultaneously processing requests from many other users…

A problem with any one of these things, or any combination of these things, might affect how long it takes to get an answer back.

But which is it?

When the process is delayed, the first challenge is to identify and isolate the problem correctly. Is the problem with the SQL code? Is it the network? Is it a delay on the server side? Is it a side-effect of some other process running at the same time, such as an ad hoc query?

The right answer, early, can save hours or days of guesswork.

These are exactly the kinds of questions that iWatch answers with unrivalled efficiency – and where traditional tools all too often fall short.

OS level monitors.

The first type of monitoring that most organizations implement is an OS-level (or server-level) monitor. These tools will deliver crucial information regarding the health of your underlying infrastructure (file system status, network I/O, etc) – but typically do not extend upward to help with database or application issues. They have no way of knowing if your network is misbehaving, or if your SQL is badly written, or even if a table is getting slammed on the database on the very same machine where they operate.

Database-level monitors.

The next category of tools that are commonly implemented are database-level monitors. This is a broad category, which includes vendor tools and a number of third-party options, but they will generally have several attributes in common: They rely on intrusive polling processes to obtain their data, and focus on internal statistics of the database itself (e.g., blocked processes, cache hit ratio, etc).

The most common drawbacks of these tools are substantial overhead, combined with a perspective that can be difficult to correlate to end user experience. Overhead can easily exceed 10 or 15% of a CPU, and will go higher as users request more detailed data. The consequence is that they can undermine the performance of the systems that they are intended to improve…making them impractical for use in many production environments.

As with the OS-level tools, the database-level tools have limited effectiveness if you need to identify poor SQL, or sort out a network issue from random ad hoc queries – assuming you can run them at all.

iWatch query-level performance monitoring.

By taking a non-intrusive approach, iWatch cannot harm the performance of your critical applications. And by measuring at the SQL-level, iWatch delivers metrics that map directly to your end users’ experience – ensuring that optimization efforts are focused exactly where they are most needed. Guesswork is eliminated, overall satisfaction improves, and costly disruptions can be averted.

AttachmentSize
pyramid.png53.9 KB