Sample of IHE Performance monitoring dashboard
I've asked a lot of questions leading up to this, so I wanted to share some of my progress.
The blue line represents the number of messages processed. The background color represents the average response time. You can see ticks for each hour (and bigger ticks for each day). Hovering over any point in the graph will show you the numbers for that period in time.
This is super useful for "at a glance" performance monitoring as well as establishing patterns in our utilization.
Here is the query used:
SELECT mh.name AS MessageType, COUNT(mh.name) AS MessageCount, CAST(AVG(ResponseTime) AS DECIMAL(5, 2)) AS AvgResponseTime FROM ( SELECT li.SessionId, li.Name, DATEDIFF(s, MIN(li.TimeCreated), MAX(lo.TimeCreated)) AS ResponseTime FROM ( SELECT SessionId, name, TimeCreated FROM ens.messageheader h1, HS_Message.XMLMessage m1 WHERE h1.MessageBodyId = m1.ID AND h1.TimeCreated > DATEADD(hh, -1, GETUTCDATE())) li JOIN ( SELECT SessionId, TimeCreated FROM ens.messageheader h2, HS_Message.XMLMessage m2 WHERE h2.MessageBodyId = m2.ID AND h2.TimeCreated > DATEADD(hh, -1, GETUTCDATE())) lo ON li.SessionId = lo.SessionId GROUP BY li.SessionId) mh WHERE mh.name LIKE '%REQUEST' GROUP BY mh.name