How iWatch Works: Capture, Parsing & Analysis

iWatch works completely non-intrusively, and yet it’s able to generate a complete performance record, in real time, of every single SQL transaction. Here, we'll outline how this is possible, what kinds of data we capture, how we keep track of it all.

Non-intrusive capture.

iWatch operates by listening passively to TCP/IP traffic (essentially, it’s a very smart sniffer). The principal benefits of this approach are that it works without the use of polling processes or other intrusive operations that might add overhead to your critical database, and that it generates measurements from the perspective or your application or end user. This also means that you won’t need to make any configuration changes or establish any connections to your database host; nor does iWatch sit in the data path or act as a proxy, where it could become a potential point of failure for a critical application. All you need to know is the type of database you’re monitoring (IBM Db2, IBM Informix, SAP ASE, etc.), and the port number and IP address of your database server, and you can configure iWatch. This type of approach is sometimes described as zero impact – though we prefer the more accurate term non-intrusive. It is possible, of course, to achieve zero impact by placing your iWatch server on a completely different host, and then using a SPAN port or network tap to listen to the traffic. But it’s more likely that you’ll install it directly on the database host, since this approach requires no network configuration changes, and provides the ability to monitor local traffic directly on the host. In the latter case, the overhead you should expect is around 1 or 2 % of a CPU. Not quite zero – but plenty close enough. Even traffic that might not seem conducive to sniffing can be monitored by iWatch. Locally-generated traffic, even SSL or other encrypted traffic (as in the case of MS SQL Server logins) can all be reliably captured and measured.

Parsing & Analysis.

Working in real time, iWatch parses the inbound queries, keeping track of every transaction and session, and then computing a full range of metrics for every SQL transaction. The process begins as soon as the queries arrive, so that even still-running queries can be examined for performance. It’s even possible to trigger alerts & actions based on the age of a SQL transaction while it is still running. For every SQL request, iWatch captures, computes and records the following details (among others):

  • Complete SQL string, including parameters.
  • Identifying details, like Username, Application name, etc.
  • Metrics such as End to end response time, server time, etc.
  • Network data such as round trip time, packet info, etc.
  • Error messages, including number, severity, and message text.

For every SQL, a row is recorded to the iWatch database. In addition, iWatch also summarizes the data, creating a useful record of trends and usage patterns. With the summaries, you can establish baselines, identify the most frequently-running stored procedures, the most heavily-used tables, and much, much more.

Tell me more: request a demo or download now. »