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 -- along with a brief look at the tools we provide to help you sift through the data quickly, and pinpoint exactly what you need to know.
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 (Sybase, Oracle, 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, Windows Named Pipes , even SSL or other encrypted traffic (as in the case of SQL Server 2005 logins) can all be reliably captured and measured.
Captured data is written to shared memory or tempfs, where it can be parsed and analyzed by the iWatch pserver process.
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, including:
- Client IP and Port #
- Login name
- User name
- Application Name
- Process ID (SPID or PID)
- Database name
- Session level details
- Metrics including:
- End to end response time
- Server Reaction time
- Maximum reaction time
- Total Server time
- Network data, including
- Network round trip time (for that particular query)
- CRC errors
- Runt/oversized packets
- 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 in fifteen-minute intervals, 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, flag multiple joins, and much, much more.