The New Foxhound 3 Connection History Page

One of the new features in Foxhound 3 is the Connection History page. Previously, if you wanted to see the performance history of one single connection over time you had two choices, both unattractive:

• Repeatedly redisplay the History page to see all the connection data for each successive sample, then repeatedly scroll through the (hundreds of?) connections to find the one of interest, or
  

• code an adhoc query for the connection of interest... if you could figure out what column(s) to use to uniquely identify a single connection (connection number isn't enough).
  

The Connection History page does all that for you; here's an example showing three samples for a single connection that executed a SELECT statement (one sample) followed by a CALL (two samples):

The next example shows a connection "347 / g.mikhailov" that

• was first blocked by a row lock held by a second connection "346 / h.barbosa",
  

• and then blocked again by a third connection "389 / s.winkler" after the Foxhound AutoDrop feature dropped the second connection:
  

You can still code an adhoc query to show the same thing, and Foxhound 3 makes it easier with the new sample_connection.connection_id_string column.

Here's what the Foxhound 3 Help says about the Connection Id String:

---

The Connection Id String column is an artificial identifier constructed by Foxhound to uniquely identify each target connection within the entire Foxhound database.

Foxhound uses this string to create URLs for opening new Connection History pages. You may find it useful when creating adhoc queries to select all the history for one connection. It is stored in the sample_connection.connection_id_string column, and there is an index on that column.

Here's the format of the Connection Id String:

ss-cc-yyyymmddhhnnss-sss
where...
ss                   sample_connection.sampling_id - unique identifier to each target database sampling session
cc                   sample_connection.connection_number  
yyyymmddhhmmss-sss   DATEFORMAT ( sample_connection.LoginTime, 'YYYYMMDDHHNNSS-SSS' ) 

---

Here's an adhoc query that uses a simple (and efficient) WHERE clause using the value '1-347-20141029161934-088' from the screenshot above:

SELECT CAST ( sample_connection.connection_number AS INTEGER ) AS "Conn#", CAST ( sample_connection.UserId AS VARCHAR ( 20 ) ), DATEFORMAT ( sample_header.sample_finished_at, 'Mmm Dd yyyy Hh:nn:ss AA' ) AS "Connection Samples", CAST ( sample_connection.BlockedOn AS INTEGER ) AS "Blocked By", CAST ( sample_connection.blocker_table_name AS VARCHAR ( 10 ) ) AS "Table", CAST ( sample_connection.blocker_reason AS VARCHAR ( 50 ) ) AS "Reason" FROM sample_connection INNER JOIN sample_header ON sample_header.sample_set_number = sample_connection.sample_set_number WHERE sample_connection.connection_id_string = '1-347-20141029161934-088' ORDER BY sample_connection.sample_set_number DESC; Conn# UserId Connection Samples Blocked By Table Reason ----------- -------------------- ----------------------- ----------- ---------- -------------------------------------------------- 347 g.mikhailov Oct 29 2014 4:20:38 PM 419 inventory Row Transaction Intent, Row Transaction WriteNoPK 347 g.mikhailov Oct 29 2014 4:20:28 PM 389 inventory Row Transaction Intent, Row Transaction WriteNoPK 347 g.mikhailov Oct 29 2014 4:20:17 PM 389 inventory Row Transaction Intent, Row Transaction WriteNoPK 347 g.mikhailov Oct 29 2014 4:20:07 PM 346 inventory Row Transaction Intent, Row Transaction WriteNoPK 347 g.mikhailov Oct 29 2014 4:19:58 PM 346 inventory Row Transaction Intent, Row Transaction WriteNoPK 347 g.mikhailov Oct 29 2014 4:19:47 PM 0 (NULL) (NULL) 347 g.mikhailov Oct 29 2014 4:19:37 PM 0 (NULL) (NULL)

---