There is a saying amongst welfare agencies that one can tell how well a family is functioning by looking at their dog. If the dog is neurotic, neglected or maltreated, one fears for the welfare of the children. Likewise, you can tell a lot about the skills of a team of DBAs and developers by looking at the tempdbs on their servers.

The tempdb database is available to all users of a SQL Server instance to house temporary objects such as cursors and tables, and it is where SQL Server creates various internal objects for sorting and spooling operations, as well as index modifications. It can get really busy in there, especially if there are unruly processes. The wise DBA will look after tempdb, giving it plenty of space, making sure it is never mistreated. In short, a happy, moist-nosed tempdb is the mark of a nurturing DBA.

By default, tempdb will be installed, with the other system databases, on the c: drive of the SQL Server machine. This is far from ideal in almost all cases. Tempdb requires a lot of space, pre-allocated so files aren’t constantly growing. You need more than one tempdb data file; one data file per CPU core is a common recommendation. These files need to be located on drives with the highest write performance possible; a RAID 10 array is a good choice. Also, tempdb storage is one area where Solid State Disks are becoming a popular storage choice in preference to conventional magnetic drives, again due to their vastly higher write performance.

Of course, not all DBAs can afford the luxury of RAID 10 arrays and expensive SSDs. In such cases, special training and vigilance is required. The developers, who exercise the dog, must be encouraged away from complex, unwieldy routines. Structured activity is best, breaking down routines into a series of well-defined steps, and storing intermediate results in a set of explicit temporary tables. In this way, tempdb usage patterns become much easier to predict. Also these tables will hopefully be cached, and the bigger ones can be indexed, both of which will help reduce contention. The DBA must diligently monitor the health of tempdb, detecting the SPIDs of wild processes, investigating and killing them if necessary (the SPIDs, not the developers).

Even given all this, one can’t help feel that Microsoft could do more to prevent a lot of tempdb agony. After all, it feels like an area that’s going to get worse rather than better, especially as use of Snapshot isolation becomes more prevalent. Aaron Bertrand has filed several tempdb-related Connect items urging Microsoft to offer better advice during the installation process, but to little avail. Judging by the number of forum questions relating to the often-painful process of moving tempdb and reallocating disk space, more action is needed.

Some early adopters have their eyes on the new Contained Databases feature in Denali, hopeful that the concept of user databases independent of “plumbing features” like logins and roles may be a move toward selective workspaces for user processes.

In the meantime, how healthy is the dog in your server? If you’ve got tales of exemplary nurturing or scandalous abuse, we’d love to hear them. The best story will, as always, win a prize.

Cheers,

Tony.