A DMV a Day – Day 2

The DMV for Day 2 is sys.dm_exec_sessions, which is described by BOL as:

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

This query tells you how many sessions each login has on your SQL Server instance. This query works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    --  Get SQL users that are connected and how many sessions they have 
    SELECT login_name, COUNT(session_id) AS [session_count] 
    FROM sys.dm_exec_sessions 
    GROUP BY login_name
    ORDER BY COUNT(session_id) DESC;

This can be useful, especially if you use application level logins for different applications that use your database instance. If you know your baseline values for the number of connections per login, it is easier to see when something has changed.

This entry was posted in SQL Server 2008. Bookmark the permalink.

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