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 'denali', 'DMVs', and 'TempDB'

Connect Digest : 2011-05-09