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.

 

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

5 Responses

  1. Kalen Delaney says:

    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

  2. cinahcaM madA says:

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

  3. Michael K. Campbell says:

    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.

  4. AaronBertrand says:

    Adam, they also added open_transaction_count.

  5. cinahcaM madA says:

    Finally! Now where is tran_count???