• I don't have much experience on any of those and don't have much spare time to play around and set something. Maybe someone else can give you something more specific.

    To use sys.dm_exec_sessions, you need to be querying the view to know what's going on at that point in time, as it will only show current sessions. An alternative is to use procedures like sp_who, sp_who2 or others that can be downloaded like Erland Sommarskog's beta_lockinfo or Adam Machanic's sp_whoisactive. All this will just give you the current state of the server.

    To monitor constantly, there are the other 2 options.

    To run a trace, use SQL Server Profiler. Be sure to save the trace to a file to minimize the needed resources and choose the appropriate events and columns.

    Extended events are a muchly improved version of Profiler (which are actually available in 2008, but GUI was included until 2012) which have lower impact on the performance of your server. You can find lots of articles in Google.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2