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.

Connect Digest : 2011-05-09

AaronBertrand — Mon, 09 May 2011 11:27:00 GMT

This week we're going to look at some issues involving tempdb...

Provide a tempdb per database

#176241 : Eliminating TempDb By Adding Temp Filegroup to Each Database
#281202 : multiple tempdb
For these two requests, I am hoping that the Contained Databases feature (which I've talked about multiple times) will get to this capability in the version that follows Denali. They've taken care of the collation issue, but it would be nice to be able to set up different tempdb file / filegroup structures for databases that have different demands, and be able to move those files easily - along with the user database - without disrupting the rest of the system, or requiring a "one size fits all" mentality for tempdb.

Tempdb on local disk for clusters

#488725 : Creating TempDB On Local drives for clusters
#532759 : Support local disk location for TempDB in failover cluster installation
I found it curious that one of these items is marked as "fixed" and the other is marked as "won't fix" - both with no comment. The capability is in Denali CTP1, but as a disclaimer, that doesn't mean it will pass all testing and make it into the final Denali release. If it is (or is not) intended to be in the Denali release, it would be great to get both of these items closed out properly with an official comment from Microsoft.

Place version store elsewhere

#648100 : ability to put the row version store somewhere other than in tempdb

Chris Adkin (twitter) asks for the ability to place version store data into a dedicated filegroup for a user database, instead of putting the version store for all databases into tempdb.

Allow tempdb to bypass model inheritance

#415343 : Enhancements to model database
This was a suggestion for a few enhancements to the model database, but one specific item was: "Allow tempdb to bypass inheriting objects and data from model. I use model as a template for new databases and in some cases tempdb inherits a lot of schema and data that will never be used ... this can affect startup time on a reboot or failover." I'm not sure if I should file a separate Connect item for this suggestion alone. Thoughts anyone?

Do not track temp table creation in default trace

#542102 : The default trace should not include creation of temp tables
Erland Sommarskog described a trace on one of his systems that was very heavily weighted toward the creation of #temp tables. I agree with him that there should be a way to configure the default trace to not bother logging certain types of operations. That said, any suggestions about tracing in any form will likely be routed to the "use XEvents instead" department.

Fix bad suggestions about tempdb log files

#643742 : Remove the recommendation of creating multiple log files for tempdb to improve performance
Sankar Reddy (blog | twitter) noted that in this MSDN topic (admittedly, written for SQL Server 2005), it has a sample with a really bad idea: "This example creates two additional data files for tempdb, each with an initial size of 8 MB, and two log files with an initial size of 1 MB." In addition to scoffing at the recommendation to add log files to improve performance, I'd like to add that creating two additional data log files at only 8 MB (and presumably leaving the autogrowth rate at 10%) has very little chance of having a positive impact on performance. In many cases the results will be even worse - three tiny data files for tempdb? and three even tinier log files? really? Clearly the example was written by someone who assumed that I/O works differently on data files and log files. The only upside to this is that I couldn't find an equivalent document for SQL Server 2008, 2008 R2 or Denali, so they aren't continuing to recommend this bad practice - but this article is still out there for people to "learn" from. Kevin Kline (blog | twitter) also commented on this issue in a blog post in 2009. I find it funny that they fixed his observation about calling SQL Server 2005 "SQL Server 9.0" but didn't bother fixing the code sample.

Stop defaulting all data/log files to C:\

#338763 : Setup : Encourage better practice for db/log locations
I filed this issue because I found it alarming that, even on a system with gobs and gobs of disks on other drives, all system and user data and log file locations (including tempdb) were pre-populated with C:\Program Files\... If C:\ is the only drive letter found (e.g. on a virtual machine or a workstation), I think the default is fine; however, if other drive letters are found, I think it makes more sense to either prompt for a more obvious choice or to leave the values blank. The time it takes during installation to choose these locations by putting some actual thought into it is much preferred over the time it will take later to correct those choices during a production emergency.

Search results matching tags 'Best Practices' and 'denali'

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

Connect Digest : 2011-05-09