July 19, 2011 | SQL Server

SQL Server v.Next (Denali) : Breaking change to sys.dm_exec_sessions

If you're not OCD about prefixing every single column in a query, this is one that might bite you. Today you might have code that assumes that, because database_id is in sys.dm_exec_requests but not in sys.dm_exec_sessions, you don't need to prefix database_id in the following example:

SELECT TOP (1)
   s.session_id,
   database_id
FROM 
   sys.dm_exec_sessions AS s
LEFT OUTER JOIN 
   sys.dm_exec_requests AS r
   ON s.session_id = r.session_id;

In SQL Server 2005, 2008 and 2008 R2, the above code works perfectly fine, because the server can determine quite easily that the database_id column comes from the sys.dm_exec_requests DMV. However, if you run this same code on Denali CTP3, you get the following error:

Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'database_id'.

Why is that? Well, we're finally getting indication of some database context even when the session is idle. Back in SQL Server 2005, they deprecated the sysprocesses system table in favor of the new DMVs sys.dm_exec_sessions, sys.dm_exec_sessions and sys.dm_exec_requests, though they made a compatibility view called sys.sysprocesses. They couldn't eliminate sysprocesses immediately because of the volume of code that it would break; and they quickly found that they couldn't eliminate it in the longer run because some crucial information was missing (see Connect #257502 for more info). In sys.sysprocesses you can see database context, for example, in the dbid column; in sys.dm_exec_sessions, there is no equivalent. There is a database_id column in the sys.dm_exec_requests DMV, but this only helps you get database context information for sessions with an active request.

Now in Denali, we have some new columns in sys.dm_exec_sessions, including database_id. This allows us to determine database context even for a session that currently doesn't have an active request; but it comes at the potential cost of breaking code, as demonstrated above. To make the above code sample work, you'll need to properly prefix the database_id column, maybe even with a COALESCE:

SELECT TOP (1)
   s.session_id,
   database_id = COALESCE(r.database_id, s.database_id)
FROM 
   sys.dm_exec_sessions AS s
LEFT OUTER JOIN 
   sys.dm_exec_requests AS r
   ON s.session_id = r.session_id;

There are some other interesting columns that have been added, such as authenticating_database_id – which will allow you to determine the original database even in cases where ORIGINAL_DB_NAME() is not available. This one warrants some testing and further treatment in a future blog post.

Now don't get me wrong, database_id is a fantastic and welcome addition – I can stop looking at sys.sysprocesses to determine the database for sessions that aren't currently running a query. But if you're not currently in the habit of properly qualifying column names throughout your queries, here's one more reason you should start. This is another one of those "maybe breaking changes" that is almost certainly not going to show up using the Upgrade Advisor or Best Practices Analyzer, so it can be a real problem if the first time you see it is after you upgrade.

 

5 comments on this post

    • cinahcaM madA - July 19, 2011, 5:18 PM

      Finally! Now where is tran_count???

    • AaronBertrand - July 19, 2011, 7:09 PM

      Adam, they also added open_transaction_count.

    • Michael K. Campbell - July 19, 2011, 7:31 PM

      Wow. That's just plain huge. I've been waiting on this FOREVER. (Found myself waayyy to frequently going back to sysprocesses for idle connections and for orphaned/opened/leaking transactions. Can't wait to try this out later today.)
      Great Post.

    • cinahcaM madA - July 20, 2011, 12:15 AM

      Even better news! Who is Active will almost certainly get a huge overhaul for Denali…

    • Kalen Delaney - July 20, 2011, 12:24 AM

      Great news about tran_count! Thanks for letting us know. (I was just about to ask this myself but Adam beat me to it. 🙂 )
      Also, I appreciate the very good explanation of why qualifying your column names is always a good idea. I've had this problem when writing a single table/view query, and then realize later I want to join it with something else… and it breaks because of a duplicate column name. So even if you have just one object in a query, you never know what you're going to use the query for tomorrow, so just add that extra info!
      Thanks again
      Kalen

Comments are closed.