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

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

Phil Brammer — Wed, 23 Jun 2010 16:25:14 GMT

+1. Thanks for airing this out.

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

Amit Banerjee — Wed, 23 Jun 2010 16:35:06 GMT

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

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

mjswart — Wed, 23 Jun 2010 18:21:44 GMT

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

I've got to remember hostname > ''

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

Mateus — Wed, 23 Jun 2010 19:19:52 GMT

thanks for this tip ;)

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

noeldr — Wed, 23 Jun 2010 19:33:52 GMT

Great tip!

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

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

Dan — Wed, 23 Jun 2010 19:35:30 GMT

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.

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

Adam Machanic — Wed, 23 Jun 2010 20:54:04 GMT

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.

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

Adam Machanic — Wed, 23 Jun 2010 20:59:26 GMT

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.

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

Dan — Wed, 23 Jun 2010 21:07:42 GMT

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.

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

Adam Machanic — Wed, 23 Jun 2010 21:30:22 GMT

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

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

James Luetkehoelter — Thu, 24 Jun 2010 01:38:19 GMT

Nice!

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

Brian Tkatch — Thu, 24 Jun 2010 11:28:36 GMT

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

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

Mark Broadbent aka — Thu, 24 Jun 2010 12:25:26 GMT

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.

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

Adam Machanic — Thu, 24 Jun 2010 14:59:11 GMT

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.

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

Peter — Fri, 25 Jun 2010 11:00:35 GMT

Yes, hostname is easily spoofable.

sqlcmd -E -Sservername -H ""

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

David Walker — Sat, 26 Jun 2010 20:06:16 GMT

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

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

Mark Broadbent — Tue, 29 Jun 2010 11:47:33 GMT

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