Question: How do I find the currently-blocked connections in all the databases being monitored by Foxhound?
Answer: Complete access to the entire Foxhound database for adhoc queries is one of the hallmarks of Foxhound, but sometimes it requires more than a simple SELECT.
Here's a query that answers the question, followed by a sample result set:
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;
sampling_id, sample_set_number, recorded_at, target_database, blocked_Userid, blocked_by_Userid, reason, ReqStatus, blocker_table_name 16, 2583089, '2015-02-20 09:43:56.544', 'DSN: ddd10', , , , , 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'c.ryan', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'f.thomson', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'g.mikhailov', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'h.barbosa', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'i.miller', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'n.simpson', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'u.wouters', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'x.wang', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 17, 2583088, '2015-02-20 09:43:56.251', 'DSN: Inventory', 'y.gustavsson', 'e.reid', Row Transaction Intent, Row Transaction WriteNoPK, BlockedLock, 'inventory' 15, 2583090, '2015-02-20 09:44:01.005', 'DSN: RuralFinds', , , , , |
- The WITH clause on lines 1 through 28 creates a temporary view that is used in the SELECT * FROM block at the bottom.
The FROM sampling_options on line 14 selects one row for each target database. The rest of the query is designed to show at least row for each target even if it doesn't have any blocked connection, even if sampling is currently stopped.
- The CROSS APPLY on lines 15 through 19 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.
Without the CROSS APPLY clause, the SELECT TOP 1 clause wouldn't work properly, and the query would become much more complex... it's the CROSS APPLY that makes the TOP 1 work properly by returning a different TOP 1 row for each row in sampling_options.
- The two LEFT OUTER JOIN clauses on lines 20 through 28 gather up all the blocked (victim) sample_connection rows plus the corresponding blocking (evil-doer) sample_connection rows.
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.
