SQL Capture

  • /
  • SQL Capture

SQL Capture

Overview

ProActive DBA SQL Capture provides continuous network-based monitoring of all SQL executed against your database server.

Major benefits include:

  • Captures all SQL sent to the database server
  • Network-based capturing does not use polling or tracing, incurs no overhead in the database server
  • Powerful reporting summarizes and analyzes captured SQL
  • Monitors exact end-user response times down to the millisecond
  • Alerts on long-running SQL and end-user response times
  • Analyze SQL to identify potential table scans
  • Login-based reporting

SQL Capture is available for SAP Sybase ASE, SAP Sybase IQ, SAP Sybase Replication Server, Microsoft SQL Server and PostgreSQL.

Features

SQL Capture monitors and records all the SQL queries sent by client applications to your database server, and displays and analyzes those queries in a powerful GUI.
sql_capture
You can sort, filter and group SQL queries by user, application, SQL statement type, execution time and other criteria.

SQL Capture provides summary reports that identify the top-N users, client host names, standardized SQL queries, and more.

Unlike polling-based monitors that incur a lot of overhead and only capture a small percentage of the SQL running on your server, SQL Capture uses no-impact network-layer sniffing to capture all the SQL sent to your databases, down to the tiniest sub-millisecond SQL executions.

Thus, SQL Capture shows you the actual response times experienced by your end-users, which provides the most accurate and most relevant measure of your database’s performance.

Technology

SQL Capture uses network-layer sniffing to monitor SQL queries sent to your database server locally or over the network. This provides continuous coverage of database server activity.

Other SQL monitors work by querying the database server itself in order to monitor its SQL activity. This can add significant overhead to the server, since these methods require the database server to do extra work to store and account for every SQL executed against it.

These products can also use significant additional memory in the database server, due to the “SQL pipes” or memory buffers that are needed to store the SQL. And, the database engine has to do extra work to populate these SQL buffers internally, so the extra overhead incurred is “invisible” and can be difficult to measure.

Besides incurring overhead on the server, the polling methods these other products use may not capture all of the SQL executed against the server, may not capture the full SQL text with parameters for every statement, may not capture the actual SQL text and may not obtain complete performance metrics and timing information for each SQL execution.

For example, here is a comparison showing SQL obtained from system tables (in this case, the SAP Sybase ASE MDA tables) for a dynamic SQL execution:

    DYNAMIC_SQL dyn_id:

In contrast, here is what SQL Capture shows for this statement — the full SQL text including parameters:

    select name from master.dbo.sysdatabases
    where name = 'usertemp' OR dbid = 2

Some other competitors might use “SQL trace” capabilities in the database server. While these methods may be able to capture most or all SQL, they add additional overhead and typically do not provide very much data regarding each SQL statement that is executed, and in some cases they report too much detail (e.g. listing every fetch operation for a cursor-based query).

Here is an example of such a SQL statement captured on a SQL Server instance, by using its SQL tracing capabilities:

declare @p1 int
set @p1=180150075
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'select * from revenue_forecast',
         @p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
exec sp_cursorfetch 180150075,32,1,1
exec sp_cursorfetch 180150075,32,1,1
exec sp_cursorfetch 180150075,32,1,1
etc...

SQL Capture simply reports a single entry for this SQL, along with the row count and other relevant performance metrics:

select * from revenue_forecast

SQL Capture is able to collect and save every SQL sent to the database server, no matter how large or small, and includes all the parameters sent along with the SQL for query types such as JDBC prepared statements, cursor executions and RPCs (stored procedure executions). It reports multi-step queries such as cursor-based SQL as a single execution with combined performance metrics for the whole batch.

And, SQL Capture calculates SQL execution times with microsecond precision, and gives a breakdown of time spent in the client vs. in the server and sending and receiving over the network!

Agent vs. Agentless Architecture

Some other SQL monitors only offer an “agent-less” architecture and tout it as an effective means for monitoring the SQL executed on your server. However, these monitors usually rely on querying the database server to monitor the SQL executed on it, which is inefficient, incurs overhead on the database server, and can be rendered useless by network outages.

SQL Capture offers both agent-based and agentless solutions, both of which capture SQL completely outside the database server.

ProActive DBA’s ultra-lightweight agent runs on the busiest servers at many of the largest enterprises in the world, and is able to non-intrusively capture all the SQL sent to the server. Our monitoring is fault-tolerant and can continue monitoring through a network outage or even a reboot of the database server itself! Our customers often describe it as the “last man standing.”

So it’s no wonder why so many organizations all over the world rely on SQL Capture for round-the-clock monitoring of their servers.

Database Support

SQL Capture supports the database types and versions listed below.

SAP Sybase ASE

SQL Capture supports SAP Sybase ASE and ASE CE versions from 11.0 through 16.0. It monitors all types of SQL requests sent to ASE, including direct SQL, prepared / parameterized SQL, cursor-based SQL and remote procedure executions (RPCs).

SQL Capture can monitor connections and SQL encrypted with SSL/TLS on Sybase ASE 16.0 servers on the Linux x64 platform.

SAP Sybase IQ

SQL Capture supports SAP Sybase IQ 12.6 through 16.1, and captures all types of requests on SAP Sybase IQ servers, from all client types including Open Client, native iAnywhere clients, ODBC, JDBC / jConnect, etc.

SQL Capture can monitor connections and SQL that are encrypted via SSL/TLS on Sybase IQ versions 16.0 SP02 through 16.1 on the Linux x64 platform.

SAP Sybase Replication Server

SQL Capture supports SAP Replication Server 12.5 through 15.7.1, and can monitor administrative (RCL) commands sent to Replication Server, as well as capturing incoming replicate data (LTL) sent by Rep Agent connections to the Replication Server.

Microsoft SQL Server

SQL Capture monitors Microsoft SQL Server 6.5 through 2022 on Windows, and SQL Server 2022 on Linux x64.

It captures full login and SQL transaction details on local and remote logins, including SQL-authenticated and Windows-authenticated (trusted) logins, from clients using JDBC, ODBC, OLE DB, SQL Native Client, etc.

SQL Capture is capable of capturing encrypted connections and SQL on SQL Server 2005 through 2022 on Windows, and SQL Server 2022 on Linux x64.

PostgreSQL

SQL Capture supports capturing of SQL traffic on PostgreSQL server versions 8.0 through 13.

Integration

SQL Capture integrates with other ProActive DBA products to provide a total monitoring solution for your database infrastructure. The sections below describe how SQL Capture works together with the other products and options in the ProActive DBA suite.

TimeLine

Using SQL Capture together with TimeLine provides a powerful solution for diagnosing current and historical performance issues on your servers. TimeLine lets you easily navigate to a point in time you’re interested in, and it shows you the SQL statements that were running at that point in time, as captured by SQL Capture.

You can also step forward or backward in time, to see how a performance problem developed and examine it in different dimensions. For example, you can see:

  • Performance levels (CPU, logical and physical I/O, network activity, operating system activity, etc.)
  • Database process state (were other processes in the database running, blocking, waiting on resources, sending or receiving network data, etc.)
  • User-defined events (long-running SQL, deadlocks, server error log notifications)

SQL Capture Alerting Option

The SQL Capture Alerting Option adds customizable SQL-based alerting capabilities to SQL Capture.

It lets you trigger alerts on SQL executions that meet the criteria you define, such as long-running SQL, deadlocked SQL or SQL that returned a large number of result rows. You can filter on SQL keywords, error codes and more.

When a SQL statement matching your criteria is found, you can trigger customizable actions, such as executing a SQL or O/S script or sending an e-mail notification.

View SQL-based alert events using the TimeLine graphical viewer, which gives you an integrated display of alerts, SQL executions, and user script output at any point in time you select.

When used together with the TimeLine Alerting Option, you can also view database performance graphs, locking/blocking details and user process details as they were gathered at the time of a SQL execution alert.

SQL Capture Login Monitoring Option

When you use the SQL Capture Login Monitoring Option with SQL Capture, you gain navigational tools that let you jump between logins and the SQL they executed, and vice-versa.

This is a powerful investigational tool, making it easy to search, sort and locate SQL and login events of interest.

SQL Capture Table Scan Analyzer Option

With the SQL Capture Table Scan Analyzer Option (available for SAP Sybase ASE and Microsoft SQL Server), you can analyze captured SQL statements to estimate if they would perform any table scans when executed.

This capability lets you identify SQL that would perform table scans, and it also enables you to test modifications to your SQL or schema, in order to ensure that table scans will not occur when they shouldn’t.

SQL Capture Warehouse Option

The SQL Capture Warehouse Option enables you to process captured SQL statements into the ProActive DBA Warehouse (PDW).

This gives you a wide array of options for analyzing and reporting on application SQL usage over long periods of time.

For example, you can answer questions such as:

  • Which SQL statements referenced a certain set of tables in a database this year, and when was the last reference made by each one?
  • Who are the top 10 database users by number of SQL executions, for the last month?
  • Which columns in a set of tables were not referenced by any SQL in the last 30 days?
  • On which dates last month were any databases created or dropped, and what was the application and SQL that did it?
  • Which frequently-executed SQL statements are performing poorly today compared to last month?
  • How does the performance of frequently-executed SQL statements compare on older vs. newer database server versions?
  • Define and compare SQL workloads between different date ranges, server versions and server configurations. Identifies SQL or stored procedures whose performance will improve or degrade.

Product Editions

SQL Capture is offered in two feature editions:

  • SQL Capture – this edition includes the base product.
  • SQL Capture Plus – this product includes the base product plus all SQL Capture options except the SQL Capture Warehouse Option.

The table below lists the features included in each product edition.

Feature / Option  SQL Capture   SQL Capture Plus   Sybase ASE   Sybase IQ   Sybase Rep Server   MSSQL   PostgreSQL 
Basic capturing of SQL and login activity Yes Yes Yes Yes Yes Yes Yes
SQL Capture Alerting Option – Yes Yes Yes Yes Yes Yes
SQL Capture Login Monitoring Option – Yes Yes Yes Yes Yes Yes
SQL Capture Table Scan Analyzer Option – Yes Yes – – Yes –
SQL Capture Warehouse Option – – Yes Yes Yes Yes –