The SQL Server tempdb Performance Issues

What Is tempdb?

tempdb is a database that is used by all users of SQL Server. Anyone can create objects in tempdb. Many processes use it silently. It is a server-wide resource that is used primarily for:

          • Internal processing of sorts, creating worktables, reformatting,and so on
          • Storing temporary tables and indexes created by users

Many applications use stored procedures that create tables in tempdb to expedite complex joins or to perform other complex data analysis that is not easily performed in a single step.

How Can tempdb Affect Performance?

Good management of tempdb is critical to the overall performance of SQL Server. tempdb cannot be overlooked or left in a default state. It is the most dynamic database on many servers, and should receive

special attention. If planned for in advance, most problems related to tempdb can be avoided. These are the kinds of things that can go wrong if tempdb is not sized or placed properly:

• tempdb fills up frequently, generating error messages to users who must resubmit their queries when                        space becomes available.
• Sorting is slow, and users do not understand why their queries have such uneven performance.
• User queries are temporarily locked from creating temporary tables because of locks on system tables.
•  Heavy use of tempdb objects flushes other pages out of the data cache.

Main Solution Areas for tempdb Performance

These main areas can be addressed easily:

•  Sizing tempdb correctly for all SQL Server activity
•  Placing tempdb optimally to minimize contention
• Binding tempdb to its own data cache
• Minimizing the locking of resources within tempdb

Types and Use of Temporary Tables

The use or misuse of user-defined temporary tables can greatly affect the overall performance of SQL Server and your applications. Temporary tables can be quite useful, often reducing the work the server has to do. However, temporary tables can add to the size requirement of tempdb. Some temporary tables are truly temporary, and others are permanent. tempdb is used for three types of tables:

•  Truly temporary tables
•  Regular user tables
• Worktables

Truly Temporary Tables

You can create truly temporary tables by using “#” as the first

character of the table name:

               create table #temptable (...)

 or:

                    select select_list

                        into #temptable ...

Temporary tables:

• Exist only for the duration of the user session or for the scope of the procedure that creates them
•  Cannot be shared between user connections
• Are automatically dropped at the end of the session or procedure (or can be dropped manually)

When you create indexes on temporary tables, the indexes are stored

in tempdb:

                  create index tempix on #temptable(col1)