Product Information

DBpulse: is a utility that records SQL query information by sniffing network packets. It captures all packets going into and coming out of the database server. It de-multiplexes this traffic and records round trip activity for each query sent to the database. The result of all this work is written to a couple of output files (RATING and THRESH). The RATING files are used for benchmarking purposes and the THRESH files are used for audit and diagnostic purposes.

The default behavior of DBpulse is to write all queries to the THRESH file. However, using the proper options, DBpulse can be made to only output long running queries (e.g. those running longer than 30.0 seconds). This will eliminate the frustration of sifting through thousands of queries to find the problematic ones. By letting the software do the tedious work, a tuning specialist can concentrate on query optimization methods rather than problem identification.

In addition to diagnosing problem queries, DBpulse can be set to collect "Rating" information. Rating information is a subset of audit information, however, it records all queries for a specific interval (default: 15 minutes) and outputs that information to the RATING file. For each new interval a RATING file is created. The contents of the RATING files are used to calculate "Rating" and "Load" metrics. These metrics can be compared across time periods to determine if the server is performing better or worse.

Foresight uses the "Rating" and "Load" metrics in its tuning engagements to determine progress. The methodology is as follows:

  1. Establish the baseline "Rating" and "Load" values before tuning
  2. Set the long-running query threshold and find the bad queries
  3. Tune a couple of the bad queries.
  4. Re-calculate the "Rating" and "Load" values
  5. If the values are acceptable, stop. Otherwise, reduce the threshold and go back to Step 2.

DBpulse reports are are offered as part of Foresight's performance management solution. Currently, there are three (3) canned reports. Custom reports and audit tools are available as options.

The canned reports are as follows:

Top N Worst Query
Scans the THRESH files to find the top N longest running queries. It displays the query along with other related information.
DB Rating and Load
Computes the rating and load of a database and sorts the query elapsed times into 20 categories which are displayed as a histogram.
Login Count
Displays the number of good and bad logins per client IP address, application name, and user name.

The sampling frequency is 15 minutes. All reports are summarized at these intervals:

One hour
Four hours
One day
One week
One month

The canned reports are available 24x7 via the Web. In addition, weekly reports are e-mailed to the user. The retention level for monthly data is 36 months and weekly data is 52 weeks. The other data is archived more quickly. But, this retention level should be sufficient for most users.

DBpulse can be used to benchmark performance, diagnose problems, and audit access to the DBMS. In addition, DBpulse has a small footprint and uses few resources on the server. When there is no traffic to the SQL Server, it simply waits. When there is traffic, it generally uses less than 5% of the CPU to accomplish its work. DBpulse is easy to install and can be running in less than 15 minutes. On most systems it doesn't even require a reboot.