THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Geek City: Why I still need Sysprocesses

I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular the new Dynamic Management Objects. When I have to do troubleshooting on a SQL Server 2000 system, it is worse than painful, not to have my favorite tools like sys.dm_tran_locks, sys.dm_exec_cached_plans and
sys.dm_exec_query_plan. By now, on the eve of the release of the version AFTER SQL Server 2005, my transition to the new metadata is almost complete.

With one notable exception...

I have always used sysprocesses constantly for troubleshooting information. Although most of the useful information that allows me to see what sessions are using lots of memory, or performing lots of I/O, can found in sys.dm_exec_sessions, there is one piece of information that isn't there. Sysprocesses contains a columns called open_tran which reflects the transaction  nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management. I use this value all the time, and once had to troubleshoot a system where dozens of connections had open_tran values in the 20s and 30s! (For more details about nesting transactions, see my earlier post.

So imagine my surprise when I discovered that the sys.dm_exec_sessions view, which is supposed to 'replace' sysprocesses in SQL Server 2005, has no column to provide this information!  Another view, sys.dm_exec_requests, has a column called open_transaction_count, which you might think would be the same thing. And it actually is the same information, but the sys.dm_exec_requests view only returns rows for sessions that are currently active, i.e. currently running a query. The need for examining the open_tran value is greatest when looking at those sessions that aren't doing anything, but are just sitting there with an open transaction. Those are the processes that you need to troubleshoot. I had felt that the omission of open_tran (or open_transaction_count) from sys.dm_exec_sessions was just an oversight, and it would be 'fixed' in the next release. But it appears I am to be disappointed. I just checked sys.dm_exec_sessions in RC0, and there is still no open_transaction_count column.

So long live sysprocesses!

~Kalen

Published Sunday, June 29, 2008 9:48 PM by Kalen Delaney
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

 

AaronBertrand said:

While you can't get information about active transactions directly from dm_exec_requests, can't you see the data in sys.dm_tran_session_transactions?  This should covert the case when the session isn't represented in dm_exec_requests...

The only thing I still find missing from the DMVs is the database.  There are several other places to get this, but as you have found, it is conditional on whether there is currently a request, whether there is currently a transaction open, etc.  Most of my sp_who replacements still use sysprocesses as a fallback for this.

June 30, 2008 12:55 AM
 

Kalen Delaney said:

Thanks Aaron. Yes, performing a join will show information about sessions with open transactions, but it won't show me which ones have deeply nested BEGIN TRANs.  

June 30, 2008 2:00 AM
 

Greg Low said:

Hi Kalen,

I find I can now get pretty much everything I wanted in the compatibility views (like sysprocesses) in the DMVs but these older views certainly provide it in a useful format.

Regards,

Greg

June 30, 2008 5:09 AM
 

AaronBertrand said:

Ah, ok.  I do see there isn't an easy way to get the count in the case of nested transactions.  I thought I would see multiple rows in one of the dm_tran_*_transactions views, but that is not the case.

June 30, 2008 10:39 AM
 

Jason said:

I keep coming back for maybe a wrong reason. But somehow I don't seem to be able to find the database id or name associated with a seesion/connection using sys.dm_exec_sessions or _connections. Again, the _requests dmv only returns active queries/transactions, it would not help with the db_id.

Is there a way to get this info out of the dmv's?

July 1, 2008 2:55 PM
 

AaronBertrand said:

Jason, it's in a few other places.  In this article:

http://sqlserver2005.databases.aspfaq.com/better-sp-who2.html

I show that you can look in at sys.dm_tran_database_transactions.database_id, which you can join back to dm_exec_sessions through sys.dm_tran_session_transactions.transaction_id.

You can also look in sys.dm_tran_locks.resource_database_id, which you can jon back to dm_exec_sessions using request_session_id.

In many scenarios you can find out the database based on locks that are held, blocking, or active transactions, even when a request is not currently active.

July 1, 2008 11:46 PM
 

Kalen Delaney said:

Thanks for following up on this Aaron. I usually use sys.dm_tran_locks to get this info, since usually when I'm exploring connections, I'm also exploring locks.

July 2, 2008 1:59 PM
 

Patrick Fowler said:

You can see the open transaction without typing, its in the "Activity Monitor" you can launch from SSMS. After reading the above, I ran profiler to see the SQL behind this, and surprise surprise, it too uses Sysprocesses (with a non-ansii join to boot).

July 3, 2008 3:00 PM
 

jeet said:

Hi Kalen,

Need your expertise in my nightmare! I am in the middle of database restore (which i initiated through one application)...now it does not stop - i tried restarting server and all - but no luck, what i should do? I am using SQL Express 2005...plz answer me at havejeet@gmail.com

thanks,

Jeet

October 17, 2008 1:03 AM
 

Kalen Delaney said:

Hi Jeet

This is not a support forum. If you need help, you should post to the public newsgroups.

~Kalen

December 5, 2008 6:50 PM
 

puneet jain said:

hi kalen,

there is a column call kpid in sys.sysprocesses table. i am using this a a unique key because even though there are parallel queries kpid helps to distinguish.

what is the alternative using dmv's in 2005/08

January 26, 2009 10:12 PM
 

Vinay said:

Did you get an answer for replacement of KPID replacement via DMV.

April 13, 2009 6:41 PM
 

Slaven Sola said:

Dear Kalen,

I have one question.

Could you please explain me what does it mean, I see READTEXT inside CMD column in sysprocesses.

I see that when I recompile SP and then recompile take about 11 minutes.

Thank you in advance.

June 8, 2009 6:28 AM
 

dennis said:

In response to the comment concerning KPIDs see sys.dm_os_threads for os_thread_id.

December 15, 2009 11:40 AM
 

John "Z-Bo" Zabroski said:

Doesn't Adam Machanic's sp_WhoIsActive script take care of this problem?

August 4, 2011 1:31 PM
 

Kalen Delaney said:

Hi John

I don't always have Adam's procedure immediately available, when I am teaching a class or at a customer site. If sp_whoIsActive gives you this info, great! I also like to know how I can get it easily from the always available metadata.

Thanks

Kalen

August 4, 2011 3:01 PM
 

John "Z-Bo" Zabroski said:

Get it easily by carrying a USB stick around with you.  It's really that simple.

sp_WhoIsActive is my swiss army knife.  I use it more than any other tool, and it really does cover 90% of my problems.  Adam did a tremendous job, and it is open source, so if you just want to learn what he is doing for open transactions, read through the code!

August 5, 2011 1:59 PM
 

Luke Campbell said:

John, the latest version of sp_WhoIsActive still uses sysprocesses.  See lines 1331 and 2016 in the latest version.  I recently ran into this when looking to re-write my own script to find root blockers but haven't found a way to not use sysprocesses yet as Kalen describes.  sp_WhoIsActive is an amazing piece of code!

January 6, 2012 12:49 PM
 

Olivier MAITRE said:

Hello Kalen,

I've noticed that open_transaction_count of sys.dm_exec_requests doesn't give the same number than open_tran from sys.processes. It's visible when you open lot of transactions...

any explanation ?

Thank's a lot,

O.

April 12, 2013 4:23 AM
 

K.S. Subramanian said:

While an update statement is executing, sysprocesses.open_tran appears to have a value of 1 more than the number of 'begin tran' that has been issued. For example,

BEGIN TRAN

-- sysprocesses.open_tran will show 1 at this point

UPDATE <sometable, large # of rows to ensure statement takes a few secs> -- if you check sysprocesses.open_tran while this is executing, it will show 2; once the statement completes it shows 1.

July 2, 2013 1:46 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

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