Sunday, March 12, 2017

Extending Foxhound, Part 1

Adhoc queries are commonly run against the Foxhound Monitor database for one of two reasons:

  1. to search historical data for performance anomalies that aren't apparent in the standard Foxhound displays, and

  2. to duplicate and enhance Foxhound features.
This article explores the second point by constructing adhoc queries that mimic the Monitor dashboard tab of the Foxhound Menu page:

Here's where the data for the top list comes from, in the screenshot above:

Query 1: Summary List Of Target Databases

Jumping ahead, here's what the custom Summary List Of Target Databases query looks like when it's run in All Programs - Foxhound4 - Tools - Adhoc Query Foxhound Database via ISQL:

The SELECT below uses a WITH clause on lines 1 through 23 to create three local views:
  • v_latest_primary_key finds the latest sample_detail row for each target database; this is a big deal since Foxhound can store many thousands of sample_detail rows for each target database.

  • v_sample_detail makes use of v_latest_primary_key to return all the columns in those latest sample_detail rows.

  • v_active_alerts uses SQL Anywhere's LIST function to gather the alert_number values for all the Alerts that aren't Cancelled or All Clear; the predicate alert_union.alert_is_clear_or_cancelled = 'N' is an prime example of the value added by Foxhound's underlying rroad_alert_union view.

  • Tip: Foxhound doesn't let you create permanent views via CREATE VIEW, but it does let you create temporary views as "common table expressions" in the WITH clause. It also lets you code temporary views as "derived tables" inside the FROM clause, but the WITH clause is sometimes easier to understand and maintain.
   v_latest_primary_key AS 
      ( SELECT sample_detail.sampling_id                           AS sampling_id,
               MAX ( sample_detail.sample_set_number )             AS sample_set_number
          FROM sample_detail
         GROUP BY sample_detail.sampling_id ),
   v_sample_detail AS
      ( SELECT sample_detail.*
          FROM sample_detail
                  INNER JOIN v_latest_primary_key
                          ON v_latest_primary_key.sampling_id       = sample_detail.sampling_id
                         AND v_latest_primary_key.sample_set_number = sample_detail.sample_set_number ),
   v_active_alerts AS
      ( SELECT alert_union.sampling_id                             AS sampling_id,
               LIST ( DISTINCT STRING ( 
                          alert_union.alert_number ),
                      ', '
                      ORDER BY alert_union.alert_number )          AS active_alert_number_list
         FROM alert_union
        WHERE alert_union.record_type                 = 'Alert'
          AND alert_union.alert_is_clear_or_cancelled = 'N'
         GROUP BY alert_union.sampling_id )
SELECT sampling_options.sampling_id                                AS "ID",
       STRING ( 
          IF sampling_options.selected_tab = '1'
             THEN '(DSN)' 
             ELSE '' 
          END IF )                                                 AS "Target Database",
       sampling_options.connection_status_message                  AS "Monitor Status",
       COALESCE ( v_active_alerts.active_alert_number_list, '-' )  AS "Active Alerts",
       IF  sampling_options.sampling_should_be_running = 'Y' 
       AND sampling_options.connection_status_message  = 'Sampling OK'
          THEN rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
                 v_sample_detail.canarian_query_elapsed_msec ) 
          ELSE '-'
       ENDIF                                                       AS "Heartbeat",
       IF COALESCE ( v_sample_detail.UnschReq, 0 ) = 0
          THEN '-'
          ELSE STRING ( v_sample_detail.UnschReq )
       ENDIF                                                       AS "Unsch Req",
       IF COALESCE ( v_sample_detail.ConnCount, 0 ) = 0
          THEN '-'
          ELSE STRING ( v_sample_detail.ConnCount )
       ENDIF                                                       AS "Conns",
       IF COALESCE ( v_sample_detail.total_blocked_connection_count, 0 ) = 0
          THEN '-'
          ELSE STRING ( 
             v_sample_detail.total_blocked_connection_count )
       ENDIF                                                       AS "Blocked",
          WHEN COALESCE ( v_sample_detail.interval_CPU_percent, 0.0 ) = 0.0
             THEN '-'
          ELSE STRING ( ROUND ( 
             v_sample_detail.interval_CPU_percent, 1 ), '%' )
       END                                                         AS "CPU Time"
  FROM sampling_options
          LEFT OUTER JOIN v_sample_detail
                       ON v_sample_detail.sampling_id = sampling_options.sampling_id
          LEFT OUTER JOIN v_active_alerts
                       ON v_active_alerts.sampling_id = sampling_options.sampling_id
 ORDER BY "Target Database", 
The SELECT statement on lines 24 through 64 gathers columns from sampling_options, v_sample_detail and v_active_alerts; in particular:
  • The STRING function call on lines 25 through 30 determines if the sampling_options.selected_name column contains a DSN or a Foxhound connection string name.

  • The IF expression on lines 33 through 38 makes use of the Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms() to display the sample_detail.canarian_query_elapsed_msec column.

Query 2: Active Alerts For Each Target Database

Here's what the ISQL output looks like for the second custom query, the one listing "Active Alerts":

Here's where the data comes from:
  • The ID and Target Database columns come from sampling_id and selected_name in the sampling_options view.

  • The Time Since Alert Recorded column is calculated from the recorded_at column in the alert_union view, using DATEDIFF(), CURRENT TIMESTAMP and the Foxhound function rroad_f_msecs_as_abbreviated_d_h_m_s_ms().

  • The Alert # and Alert Description columns come from alert_number and alert_description in the alert_union view.
SELECT sampling_options.sampling_id                 AS "ID",
       STRING ( 
          IF sampling_options.selected_tab = '1'
             THEN '(DSN)' 
             ELSE '' 
          END IF )                                  AS "Target Database",
       rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
                     CURRENT TIMESTAMP ) )          AS "Time Since Alert Recorded",
       alert_union.alert_number                     AS "Alert #",
       alert_union.alert_description                AS "Alert Description"
  FROM sampling_options
          INNER JOIN alert_union
                  ON alert_union.sampling_id = sampling_options.sampling_id
 WHERE alert_union.record_type                 = 'Alert'
   AND alert_union.alert_is_clear_or_cancelled = 'N'
 ORDER BY "Target Database", 
       "Alert #";

No comments: