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

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

    1278
    18

    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):

    SELECT *
    FROM sys.sysprocesses
    WHERE
      hostname > ''
    

    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):

    SELECT *
    FROM sys.sysprocesses
    WHERE
      hostprocess > ''
    

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

    SELECT *
    FROM sys.dm_exec_sessions
    WHERE
      is_user_process = 1
    

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

    Enjoy!

    Previous articleT-SQL Tuesday #007 and T-SQL Tuesday Has a Logo
    Next articleRevisiting ISNULL, COALESCE, and the Perils of Micro-Optimization
    Adam Machanic helps companies get the most out of their SQL Server databases. He creates solid architectural foundations for high performance databases and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has contributed to numerous books on SQL Server development. A long-time Microsoft MVP for SQL Server, he speaks and trains at IT conferences across North America and Europe.

    18 COMMENTS

    1. Be careful as that’s not always the case!  Other applications can also hide host_name from sys.dm_exec_sessions.
      The system processes hostnames are NULL, but, for example, VMWare’s Virtual Center does not supply a hostname.  Make your statement "is not NULL" instead.

    2. Dan,
      System process hostnames are not NULL; they’re an empty string. The hostname column is non-nullable… Do you consider the VMWare processes to be system processes? If not, then something else needs to be done to filter this–probably a join to sys.dm_exec_sessions for the is_user_process column.

    3. Dan,
      Can you check and see if the VMWare processes have a non-blank value in the sys.sysprocesses.hostprocess column? If so, I think that’s the best solution given what you said about hostname.

    4. Sorry, the NULL/blank was only in sys.dm_exec_sessions.  But in sys.sysprocesses, the VMWare processes still do have the blank hostname/program name.  The hostprocess however is populated.

    5. Thanks for pointing this out, but I am presuming the the rule the user processes absolutely DONT appear 50 or under is actually still valid? …because I personally dont care about capturing too much information just that I dont lose what I need.

    6. Hi Mark,
      That’s my understanding, yes. But personally at this point I wouldn’t trust the magic number either way, especially given that we have surefire ways of properly filtering the data.

    7. Yes, the DMV (Department of Motor Vehicles) has myths such as "we work efficiently and we’ll get to you soon".  It’s not true, trust me on this.  ðŸ™‚

    Comments are closed.