Thursday, May 19, 2011

Back to the Drawing Board

Like many DBAs, I have a variety of scripts I use to check on the health of my servers.  Some are borrowed, others stolen, and a few I have written myself.  A common theme amongst them is the need to filter either user or system processes depending on the situation.

Filtering User Processes

Typically I have done this by adding a where clause which filters the results on session_id.

[sql]

-- incorrect method to filter for user sessions
select *
from sys.dm_exec_sessions
where session_id > 50

-- incorrect method to filter for user requests
select *
from sys.dm_exec_requests
where session_id > 50

[/sql]

You see, I have always been taught that system processes will always have a session_id of one through fifty, while user processes will always be greater than fifty.

Recently, I had the opportunity to work with a Microsoft engineer while troubleshooting some systems.  As we were running through my scripts he mentioned that this was no longer the case; system SPIDs can exist above fifty.  When I asked him how I should go about filtering user and system processes, he said he did not know of a way.

System Processes Have SPIDs Above 50

I was crestfallen to say the least.  Here I was, using an outdated technique and thinking I was cool. However, I was sure there must be a way to achieve the results I was looking for.

As luck would have it, the following week I was taking a SQLskills class with Paul Randal (b | t). During one of the breaks, I asked him about this and if he knew of a new method for filtering user and system processes.  True to form, he did some research, reached out to one of his contacts at Microsoft and had an answer for me within the hour.

New way?  What's this about a new way?

Apparently, this behavior has existed since SQL 2005 but is not widely known.  The correct way to filter user processes is to query sys.dm_exec_sessions and filter on the is_user_process column.

[sql]

-- list user processes
select *
from sys.dm_exec_sessions
where is_user_process = 1

-- list system processes
select *
from sys.dm_exec_sessions
where is_user_process = 0

[/sql]

So, in order to leverage this in other DMVs, you will need to use a join clause.  My updated code looks like this...

[sql]

-- correct method to filter user requests
select der.*
from sys.dm_exec_requests der
join sys.dm_exec_sessions des
on der.session_id = des.session_id
where des.is_user_process = 1

[/sql]

An Exercise for the Writer

It always amazes me how long you can use a product and still stumble upon 'new' features at any moment.  So now, I will be reviewing my existing scripts and making updates where appropriate.

1 comment: