Help for Foxhound 4.0.4740a

Table of Contents     [RisingRoad]


Foxhound 4 

8. Adhoc Queries

8.1 Frequently Asked Questions

8.2 Adhoc Views

8.3 Adhoc Functions

8.4 Examples of Adhoc Queries


Foxhound 4 8. Adhoc Queries 
8.1 Frequently Asked Questions


Foxhound 4 8. Adhoc Queries 8.1 Frequently Asked Questions 
How do I run adhoc queries on the Foxhound database?
Foxhound comes with this shortcut to run adhoc queries:
All Programs 
   - Foxhound4 
      - Tools 
         - Adhoc Query Foxhound Database via ISQL

Here's what the ISQL command line looks like:

"%SQLANY16%\bin64\dbisql.com" -c "ENG=foxhound4; DBN=f; UID=ADHOC; PWD=SQL; CON=Foxhound4-ADHOC" 

Here's how to change the password:

GRANT CONNECT TO ADHOC IDENTIFIED BY 'JqDCt64Kfy73';


Foxhound 4 8. Adhoc Queries 8.1 Frequently Asked Questions 
How do I see the schema for adhoc reporting?
A separate adhoc schema database is delivered with Foxhound, containing all the views and underlying tables that are available for adhoc reporting.

To see the adhoc schema, use the String tab of the Foxhound menu page to select this connection string

Foxhound 4 Adhoc Schema - autostart and connect

then click on the Display Schema button.

Note: You can't run queries on the Adhoc Schema database, it's just there to show you what the schema looks like.


Foxhound 4 8. Adhoc Queries 
8.2 Adhoc Views
You have to use views to write your adhoc queries, not the base tables.

In other words, the base tables like rroad_group_1_property_pivot are shown in the adhoc schema so you can see the foreign keys and indexes, but only the corresponding views like sample_detail can be used in your queries:

alert  One row per alert.
alert_cancelled  One row per alert cancellation.
alert_union  A UNION of all the rows in the alert, alert_cancelled, all_clear and sampling_options views to make reporting easier.
alerts_criteria  One row containing the Monitor Options page settings for each sampling session, plus rows for 'Factory Settings' and 'Saved Defaults'.
all_clear  One row per alert all-clear.
build_number  A single-row table containing some attributes of the current installation of the Foxhound database.
connection_string  One row for each connection string displayed on the String tab of the Foxhound main menu.
data_upgrade_history  One row for each time the data in an existing Foxhound database has been copied into a new Foxhound database as part of the installation process.
email_failure  One row for each time Foxhound failed in an attempt to send an Alert or other email.
exception_diagnostic  One row for each time Foxhound detected an error or other important event. In some cases, a single underlying error may result in two or more rows in exception_diagnostic recorded by nested exception handlers in Foxhound.
exception_dump  One row for each time Foxhound stored extra internal diagnostic information associated with an event recorded in exception_diagnostic.
expiry_date  A single-row table containing some attributes of the current installation of the Foxhound database.
global_options  A single-row table containing some options affecting how Foxhound behaves.
monitor_sampler_control  A single-row table containing some information about current execution of the Foxhound database.
peaks  One row for each Foxhound Monitor target database, holding various peak values and links to the corresponding samples.
purge_run  One row for each run of the Foxhound database purge process, holding information about the progress of the current run and the work accomplished by previous runs.
run_characteristics  A single-row table containing some information about current execution of the Foxhound database.
sample_detail  One row for each sample recorded by the Foxhound Database Monitor, holding various additional server and database-level properties and computed columns.
sample_connection  One row for each connection recorded for each Foxhound Database Monitor sample, holding various connection-level properties and computed columns.
sample_header  One row for each sample recorded by the Foxhound Database Monitor, holding various server and database-level properties and computed columns.
sampling_options  One row for each Foxhound Monitor target database, holding various options affecting how the Monitor behaves and information about the current status of the Monitor session.
serial_number  A single-row table containing the Foxhound serial number as shown on the About page.
session_options  One row for each HTTP session established during the current execution of the Foxhound database.
used_activation_key  One row for each different key used in the Foxhound activation process.


Foxhound 4 8. Adhoc Queries 
8.3 Adhoc Functions

Foxhound uses a variety of SQL functions to reformat performance statistics for readability, and some of those functions are available for use in adhoc queries.

Some of these functions are used in the example Compute the "Totals:" line removed from the Monitor and History pages.

CREATE FUNCTION rroad_f_bytes_as_kmg

CREATE FUNCTION rroad_f_cpu_percentage_string

CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms

CREATE FUNCTION rroad_f_number_with_commas

CREATE FUNCTION rroad_f_decimal_with_commas


Foxhound 4 8. Adhoc Queries 8.3 Adhoc Functions 
CREATE FUNCTION rroad_f_bytes_as_kmg
The rroad_f_bytes_as_kmg function reduces large byte values to more readable multiples of k, M, G and T.

Foxhound uses this function for many displayed values like Throughput... Bytes

CREATE FUNCTION rroad_f_bytes_as_kmg
   ( IN @p_bytes        UNSIGNED BIGINT,
     IN @rounding_digit INTEGER DEFAULT 1 ) -- this should be either 0 or 1
   RETURNS VARCHAR ( 200 )

rroad_f_bytes_as_kmg ( 9223372036854775807 )   8,388,608T                                    
rroad_f_bytes_as_kmg ( 1057505280 )            1,009M                                        
rroad_f_bytes_as_kmg ( 1024000 )               1,000k                                        
rroad_f_bytes_as_kmg ( 1048576000 )            1,000M                                        
rroad_f_bytes_as_kmg ( 1073741824000 )         1,000G                                        
rroad_f_bytes_as_kmg ( 1099511627776000 )      1,000T                                        
rroad_f_bytes_as_kmg ( 1124100 )               1.1M                                          
rroad_f_bytes_as_kmg ( 1148678400 )            1.1G                                          
rroad_f_bytes_as_kmg ( 1173846681600 )         1.1T                                          
rroad_f_bytes_as_kmg ( 1124100, 0 )            1M                                            
rroad_f_bytes_as_kmg ( 1148678400, 0 )         1G                                            
rroad_f_bytes_as_kmg ( 1173846681600, 0 )      1T       


Foxhound 4 8. Adhoc Queries 8.3 Adhoc Functions 
CREATE FUNCTION rroad_f_cpu_percentage_string
The rroad_f_cpu_percentage_string function calculates the readable CPU usage percentage in a given time interval, taking into account the number of CPUs being used. It also limits wildly out-of-bounds values to 100%, and allows for zero values to be represented as a dash.

Foxhound uses this function for the CPU time display values.

CREATE FUNCTION rroad_f_cpu_percentage_string
   ( IN @interval_ProcessCPU               DECIMAL ( 30, 6 ),
     IN @interval_msec                     BIGINT,
     IN @CPU_count                         INTEGER,
     IN @show_zero_as_dash                 VARCHAR ( 1 ) )
   RETURNS VARCHAR ( 10 )

rroad_f_cpu_percentage_string ( 999.0, 2000, 1, 'N' )   100%                                            
rroad_f_cpu_percentage_string ( 2.0,   2000, 1, 'N' )   100%                                            
rroad_f_cpu_percentage_string ( 1.99,  2000, 1, 'N' )   99.5%                                           
rroad_f_cpu_percentage_string ( 1.0,   2000, 2, 'N' )   25.0%                                           
rroad_f_cpu_percentage_string ( 0.0,   2000, 2, 'N' )   0%                                              
rroad_f_cpu_percentage_string ( 0.0,   2000, 2, 'Y' )   -                                               
rroad_f_cpu_percentage_string ( 0.01,  2000, 2, 'N' )   .3%     


Foxhound 4 8. Adhoc Queries 8.3 Adhoc Functions 
CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms
The rroad_f_msecs_as_abbreviated_d_h_m_s_ms.sql function reformats large millisecond interval values into readable strings using the unit abbreviations d, h, m, s and ms.

Foxhound uses this function for many displayed values like Response... Heartbeat, Sample, Ping.

CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms
   ( IN @msecs   BIGINT )
   RETURNS VARCHAR ( 20 )

rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 9590400000 )   111d                                                
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 1028160000 )   11.9d                                               
rroad_f_msecs_as_abbreviated_d_h_m_s_ms (  69200000 )    1d 23h                                              
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 120240000 )    1d 9.4h                                             
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 43020000 )     12h                                                 
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 42984000 )     11.9h                                               
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 7140000 )      1h 59m                                              
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 714000 )       11.9m                                               
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 71000 )        1m 11s                                              
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 11940 )        11.9s                                               
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 1950 )         2s                                                  
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 1940 )         1.9s                                                
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 149 )          149ms                                               
rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 1 )            1ms    


Foxhound 4 8. Adhoc Queries 8.3 Adhoc Functions 
CREATE FUNCTION rroad_f_number_with_commas
The rroad_f_number_with_commas function inserts commas in the integer portion of a number. Leading zeros are implicitly stripped from a numeric argument but not if the argument is already a string.

Foxhound uses this function for many displayed values like Throughput... Req, Commits.

CREATE FUNCTION rroad_f_number_with_commas
   ( IN @p_number_string VARCHAR ( 100 ) )
   RETURNS VARCHAR ( 200 )

rroad_f_number_with_commas ( 1234567.8901234 )    1,234,567.8901234 
rroad_f_number_with_commas ( 00000001111111 )     1,111,111      
rroad_f_number_with_commas ( '00000001111111' )   00,000,001,111,111                            


Foxhound 4 8. Adhoc Queries 8.3 Adhoc Functions 
CREATE FUNCTION rroad_f_decimal_with_commas
The rroad_f_decimal_with_commas function does more than insert commas in large numbers, it replaces zero with a dash, limits the precision of fractional numbers and reduces tiny fractions to zero.

Foxhound uses this function for some displayed values like Disk/Cache: Internal Index, Leaf, Table.

CREATE FUNCTION rroad_f_decimal_with_commas
   ( IN @decimal                    DECIMAL ( 30, 6 ),
     IN @round_to_non_zero_digits   INTEGER DEFAULT 2 )  -- 1 or 2
   RETURNS VARCHAR ( 200 )

rroad_f_decimal_with_commas ( NULL )           -                                             
rroad_f_decimal_with_commas ( 0.0 )            -                                             
rroad_f_decimal_with_commas ( 0.19 )           .19                                           
rroad_f_decimal_with_commas ( 1.29 )           1.3                                           
rroad_f_decimal_with_commas ( 123456789 )      123,456,789                                   
rroad_f_decimal_with_commas ( 123.9 )          124                                           
rroad_f_decimal_with_commas ( 12.9 )           13                                            
rroad_f_decimal_with_commas ( 1.29 )           1.3                                           
rroad_f_decimal_with_commas ( 0.19 )           .19                                           
rroad_f_decimal_with_commas ( 0.019 )          .019                                          
rroad_f_decimal_with_commas ( 0.0019 )         .0019                                         
rroad_f_decimal_with_commas ( 0.00019 )        0                                             
rroad_f_decimal_with_commas ( 0.000019 )       0                                             
rroad_f_decimal_with_commas ( NULL, 1 )        -                                             
rroad_f_decimal_with_commas ( 0.0, 1 )         -                                             
rroad_f_decimal_with_commas ( 0.19, 1 )        .2                                            
rroad_f_decimal_with_commas ( 1.29, 1 )        1.3                                           
rroad_f_decimal_with_commas ( 123456789, 1 )   123,456,789                                   
rroad_f_decimal_with_commas ( 123.9, 1 )       124                                           
rroad_f_decimal_with_commas ( 12.9, 1 )        13                                            
rroad_f_decimal_with_commas ( 1.29, 1 )        1.3                                           
rroad_f_decimal_with_commas ( 0.19, 1 )        .2                                            
rroad_f_decimal_with_commas ( 0.019, 1 )       .02                                           
rroad_f_decimal_with_commas ( 0.0019, 1 )      .002                                          
rroad_f_decimal_with_commas ( 0.00019, 1 )     0                                             
rroad_f_decimal_with_commas ( 0.000019, 1 )    0   


Foxhound 4 8. Adhoc Queries 
8.4 Examples of Adhoc Queries
Active alerts for each target database.
Count active alerts for each target database.
Alert #1 Database unresponsive activity for a particular target database.
Find "Go to:" sample set numbers for Alert #1 / All Clear pairs at least 60 seconds apart.
Investigate relationship between intra-query parallelism and Alert #1 Database Unresponsive.

Recent purge runs.
Selected columns from the most recent purge runs.

Sample sessions for all target databases.

Recent sample headers for each target database.
Recent sample headers and details for each target database.
Recent sample headers, details and connections for each target database.

Sample details selected by exact primary key values.
Sample header, details and connections selected by exact primary key values.

Some AND all the connection data for one connection.
Some AND all the connection data for one connection, up to and including a specific sample timestamp.

Blocked connection samples.
Currently blocked connections for each target database.
Find when connections became blocked.

Long-running queries.

Response and throughput with 100-sample moving averages.

Busy SQL statements.

First and last successful sample timestamps across all target databases.
First and last successful sample timestamps for each target database.

Number of samples for each target database.

Compute the "Totals:" line removed from the Monitor and History pages.

List all the patches that have been applied to the Foxhound database.


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Active alerts for each target database.
SELECT IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       alert_union.*
  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 ASC,
       alert_union.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Count active alerts for each target database.
SELECT IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       COUNT(*) AS "Active Alerts"
  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'
 GROUP BY sampling_options.selected_tab,
       sampling_options.selected_name,
       alert_union.sampling_id
 ORDER BY target_database ASC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Alert #1 Database unresponsive activity for a particular target database.
SELECT IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       alert_union.*
  FROM sampling_options
          INNER JOIN alert_union
          ON alert_union.sampling_id = sampling_options.sampling_id
 WHERE target_database = 'Inventory'
   AND alert_union.alert_number = 1
 ORDER BY alert_union.recorded_at ASC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Find "Go to:" sample set numbers for Alert #1 / All Clear pairs at least 60 seconds apart.
SELECT CAST ( alert.sample_set_number AS INTEGER )      AS "Go to: Alert",
       CAST ( all_clear.sample_set_number AS INTEGER )  AS "Go to: All Clear",
       CAST ( DATEDIFF ( 
                 SECOND,  
                 alert.alert_in_effect_at,
                 all_clear.alert_all_clear_at )
              AS DECIMAL ( 6  ) )                        AS duration,
       LEFT ( STRING ( 
          IF sampling_options.selected_tab = 1 
             THEN 'DSN: ' 
             ELSE 'String: ' 
          END IF,
          sampling_options.selected_name ), 20 )        AS target_database
  FROM sampling_options
       INNER JOIN ( SELECT *  
                      FROM alert  
                     WHERE alert.alert_number = 1  
                  ) AS alert
          ON alert.sampling_id = sampling_options.sampling_id
       INNER JOIN ( SELECT * 
                      FROM all_clear  
                     WHERE all_clear.alert_number = 1  
                  ) AS all_clear
          ON all_clear.sampling_id        = sampling_options.sampling_id
         AND all_clear.alert_in_effect_at = alert.alert_in_effect_at
 WHERE duration >= 60
 ORDER BY target_database ASC,
       alert.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Investigate relationship between intra-query parallelism and Alert #1 Database Unresponsive.
SELECT alert.alert_number,
       alert.sample_set_number,
       sample_connection.connection_number, 
       sample_connection.child_connection_count
  FROM alert
          LEFT OUTER JOIN ( SELECT * 
                              FROM sample_connection
                             WHERE sample_connection.child_connection_count > 0
                          ) AS sample_connection
          ON sample_connection.sample_set_number = alert.sample_set_number
 WHERE alert.alert_number = 1
ORDER BY alert.sample_set_number DESC;

alert_number    sample_set_number    connection_number child_connection_count 
------------ -------------------- -------------------- ---------------------- 
           1              1381013               (NULL)                 (NULL) 
           1              1377390                  259                      6 
           1              1375806               (NULL)                 (NULL) 
           1              1375801               (NULL)                 (NULL) 
           1              1367002                38280                      6 
           1              1366903                38487                      6 
           1              1358577               (NULL)                 (NULL) 
           1              1349911                33137                      6 
           1              1349224                31691                      6 
           1              1347243                31564                      6 
           1              1347097                31690                      6 
           1              1346687                31564                      6 
           1              1343606                31243                      6 
           1              1320836                25967                      6 
           1              1320721                25967                      6 
           1              1298064                18130                      6 
           1              1289448               (NULL)                 (NULL) 
           1              1288153                14994                      6 
           1              1245483                 4439                      6 
           1              1235681               (NULL)                 (NULL) 
           1              1229545               (NULL)                 (NULL) 
           1              1229538               126844                      6 


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Recent purge runs.
SELECT TOP 100 * 
  FROM purge_run
 ORDER BY run_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Selected columns from the most recent purge runs.
SELECT TOP 100
       run_number,
       progress,
       started_at,
       DATEDIFF ( second, started_at, completed_at ) AS sec,
       is_complete,
       old_sample_set_delete_count,
       uninteresting_connections_delete_count,
       sample_purge_interval,
       uninteresting_connections_purge_interval,
       purge_speed,
       * 
  FROM purge_run
 ORDER BY run_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Sample sessions for all target databases.
SELECT sampling_id,
       IF selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       selected_name AS target_database,
       sampling_should_be_running, 
       connection_status_message, 
       last_sample_finished_at
  FROM sampling_options
 ORDER BY target_database;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Recent sample headers for each target database.
SELECT sample_header.row_order,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       sample_header.*
  FROM sampling_options
       INNER JOIN ( SELECT *,
                           RANK() OVER sample_window AS row_order
                      FROM sample_header
                    WINDOW sample_window AS (
                              PARTITION BY sampling_id
                              ORDER BY sample_set_number DESC ) 
                  ) AS sample_header
       ON sample_header.sampling_id = sampling_options.sampling_id
 WHERE sample_header.row_order <= 10
 ORDER BY target_database,
       sample_header.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Recent sample headers and details for each target database.
SELECT sample_header.row_order,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       '     HEADER:',
       sample_header.*,
       '     DETAIL:',
       sample_detail.*
  FROM sampling_options
       INNER JOIN ( SELECT *,
                           RANK() OVER sample_window AS row_order
                      FROM sample_header
                    WINDOW sample_window AS (
                              PARTITION BY sampling_id
                              ORDER BY sample_set_number DESC ) 
                  ) AS sample_header
       ON sample_header.sampling_id = sampling_options.sampling_id
       INNER JOIN sample_detail
       ON sample_detail.sample_set_number = sample_header.sample_set_number
 WHERE sample_header.row_order <= 10
 ORDER BY target_database,
       sample_header.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Recent sample headers, details and connections for each target database.
SELECT sample_header.row_order,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       '     HEADER:',
       sample_header.*,
       '     DETAIL:',
       sample_detail.*,
       '     CONNECTION:',
       sample_connection.*
  FROM sampling_options
       INNER JOIN ( SELECT *,
                           RANK() OVER sample_window AS row_order
                      FROM sample_header
                    WINDOW sample_window AS (
                              PARTITION BY sampling_id
                              ORDER BY sample_set_number DESC ) 
                  ) AS sample_header
       ON sample_header.sampling_id = sampling_options.sampling_id
       INNER JOIN sample_detail
       ON sample_detail.sample_set_number = sample_header.sample_set_number
       LEFT OUTER JOIN sample_connection
       ON sample_connection.sample_set_number = sample_detail.sample_set_number
 WHERE sample_header.row_order <= 10
 ORDER BY target_database,
       sample_header.sample_set_number DESC,
       sample_connection.connection_number;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Sample details selected by exact primary key values.
-- Samples selected by these [sampling_id,sample_set_number] key values shown on the Monitor and History pages:
--    [1,438407]
--    [1,438406]
--    [1,1497]
--    [1,89]
--    [1,88]

SELECT sample_detail.*
  FROM sample_detail
 WHERE sample_detail.sampling_id = 1
   AND sample_detail.sample_set_number IN ( 88, 89, 1497, 438406, 438407 )
 ORDER BY sample_detail.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Sample header, details and connections selected by exact primary key values.
-- Samples selected by these [sampling_id,sample_set_number] key values shown on the Monitor and History pages:
--    [1,438407]
--    [1,438406]
--    [1,1497]
--    [1,89]
--    [1,88]

SELECT IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       '     HEADER:',
       sample_header.*,
       '     DETAIL:',
       sample_detail.*,
       '     CONNECTION:',
       sample_connection.*
  FROM sampling_options
       INNER JOIN sample_header
       ON sample_header.sampling_id = sampling_options.sampling_id
       INNER JOIN sample_detail
       ON sample_detail.sample_set_number = sample_header.sample_set_number
       LEFT OUTER JOIN sample_connection
       ON sample_connection.sample_set_number = sample_detail.sample_set_number
 WHERE sample_header.sampling_id = 1
   AND sample_header.sample_set_number IN ( 88, 89, 1497, 438406, 438407 )
 ORDER BY sample_header.sample_set_number DESC,
       sample_connection.connection_number;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Some AND all the connection data for one connection.
SELECT sample_detail.sample_recorded_at,    -- some columns
       sample_connection.sample_set_number,
       sample_connection.LastPlanText,
       sample_connection.LastStatement,
       sample_connection.*                  -- all columns
  FROM sample_connection
          INNER JOIN sample_detail
                  ON sample_detail.sample_set_number = sample_connection.sample_set_number
 WHERE sample_connection.connection_id_string = '1-6921-20150626091359-851'
 ORDER BY sample_connection.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Some AND all the connection data for one connection, up to and including a specific sample timestamp.
SELECT sample_detail.sample_recorded_at,    -- some columns
       sample_connection.sample_set_number,
       sample_connection.LastPlanText,
       sample_connection.LastStatement,
       sample_connection.*                  -- all columns
  FROM sample_connection
          INNER JOIN sample_detail
                  ON sample_detail.sample_set_number = sample_connection.sample_set_number
 WHERE sample_connection.connection_id_string = '1-6921-20150626091359-851'
   AND sample_detail.sample_recorded_at <= '2015-06-26 09:17:34.522' 
 ORDER BY sample_connection.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Blocked connection samples.
SELECT TOP 1000
       sample_connection.sampling_id,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       sample_connection.sample_set_number,
       sample_header.sample_finished_at           AS sample_recorded_at,
       sample_connection.connection_number        AS blocked_connection_number,
       sample_connection.BlockedOn                AS blocked_by_connection_number,
       sample_connection.LastReqTime,
       sample_connection.LastStatement,
       sample_connection.blocker_reason           AS reason_for_block,
       sample_connection.blocker_owner_name       AS owner_name,
       sample_connection.blocker_table_name       AS table_name,
       sample_connection.blocker_row_identifier   AS row_identifier
  FROM sampling_options
          INNER JOIN sample_connection
                  ON sample_connection.sampling_id = sampling_options.sampling_id
          INNER JOIN sample_header
                  ON sample_header.sampling_id       = sampling_options.sampling_id
                 AND sample_header.sample_set_number = sample_connection.sample_set_number
 WHERE sample_connection.BlockedOn <> 0
 ORDER BY sample_connection.sample_set_number DESC,
       sample_connection.connection_number ASC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Currently blocked connections for each target database.
-- Here's how this query works:
-- 
--   - The WITH clause creates a temporary view that is used in the SELECT * FROM block at the bottom.
-- 
--   - The FROM sampling_options selects one row for each target database.
-- 
--   - The CROSS APPLY selects one sample_header row for each target database. That row is the most
--     recent successful sample for that target. If sampling is running then it will be a recent row.
--     If sampling is stopped then it might be an old row. Either way, it is the "most recent 
--     successful sample" for each target database.
-- 
--   - The CROSS APPLY clause is used instead of INNER JOIN because the inner FROM sample_header
--     clause refers to a column in a different table in the outer FROM clause, something you
--     can't do with INNER JOIN. 
-- 
--   - The two LEFT OUTER JOIN clauses gather up all the blocked (victim) sample_connection rows 
--     plus the corresponding blocking (evil-doer) sample_connection rows. 
-- 
--   - The LEFT OUTER JOIN clause is used instead of INNER JOIN so the view will return at least 
--     one row for each target database even if it doesn't have any blocked connections.

WITH block AS (
SELECT sampling_options.sampling_id                  AS sampling_id,
       latest_header.sample_set_number               AS sample_set_number,
       latest_header.sample_finished_at              AS recorded_at, 
       IF sampling_options.selected_tab = 1 
          THEN STRING ( 'DSN: ',    sampling_options.selected_name )  
          ELSE STRING ( 'String: ', sampling_options.selected_name )
       END IF                                        AS target_database,
       blocked_connection.Userid                     AS blocked_Userid,
       blocked_by_connection.Userid                  AS blocked_by_Userid,
       blocked_connection.blocker_reason             AS reason,
       blocked_connection.ReqStatus                  AS ReqStatus,
       blocked_connection.blocker_table_name         AS blocker_table_name  
  FROM sampling_options                                            -- one row per target database
       CROSS APPLY ( SELECT TOP 1 *                                -- most recent successful sample for each target
                       FROM sample_header
                      WHERE sample_header.sampling_id = sampling_options.sampling_id 
                        AND sample_header.sample_lost = 'N'
                      ORDER BY sample_header.sample_set_number DESC ) AS latest_header
       LEFT OUTER JOIN ( SELECT *                                       -- all the blocked connections in the latest sample
                           FROM sample_connection
                          WHERE sample_connection.BlockedOn <> 0 ) AS blocked_connection
          ON  blocked_connection.sampling_id       = sampling_options.sampling_id 
          AND blocked_connection.sample_set_number = latest_header.sample_set_number
       LEFT OUTER JOIN sample_connection AS blocked_by_connection       -- the corresponding blocking connections
          ON  blocked_by_connection.sampling_id       = sampling_options.sampling_id 
          AND blocked_by_connection.sample_set_number = blocked_connection.sample_set_number
          AND blocked_by_connection.connection_number = blocked_connection.BlockedOn )
SELECT *
  FROM block
 ORDER BY block.target_database,
       block.blocked_Userid;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Find when connections became blocked.
-- Take the sample_set_number and paste it into the "Go to:" field on the 
-- Sample History page to see what was going on when the connection became blocked.

SELECT sample_connection.sampling_id,
       MIN ( sample_connection.sample_set_number ) AS sample_set_number,
       sample_connection.connection_number 
  FROM sample_connection
 WHERE COALESCE ( sample_connection.BlockedOn, 0 ) <> 0
 GROUP BY sample_connection.sampling_id,
       sample_connection.connection_id_string,
       sample_connection.connection_number,
       sample_connection.LastReqTime
 ORDER BY sample_set_number DESC,
       sample_connection.connection_number ASC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Long-running queries.
-- Each connection is uniquely identified by sampling_id, connection_number and LoginTime.
-- Each query is uniquely identified by sampling_id, connection_number, LoginTime and LastReqTime.

WITH long_running_query AS
   ( SELECT sample_connection.sampling_id               AS sampling_id, 
            sample_connection.connection_number         AS connection_number, 
            sample_connection.LoginTime                 AS LoginTime,
            sample_connection.LastReqTime               AS LastReqTime,
            MIN ( sample_connection.sample_set_number ) AS from_sample_set_number, 
            MAX ( sample_connection.sample_set_number ) AS to_sample_set_number 
       FROM sample_connection
      WHERE sample_connection.ReqStatus = 'Executing'
        AND sample_connection.time_since_last_request > 0
        AND TRIM ( COALESCE ( sample_connection.LastStatement, '' ) ) <> ''
      GROUP BY sample_connection.sampling_id, 
            sample_connection.connection_number, 
            sample_connection.LoginTime,
            sample_connection.LastReqTime
     HAVING from_sample_set_number <> to_sample_set_number )
SELECT long_running_query.sampling_id                AS sampling_id,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF                                        AS connection_type,
       sampling_options.selected_name                AS target_database,
       long_running_query.connection_number          AS connection_number, 
       long_running_query.to_sample_set_number       AS sample_set_number,
       long_running_query.LoginTime                  AS LoginTime,
       long_running_query.LastReqTime                AS started_at,
       sample_header.sample_finished_at              AS recorded_at,
       CAST ( sample_connection.time_since_last_request / 1000 AS BIGINT )  AS elapsed_seconds,
       sample_connection.LastStatement               AS LastStatement,
       sample_connection.LastPlanText                AS LastPlanText 
  FROM sampling_options
          INNER JOIN long_running_query
                  ON long_running_query.sampling_id = sampling_options.sampling_id
          INNER JOIN sample_header
                  ON sample_header.sampling_id       = sampling_options.sampling_id
                 AND sample_header.sample_set_number = long_running_query.to_sample_set_number
          INNER JOIN sample_connection
                  ON sample_connection.sample_set_number = long_running_query.to_sample_set_number 
                 AND sample_connection.connection_number = long_running_query.connection_number;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Response and throughput with 100-sample moving averages.
SELECT IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       sample_detail.sample_set_number,
       sample_detail.sample_recorded_at,
       ( sample_detail.interval_msec / 1000.0 )                AS interval_sec,
       sample_detail.ConnCount                                 AS connections,
       sample_detail.interval_CPU_percent                      AS CPU_percent,
       ( sample_detail.canarian_query_elapsed_msec / 1000.0 )  AS heartbeat_sec,
       ( sample_detail.sample_elapsed_msec         / 1000.0 )  AS sample_sec,
       sample_detail.interval_Req    / interval_sec            AS requests_per_sec,
       sample_detail.interval_Commit / interval_sec            AS commits_per_sec,
       ( sample_detail.interval_BytesReceived 
         + sample_detail.interval_BytesSent ) / interval_sec   AS bytes_per_sec,   
       AVG ( connections )      OVER moving_window             AS avg_connections,
       AVG ( CPU_percent )      OVER moving_window             AS avg_CPU_percent,
       AVG ( heartbeat_sec )    OVER moving_window             AS avg_heartbeat_sec,
       AVG ( sample_sec )       OVER moving_window             AS avg_sample_sec, 
       AVG ( requests_per_sec ) OVER moving_window             AS avg_requests_per_sec, 
       AVG ( commits_per_sec )  OVER moving_window             AS avg_commits_per_sec  
  FROM sampling_options
       INNER JOIN sample_detail
          ON sample_detail.sampling_id = sampling_options.sampling_id
 WHERE sample_detail.sampling_id = 4
   AND sample_detail.sample_lost = 'N'
   AND sample_detail.sample_recorded_at BETWEEN 'Feb 21 1:14:49 PM' AND 'Feb 21 3:32:34 PM'
WINDOW moving_window AS
          ( ORDER BY sample_detail.sample_set_number DESC
            ROWS BETWEEN CURRENT ROW AND 99 FOLLOWING )
 ORDER BY sample_detail.sample_set_number DESC;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Busy SQL statements.
SELECT sampling_options.selected_name         AS target_database,
       sample_connection.LoginTime,
       sample_connection.connection_number, 
       sample_connection.Name                 AS connection_name,
       sample_connection.Userid,
       MAX ( sample_connection.busy_percent ) AS busy_percent,
       sample_connection.LastStatement,  
       sample_connection.LastPlanText  
  FROM sampling_options
          INNER JOIN sample_connection
                  ON sample_connection.sampling_id = sampling_options.sampling_id
 WHERE sample_connection.busy_percent >= 5
   AND sample_connection.LastStatement <> ''
   AND sample_connection.Name NOT LIKE 'Foxhound%'
 GROUP BY sampling_options.selected_name,
       sample_connection.LoginTime,
       sample_connection.connection_number, 
       sample_connection.Name,
       sample_connection.Userid,
       sample_connection.LastStatement, 
       sample_connection.LastPlanText  
 ORDER BY target_database,
       sample_connection.LoginTime,
       sample_connection.connection_number; 


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
First and last successful sample timestamps across all target databases.
SELECT DATEFORMAT ( MIN ( sample_header.sample_finished_at ), 'Mmm Dd yyyy Hh:nn:ss AA' ) AS from_timestamp, 
       DATEFORMAT ( MAX ( sample_header.sample_finished_at ), 'Mmm Dd yyyy Hh:nn:ss AA' ) AS to_timestamp  
  FROM sample_header
          INNER JOIN sampling_options
                  ON sampling_options.sampling_id = sample_header.sampling_id
 WHERE sample_header.sample_lost = 'N';

First and last successful sample timestamps for each target database.

SELECT sample_header.sampling_id AS sampling_id,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       DATEFORMAT ( MIN ( sample_header.sample_finished_at ), 'Mmm Dd yyyy Hh:nn:ss AA' ) AS from_timestamp, 
       DATEFORMAT ( MAX ( sample_header.sample_finished_at ), 'Mmm Dd yyyy Hh:nn:ss AA' ) AS to_timestamp  
  FROM sample_header
          INNER JOIN sampling_options
                  ON sampling_options.sampling_id = sample_header.sampling_id
 WHERE sample_header.sample_lost = 'N'
 GROUP BY sampling_id,
       connection_type,
       target_database
 ORDER BY target_database,
       connection_type;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Number of samples for each target database.
SELECT sample_header.sampling_id AS sampling_id,
       IF sampling_options.selected_tab = 1 
          THEN 'DSN' 
          ELSE 'String' 
       END IF AS connection_type,
       sampling_options.selected_name AS target_database,
       COUNT(*) AS sample_count
  FROM sample_header
          INNER JOIN sampling_options
                  ON sampling_options.sampling_id = sample_header.sampling_id
 GROUP BY sampling_id,
       connection_type,
       target_database
 ORDER BY target_database,
       connection_type;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
Compute the "Totals:" line removed from the Monitor and History pages.
Earlier versions of Foxhound displayed a "Totals:" line in the "Most Recent Sample" and "Top Sample" sections of the Monitor, Sample History and Connection History pages.

That line has been removed from Foxhound 4 to reduce clutter, but the values are available using the following adhoc query:

SELECT 'Totals:'                                                 AS "Totals:",  
       rroad_f_number_with_commas ( sample_detail.Req )          AS "Throughput... Req",  
       rroad_f_number_with_commas ( sample_detail."Commit" )     AS "Throughput... Commits",
       rroad_f_bytes_as_kmg ( sample_detail.BytesReceived 
                            + sample_detail.BytesSent )          AS "Throughput... Bytes",
       rroad_f_msecs_as_abbreviated_d_h_m_s_ms ( 
          CAST ( sample_detail.ProcessCPU * 1000.0 AS BIGINT ) ) AS "CPU",
       rroad_f_cpu_percentage_string (
          sample_detail.ProcessCPU,
          DATEDIFF ( MILLISECOND, 
                     sample_detail.StartTime, 
                     sample_detail.sample_recorded_at ),
          sample_detail.CPU_count,
          'N' )                                                  AS "CPU Time % av",
       rroad_f_number_with_commas ( sample_detail.CachePanics )  AS "Cache Panics",  
       rroad_f_number_with_commas 
          ( sample_detail.QueryLowMemoryStrategy )               AS "Cache Low Memory",  
       rroad_f_number_with_commas ( 
          CAST ( ROUND ( CAST ( sample_detail.CacheHits 
                                AS DECIMAL ( 30, 6 ) )
                         / CAST ( GREATER ( 1, sample_detail.CacheRead ) 
                                  AS DECIMAL ( 30, 6 ) )
                         * 100.0,
                         2 )
                 AS DECIMAL ( 30, 2 ) ) )                         AS "Cache Satisfaction % av",
       rroad_f_number_with_commas ( sample_detail.DiskRead )      AS "Disk Reads", 
       rroad_f_number_with_commas ( sample_detail.DiskWrite )     AS "Disk Writes", 
       rroad_f_number_with_commas ( sample_detail.LogWrite )      AS "Log Writes", 
       rroad_f_number_with_commas ( sample_detail.IndAdd )        AS "Index Adds",
       rroad_f_number_with_commas ( sample_detail.IndLookup )     AS "Index Lookups",
       rroad_f_number_with_commas (  
          CAST ( ROUND ( CAST ( sample_detail.IndLookup 
                                - LESSER ( sample_detail.IndLookup, 
                                           sample_detail.FullCompare )  
                                AS DECIMAL ( 30, 6 ) )
                       / CAST ( GREATER ( 1, sample_detail.IndLookup )   
                                AS DECIMAL ( 30, 6 ) )
                       * 100.0,
                       0 )
                AS DECIMAL ( 30, 0 ) ) )                          AS "Index Satisfaction % av",
       rroad_f_number_with_commas ( sample_detail.FullCompare )   AS "Full Index Comps"
  FROM sample_detail 
 WHERE sample_detail.sampling_id = 7
   AND sample_detail.sample_set_number = 1640173;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries 
List all the patches that have been applied to the Foxhound database.
SELECT * 
  FROM applied_patch 
 ORDER BY applied_patch.inserted_at;


Foxhound 4 8. Adhoc Queries 8.4 Examples of Adhoc Queries