THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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

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!

Published Wednesday, June 23, 2010 12:26 PM by Adam Machanic
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Phil Brammer said:

+1.  Thanks for airing this out.

June 23, 2010 12:25 PM
 

Amit Banerjee said:

+1. Good point. A lot of monitoring systems out there are a victim to this assumption!

June 23, 2010 12:35 PM
 

mjswart said:

I did not know this! Thanks for pointing it out Adam.

I've got to remember hostname > ''

June 23, 2010 2:21 PM
 

Mateus said:

thanks for this tip ;)

June 23, 2010 3:19 PM
 

noeldr said:

Great tip!

BTW it was not a "myth busting" but a CHANGE ;-)

June 23, 2010 3:33 PM
 

Dan said:

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.

June 23, 2010 3:35 PM
 

Adam Machanic said:

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.

June 23, 2010 4:54 PM
 

Adam Machanic said:

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.

June 23, 2010 4:59 PM
 

Dan said:

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.

June 23, 2010 5:07 PM
 

Adam Machanic said:

Thanks, Dan! Off to update Who is Active...

June 23, 2010 5:30 PM
 

James Luetkehoelter said:

Nice!

June 23, 2010 9:38 PM
 

Brian Tkatch said:

Ooh, the strike tag. Worth it to read just for a good use of that. :)

June 24, 2010 7:28 AM
 

Mark Broadbent aka said:

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.

June 24, 2010 8:25 AM
 

Adam Machanic said:

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.

June 24, 2010 10:59 AM
 

Peter said:

Yes, hostname is easily spoofable.

sqlcmd -E -Sservername -H ""

June 25, 2010 7:00 AM
 

David Walker said:

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

June 26, 2010 4:06 PM
 

Mark Broadbent said:

Good point Adam, ok thanks will make alterations to my utility procs.

June 29, 2010 7:47 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement