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

AaronBertrand — Tue, 19 Jul 2011 11:22:00 GMT

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.

Search results matching tags 'DMVs', 'good habits', 'SQL Server "Denali"', and 'denali'

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