Temporary Tables and Table Variables in Microsoft SQL Server 2008 T-SQL Programming

9/23/2009

Contents

tempdb Considerations

Remember that temporary tables and table variables are physically stored in tempdb. SQL Server also stores data in tempdb for many implicit activities that take place behind the scenes. Examples for such activities include: spooling data as part of an execution plan of a query, sorting, hashing, and maintaining row versions. You realize that tempdb can become a bottleneck, and you should give it focused tuning attention so that it will accommodate the workload against your server.

Here are some important points you should consider when tuning tempdb:

In systems where tempdb is heavily used (explicitly or implicitly), consider placing tempdb on its own disk array, and not on the same drives where other databases are located. Also, stripe the data portion to multiple drives to increase I/O throughput. The more spindles, the better. Ideally, use RAID 10 for the data portion and RAID 1 for the log.
Every time you restart SQL Server, tempdb is re-created, and its size reverts to the effective defined size. If you made no changes to the original size configuration after installing SQL Server, tempdb’s size will default to 8 MB and its growth increment will default to 10 percent. In most production environments, these values might not be practical. Whenever a process needs to store data in tempdb and tempdb is full, SQL Server will initiate an autogrow operation. The process will have to wait for the space to be allocated. Also, when the database is small, 10 percent is a very small unit. The small fragments will most probably be allocated in different places on disk, resulting in a high level of file-system fragmentation. And if that’s not enough, remember that every time SQL Server restarts, tempdb’s size will revert to its defined size (8 MB). This means that the whole process will start again, where tempdb will keep on autogrowing until it reaches a size appropriate to your environment’s workload. Until it reaches that point, processes will suffer as they wait while tempdb autogrows.
You can figure out the appropriate size for tempdb by observing its actual size after a period of activity without restarts. You then alter the database and change the SIZE parameter of tempdb’s files so that tempdb’s size will be appropriate. Whenever SQL Server is restarted, tempdb will just start out at the defined size. If you do this, there won’t be a need for autogrowth until tempdb gets full, which should occur only with irregular and excessive tempdb activity.
Remember that logically tempdb is re-created whenever SQL Server restarts. Like any other new database, tempdb is created as a copy of the model database. This means that if you create permanent objects in tempdb (permanent tables, user-defined types, database users, and so on), they’re erased in the next restart. If you need objects to exist in tempdb after restarts, you have two options. One is to create them in model. They will appear in tempdb after a restart. However, this option will also affect new user databases you create. Another option is to encapsulate code that creates all objects in a startup procedure. (See information on startup procedures earlier in the chapter in the Global Temporary Tables section.) Remember that a startup procedure is invoked whenever SQL Server is restarted. Essentially the objects will be re-created every time upon restart, but this will be invisible to users.
With regard to temporary tables, obviously dealing with very large volumes of data can cause performance problems. However, you might face performance problems with tempdb even when working with small temporary tables. When many concurrent sessions create temporary tables, SQL Server might experience latch contention on allocation bitmaps when it tries to allocate pages. In the last couple of versions of SQL Server this problem was reduced substantially because of improvements in the engine—caching of temporary objects across routine calls—and improvements in the proportional fill algorithm SQL Server uses. Still, the problem may occur. The recommended practices to mitigate the problem are to use multiple data files for tempdb (as a general rule of thumb, one file per each CPU core), and to meet the requirements described earlier that would allow caching of temporary objects across routine calls.