Metadata for Troubleshooting

As I mentioned last week, it’s not always easy coming up with a topic for my weekly commentary, and I sometimes do a search for SQL Server news online to see what the hot topics currently are. However, that strategy doesn’t work if I can’t get online.  I'm currently on holiday in the mountains in California, and my Internet access is spotty at best. There are certain websites I can’t even get to, not because they're blocked for any reason, but because it takes far too long to open them. I can’t even check out the latest news about the SQL Server 2008 release to manufacturing (RTM), which was scheduled for July 31, because the websites I usually check for this kind of internal Microsoft information are among the websites that I can’t get to. So by next week, we should all know for sure what’s happening with the SQL Server 2008 RTM. This week, I'm going to discuss a technical topic instead.

I've said many times that my favorite feature in SQL Server 2005 is the metadata, in particular the Dynamic Management Objects (DMVs). I try to avoid having to troubleshoot problems with SQL Server 2000 systems because they don't have my favorite tools, such as sys.dm_tran_locks, sys.dm_exec_cached_plans, and sys.dm_exec_query_plan. Although it has taken me quite a while to get used to SQL Server 2005's metadata, I realize now, on the eve of the release of SQL Server 2008, that my transition to SQL Server 2005's metadata is almost complete. However, there's one notable exception in which I find I still need to use an old object instead of a new one.  That object is sysprocesses, which has always been one of my favorite metadata objects, and I use it extensively when troubleshooting. I even wrote a series of articles for SQL Server Magazine about sysprocesses in July, August, and September 2003. One of the most interesting things about sysprocesses prior to SQL Server 2005 was that it was one of only a few objects that were referred to as pseudo-tables. In SQL Server 2000 and earlier, sysprocesses looked and acted like a system table, but it didn’t take up any disk space. In fact, the data in this pseudo-table was never written to (or read from) your disk. The data in sysprocesses was drawn from internal system information and presented to you in tabular format, but underneath, it wasn’t really a table. This pseudo-table was actually the forerunner to the DMVs in SQL Server 2005.

So why is sysprocesses an exception to my exclusive use of SQL Server 2005's metadata? Although most of the useful information that lets me see which sessions are using lots of memory or performing lots of I/O can be found in sys.dm_exec_sessions, so I don’t need to refer to sysprocesses, there's one piece of information that isn't there. Sysprocesses contains a column called open_tran, which reflects the transaction nesting level of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, the session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 could mean that a transaction is holding locks and blocking other processes, or that the open transaction might be keeping the transaction log from being cleared. If you ever see open_tran values higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management. I examine this sysprocesses value all the time and once had to troubleshoot a system in which dozens of connections had open_tran values in the 20s and 30s!

So I find it very frustrating that the sys.dm_exec_sessions view, which supposedly replaces sysprocesses in SQL Server 2005, doesn't have a column to provide this information.  Another view, sys.dm_exec_requests, has a column called open_transaction_count, which you might think would offer the same information as the open_tran column in sysprocesses. Although it's the same information, the sys.dm_exec_requests view returns only rows for sessions that are 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 rather are just sitting there with an open transaction. Those are the processes that you need to troubleshoot.

You can take a look at another SQL Server 2005 DMV called sys.dm_tran_session_transactions to see which sessions have open transactions, but all you can find out is if there's a transaction in progress, not how deeply nested it is. And for troubleshooting serious problems with code, noting a deep nesting level is vital to finding the solution.

I had thought that the omission of the open_tran (or open_transaction_count) column from sys.dm_exec_sessions was just an oversight and would be "fixed" in the next release. But it appears I'm going to be disappointed. In SQL Server 2008 RC0, there still isn't an open_transaction_count column. I'm eagerly awaiting SQL Server 2008 RTM to see if maybe the SQL Server team added it at last minute. In fact, I'm fantasizing that this is the reason the product was delayed past July 31.