Have you ever wondered who is connected to SQL server? sys.dm_exec_sessions is an excellent DMV to be able to determine who is logged on and where they are logged on from.
Often times I’ll use the view to narrow down how many connections a certain application is using, or to narrow down what machines connections are coming from. A very common usage of this view is when I query on sys.dm_exec_requests. I’ll join by session_id on this DMV in order to determine where a query is coming from. Based on the host name, I can usually find out who is working with the query.
Another common situation I’ve used this query is to kill outstanding sessions. I’ll load the output into a table variable and go through the sessions one by one with the “kill” command to terminate them. This is very useful when I need to bring down the server, or I know there is a rogue application causing mischief on the server.
When you first select from the view you may notice some odd entries with a null host_name, and several other null columns. It’s completely normal and just some internal SQL tasks being reflected in the view.
— get the most connections by host name
GROUP BY host_name
ORDER BY Count(*) DESC
Check out more on this DMV on the MSDN site: http://msdn.microsoft.com/en-us/library/ms176013.aspx