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
 

Liam Gavin said:

Hi Kalen,

Are you still seeing same problem with pulling out open transaction count using DMVs rather than sysprocesses? I have noticed exactly the same thing and it is pretty annoying as in most cases the DMVs are much quicker.

I wrote a dashboard sproc a few years back (been meaning to blog it for some time) and the only reliable way I found was to use dbcc opentran and loop round like this to associate the transactions with the DB. All a bit of a fiddle and I would love to be able to put it in a DMV and know all open transactions are being picked up.

CREATE TABLE #OpenTran (Defn VARCHAR(25), Details SQL_VARIANT, Database_id INT NULL, id int identity(1,1))

DECLARE @command VARCHAR(4000)

SET @command = 'dbcc opentran([?]) WITH TABLERESULTS'

INSERT INTO #OpenTran (Defn , Details )

EXEC sp_MSforeachDB @command

--Get dbid for dbcc opentran results - not the most efficient but need to use DBCC OPENTRAN to get oldetst tx reliably

UPDATE T

SET Database_id = S.dbid

FROM #OpenTran T

JOIN master.dbo.sysprocesses S

ON CONVERT(INT, REPLACE(CONVERT(VARCHAR(10), Details), 's', '')) = spid

WHERE Defn = 'OLDACT_SPID' --If no long spids then this join will return blanks

AND spid <> @@SPID --ignore your process

--Populate Database_id for other rows in transaction

UPDATE T1

SET Database_id = (SELECT TOP 1 Database_id FROM #OpenTran T2 WHERE T2.id < T1.Id AND Defn = 'OLDACT_SPID' ORDER BY id DESC)

FROM #OpenTran T1

WHERE Defn <> 'OLDACT_SPID'

Thanks, Liam

August 25, 2014 12:05 PM
 

Liam Gavin said:

ps we are using SQL 2008 R2

August 25, 2014 12:06 PM
 

Liam Gavin said:

I am going to go with this for my queries from now on for picking up any Tx older than 30s (an age already I know but I don't want this to be too sensitive), I would love to hear back if you think there are transactions that would be picked up by sysprocesses or opentran that would not be picked up by below. The other question would be if #2 will always pick up the same as #1, a left join to sys.databases might be necessary but like the whole point of this post - it would be great to nail it and know for sure!

--1. Without identifying database

SELECT COUNT(*)

FROM sys.dm_tran_active_transactions T

JOIN sys.dm_tran_session_transactions S

ON S.transaction_id = T.transaction_id

WHERE transaction_begin_time < DATEADD(MS, -30000, GETDATE())

--2. With identifying database

SELECT d.name, COUNT(*) as Tx, MIN(transaction_begin_time) as Earliest

FROM sys.dm_tran_active_transactions tat

INNER JOIN sys.dm_exec_requests er

ON tat.transaction_id = er.transaction_id

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)

JOIN sys.databases d

ON d.database_id = er.database_id

WHERE transaction_begin_time <=  DATEADD(MS, -30000, GETDATE())    

GROUP BY d.name

August 26, 2014 12:10 PM
 

#ChateadaDMV | Comunidade SQL Server said:

October 14, 2014 10:50 AM

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