Using CROSS APPLY To Help SELECT TOP 1

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.
  

For more queries, see "How do I run adhoc queries on the Foxhound database?"

---