Find where Active Sessions are coming from

Question

Is there any way to drill into the active sessions to find what user id is generating the active sessions?

We are seeing times where there is an unusually high number of active sessions and would like to find out where they are coming from.

Tried drilling into the DBUsers tab on the Trends page, but that just shows the top users by wait times.

We want to see specifically which user IDs are generating the large number of active sessions and how many per User ID and also possibly by host name

Is DPA capturing the actual session information anywhere that we can dig it out from?

Would like to see if we can get it out of DPA somewhere rather than having to write our own process to capture session activity

Thanks,

Ray

rrankins · Answer

Thanks.

I was hoping that DPA was capturing some of the detail for the active sessions for at least a short period of time but I guess it's just aggregating it.

Sounds like what I'll have to do is either setup something that captures info on the active sessions on a defined interval, and/or setup an alert that captures the info when the number of active sessions exceeds a certain threshold.

All I'm really looking for is the session login name, host name, and application name if set.

I use a stored proc (sp_whoisactive) when I'm monitoring manually, I think I can use the same proc to capture the info I need when triggered.

I can then collate and aggregate the raw data captured.

mandevil · Answer

Ray, couple of thoughts on this:

* DPA persists only certain data about session activity currently (select metadata - basically the dimensional tabs in the UI)
* You can watch for current sessions and keep refreshing them, but we're only as good as what information is sent from the client application - some send more client info or session metadata than others
* If you want to do this programmatically, you could probably capture it periodically using an alert that can send you some data, but it's going to be a manual process to collate it - it's just a collection engine now to gather raw data

Note that all of this is pretty custom and not what DPA was designed to do, but I'd think with some creative thought, you could use DPA's mechanisms to at least do some sampling.

If you do come up with some good stuff on this, it's always appreciated by the community to share back what you might come up with...

Let me know if that isn't the right direction you were looking to go.