November 18, 2019 by Kenneth Fisher
Anecdotally any session_id that’s 50 or less is going to be a system session and anything over 50 will be a user session. Just to be clear system sessions are things like the log writer, checkpoint, task manager, XE dispatcher and timer etc. I.E. All of the tasks that SQL requires to keep things running.
Now, is it true? No, obviously not or I probably wouldn’t be writing about it would I?
I believe it is true that every session 50 or lower is a system task, at least I’ve never seen one otherwise. I certainly won’t guarantee it though. That said I absolutely have seen system sessions greater than 50. In fact right now I’m looking at a server with 207 system sessions. The math is pretty obvious right? Some (most) of those sessions have to have session ids greater than 50. So how do we tell? Fortunately it’s easy.
SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 0;
Realistically that’s not much harder than typing session_id <= 50 and far, far more accurate.