Monday, January 14, 2008 5:44 PM
xmldeveloper
It has been 3 years and it finally happened - correct usage of sys.dm_exec_sessions to retrieve user sessions
Since SQL Server 2005 in all the demos (even in Microsoft demos) involving sys.dm_exec_sessions DMV I saw this usage for retrieving user sessions:
SELECT * FROM sys.dm_exec_sessions WHERE SPID > 50
This comes from SQL Server 2000 where you could select using from sysprocesses table the client processes using:
SELECT * FROM sysprocesses WHERE spid > 50
In SQL Server 2005 there is no guarantee that SQL Server will use less than 50 system sessions so you should use SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 to get user sessions.
Read more about it here: How It Works: System Sessions
P.S. For three years, I promised myself that I will blog about it but somehow it slipped from my mind.