Help for Foxhound 4.0.4740a
Table of Contents [RisingRoad]
8.1 Frequently Asked Questions
Here's what the ISQL command line looks like:
Here's how to change the password:
To see the adhoc schema, use the String tab of the Foxhound menu page to select this connection string
then click on the Display Schema button.
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:
Some of these functions are used in the example
Compute the "Totals:" line removed from the Monitor and History pages.
CREATE FUNCTION rroad_f_cpu_percentage_string
CREATE FUNCTION rroad_f_msecs_as_abbreviated_d_h_m_s_ms
Foxhound uses this function for many displayed values like Throughput... Bytes
Foxhound uses this function for the CPU time display values.
Foxhound uses this function for many displayed values like Response... Heartbeat, Sample, Ping.
Foxhound uses this function for many displayed values like Throughput... Req, Commits.
Foxhound uses this function for some displayed values like Disk/Cache: Internal Index, Leaf, Table.
Recent purge runs.
Sample sessions for all target databases.
Recent sample headers for each target database.
Sample details selected by exact primary key values.
Some AND all the connection data for one connection.
Blocked connection samples.
Response and throughput with 100-sample moving averages.
First and last successful sample timestamps across all target databases.
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.
First and last successful sample timestamps for each target database.
That line has been removed from Foxhound 4 to reduce clutter, but the values are available
using the following adhoc query:
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
"%SQLANY16%\bin64\dbisql.com" -c "ENG=foxhound4; DBN=f; UID=ADHOC; PWD=SQL; CON=Foxhound4-ADHOC"
GRANT CONNECT TO ADHOC IDENTIFIED BY 'JqDCt64Kfy73';
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.
Foxhound 4 Adhoc Schema - autostart and connect
Note: You can't run queries on the Adhoc Schema database, it's just there to show you what the schema looks like.
8.2 Adhoc Views
You have to use views to write your adhoc queries, not the base tables.
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.
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.
CREATE FUNCTION rroad_f_bytes_as_kmg
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.
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
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.
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%
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.
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
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.
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
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.
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
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.
Selected columns from the most recent purge runs.
Recent sample headers and details for each target database.
Recent sample headers, details and connections for each target database.
Sample header, details and connections selected by exact primary key values.
Some AND all the connection data for one connection, up to and including a specific sample timestamp.
Currently blocked connections for each target database.
Find when connections became blocked.
First and last successful sample timestamps for each target database.
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;
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;
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;
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;
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
SELECT TOP 100 *
FROM purge_run
ORDER BY run_number DESC;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
-- 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;
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;
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;
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';
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;
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;
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.
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;
List all the patches that have been applied to the Foxhound database.
SELECT *
FROM applied_patch
ORDER BY applied_patch.inserted_at;