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!