Dynamic Management View: sys.dm_exec_sessions

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
SELECT host_name,Count(*)
FROM   sys.dm_exec_sessions
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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s