Using DMV to identify sessions in your SQL Server

More than running sp_who2, which gives you more information than the good ol’ sp_who, or querying sys.dm_exec_sessions to check all sessions running in your SQL Server, you need be able to flag to suspicious or questionable sessions thats running transactions in your server. You need to identify which session has multiple connections or sessions with context switching or sessions with orphaned transactions then move on to eliminate these sessions.

Logins with more than one session

To identify logins running multiple sessions in your SQL Server you may run query below

SELECT login_name, COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
GROUP BY login_name
ORDER BY login_name

If you have a baseline figure of number of connections per login, including logins you may have at the application level, then you know if a login has a doubtful number of connections. Though this may not at all present a critical problem, it’s a nice-to-have information at hand that you can discuss with the development team regarding their application design.

Sessions with context switching

Context switching is the executing a T-SQL statement under the guise of another user connection in order to use their access rights that comes with their credential. To identify if there is such session running in your SQL Server, you may run below query and still using sys.dm_exec_sessions DMV;

SELECT session_id, login_name, original_login_name
FROM sys_dm_exec_sessions
WHERE login_name <> original_login_name AND is_user_process = 1

Sessions with orphaned transactions

The query below will filter out all sessions with orphaned transactions, still using the same DMV, dm_exec_sessions.

FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_tran_session_transactions dtst ON des.session_id = dtst.session_id
LEFT JOIN sys.dm_exec_request der ON dtst.session_id = der.session_id
WHERE der.session_id IS NULL
ORDER BY des.session_id

The LEFT JOIN on sys.dm_exec_request and returning only for NULL results in sessions that are still open and yet do not have a corresponding request associated to that session.

Sessions that are inactive 

To filter out all open sessions with still active connections but have been inactive or have no active request running in your SQL Server, you can run the query below.

FROM sys.dm_exec_sessions des INNER JOIN sys_dm_tran_session_transactions dtst ON des.session_id = dtst.session_id
WHERE des.is_user_process = 1 AND DATEDIFF(dd, des.Last_request_end_time, GETDATE()) > 3
ORDER BY des.last_request_end_time

From the result of the above query, you can watch out for the [STATUS] column that is not equal to ‘Running’ (maybe ‘Sleeping’, ‘Dormant’, etc). In m sample query above, i filter out all open sessions for the last 3 days but you can change this to any number of day as your case maybe.

Now that you have identified sessions that are orphaned or inactive sessions, you may proceed to clean up your sessions and KILL those who are not needed anymore.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: