Smashing a DMV Myth: session_id > 50 == User Process

Ever since I started working with SQL Server, I’ve been seeing advice to filter system views based on session identifiers, in order to return only user processes. The general advice is to look for session IDs (or SPIDs) greater than 50. And this seems relatively safe if you look at the system views on an average server.

A recent conversation on an MVP mailing list revealed that this magic number, while perhaps once a legitimate filter, is certainly not safe to use in SQL Server 2005 or SQL Server 2008. Several system features can–and will–use session IDs greater than 50, because there is simply not enough room otherwise. Examples include:

• Large servers that use soft NUMA, because there is one checkpoint and lazy writer thread per NUMA node
• Asynchronous statistics updating, again (and especially) on larger servers
• Database mirroring, especially if a large number of databases are involved
• Service Broker activation, when a large number of activation tasks are being used

And there may be other cases as well. The point is, the number 50 is no longer a valid way to filter out system session IDs.

So what is the correct way to proceed? Two options:

If you’re still using the legacy sysprocesses view–which I do, and which Who is Active does–you can use the fact that system processes have a blank host name, and filter on the hostname column as in the following query (note that all versions of Who is Active already do this; I didn’t realize just how dangerous the magic number was in this case, but I always avoid them anyway and this situation only supports that mindset):

[sql]

SELECT *
FROM sys.sysprocesses
WHERE
hostname > ” [/sql]

UPDATE: Hostname is not bulletproof–see Dan’s comments below. Turns out that an external process can tell SQL Server what its host name is, and can choose to send a blank string. Another column is not settable, however, and that’s hostprocess. Same rules; look for the blank (and Who is Active has been updated accordingly, as of v9.89):

[sql]

SELECT *
FROM sys.sysprocesses
WHERE
hostprocess > ” [/sql]

If you’re using the DMVs, the sys.dm_exec_sessions view has a handy and well-named column, is_user_process:

[sql]

SELECT *
FROM sys.dm_exec_sessions
WHERE
is_user_process = 1 [/sql]

So there you have it. User sessions, and only user sessions, the right way.

Enjoy!