Home

	Expand Search

Performance and Tuning Guide Volumes 1 - 3 (Online Only)

Chapter 18 tempdb Performance Issues

Chapter 18

tempdb Performance Issues

This chapter discusses the performance issues associated with using the tempdb database. tempdb is used by Adaptive Server users. Anyone can create objects in tempdb. Many processes use it silently. It is a server-wide resource that is used primarily for internal sorts processing, creating worktables, reformatting, and for 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 management of tempdb affects performance

Good management of tempdb is critical to the overall performance of Adaptive 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 then 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 Adaptive Server activity

• Placing tempdb optimally to minimize contention

• Binding tempdb to its own data cache

• Minimizing the locking of resources within tempdb

Types and uses of temporary tables

The use or misuse of user-defined temporary tables can greatly affect the overall performance of Adaptive 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)

Regular user tables

You can create regular user tables in tempdb by specifying the database name in the command that creates the table:

create table tempdb..temptable (...)

or:

select select_list    into tempdb..temptable

Regular user tables in tempdb:

• Can persist across sessions

• Can be used by bulk copy operations

• Can be shared by granting permissions on them

• Must be explicitly dropped by the owner (otherwise, they are removed when Adaptive Server is restarted)

You can create indexes in tempdb on permanent temporary tables:

create index tempix on tempdb..temptable(col1)

Worktables

Worktables are automatically created in tempdb by Adaptive Server for merge joins, sorts, and other internal server processes. These tables:

• Are never shared

• Disappear as soon as the command completes

Initial allocation of tempdb

When you install Adaptive Server, tempdb is 2MB, and is located completely on the master device, as shown in Figure 18-1. This is typically the first database that a System Administrator needs to make larger. The more users on the server, the larger it needs to be. It can be altered onto the master device or other devices. Depending on your needs, you may want to stripe tempdb across several devices.

Use sp_helpdb to see the size and status of tempdb. The following example shows tempdb defaults at installation time:

sp_helpdb tempdb

name      db_size  owner  dbid   created     status
--------- -------- ------ ------ ----------- --------------------
tempdb    2.0 MB   sa     2     May 22, 1999 select into/bulkcopy

device_frag  size    usage        free kbytes
------------ -------- ------------ ---------
master       2.0 MB  data and log 1248

Sizing the tempdb

tempdb needs to be big enough to handle the following processes for every concurrent Adaptive Server user:

• Worktables for merge joins

• Worktables that are created for distinct, group by, and order by, for reformatting, and for the OR strategy, and for materializing some views and subqueries

• Temporary tables (those created with "#" as the first character of their names)

• Indexes on temporary tables

• Regular user tables in tempdb

• Procedures built by dynamic SQL

Some applications may perform better if you use temporary tables to split up multitable joins. This strategy is often used for:

• Cases where the optimizer does not choose a good query plan for a query that joins more than four tables

• Queries that join a very large number of tables

• Very complex queries

• Applications that need to filter data as an intermediate step

You might also use tempdb to:

• Denormalize several tables into a few temporary tables

• Normalize a denormalized table to do aggregate processing

For most applications, make tempdb 20 to 25% of the size of your user databases to provide enough space for these uses.

Placing tempdb

Keep tempdb on separate physical disks from your critical application databases. Use the fastest disks available. If your platform supports solid state devices and your tempdb use is a bottleneck for your applications, use those devices. After you expand tempdb onto additional devices, drop the master device from the system, default, and logsegment segments.

Although you can expand tempdb on the same device as the master database,Sybase suggests that you use separate devices. Also, remember that logical devices, but not databases, are mirrored using Adaptive Server mirroring. If you mirror the master device, you create a mirror of all portions of the databases that reside on the master device. If the mirror uses serial writes, this can have a serious performance impact if your tempdb database is heavily used.

Dropping the master device from tempdb segments

By default, the system, default, and logsegment segments for tempdb include its 2MB allocation on the master device. When you allocate new devices to tempdb, they automatically become part of all three segments. Once you allocate a second device to tempdb, you can drop the master device from the default and logsegment segments. This way, you can be sure that the worktables and other temporary tables in tempdb do not contend with other uses on the master device.

To drop the master device from the segments:

1. Alter tempdb onto another device, if you have not already done so. For example:

   alter database tempdb on tune3 = 20

2. Issue a use tempdb command, and then drop the master device from the segments:

   sp_dropsegment "default", tempdb, master

   sp_dropdegment system, tempdb, master

   sp_dropdegment logsegment, tempdb, master

3. To verify that the default segment no longer includes the master device, issue this command:

   select dbid, name, segmap
   from sysusages, sysdevices
   where sysdevices.low <= sysusages.size + vstart
     and sysdevices.high >= sysusages.size + vstart -1
     and dbid = 2
     and (status = 2 or status = 3)

   The segmap column should report "1" for any allocations on the master device, indicating that only the system segment still uses the device:

    dbid   name            segmap      
    ------ --------------- ----------- 
         2 master                    1 
         2 tune3                     7

Using multiple disks for parallel query performance

If tempdb spans multiple devices, as shown in Figure 18-2, you can take advantage of parallel query performance for some temporary tables or worktables.

Binding tempdb to its own cache

Under normal Adaptive Server use, tempdb makes heavy use of the data cache as temporary tables are created, populated, and then dropped.

Assigning tempdb to its own data cache:

• Keeps the activity on temporary objects from flushing other objects out of the default data cache

• Helps spread I/O between multiple caches

See "Examining cache needs for tempdb" for more information.

Commands for cache binding

Use sp_cacheconfig and sp_poolconfig to create named data caches and to configure pools of a given size for large I/O. Only a System Administrator can configure caches and pools.

Reference to Large I/Os are on a 2K logical page size server. If you have an 8K page size server, the basic unit for the I/O is 8K. If you have a 16K page size server, the basic unit for the I/O is 16K.

For instructions on configuring named caches and pools, see the System Administration Guide.

Once the caches have been configured, and the server has been restarted, you can bind tempdb to the new cache:

sp_bindcache "tempdb_cache", tempdb

Temporary tables and locking

Creating or dropping temporary tables and their indexes can cause lock contention on the system tables in tempdb. When users create tables in tempdb, information about the tables must be stored in system tables such as sysobjects, syscolumns, and sysindexes. If multiple user processes are creating and dropping tables in tempdb, heavy contention can occur on the system tables. Worktables created internally do not store information in system tables.

If contention for tempdb system tables is a problem with applications that must repeatedly create and drop the same set of temporary tables, try creating the tables at the start of the application. Then use insert...select to populate them, and truncate table to remove all the data rows. Although insert...select requires logging and is slower than select into, it can provide a solution to the locking problem.

Minimizing logging in tempdb

Even though the trunc log on checkpoint database option is turned on in tempdb, changes to tempdb are still written to the transaction log. You can reduce log activity in tempdb by:

• Using select into instead of create table and insert

• Selecting only the columns you need into the temporary tables

With select into

When you create and populate temporary tables in tempdb, use the select into command, rather than create table and insert...select, whenever possible. The select into/bulkcopy database option is turned on by default in tempdb to enable this behavior.

select into operations are faster because they are only minimally logged. Only the allocation of data pages is tracked, not the actual changes for each data row. Each data insert in an insert...select query is fully logged, resulting in more overhead.

By using shorter rows

If the application creating tables in tempdb uses only a few columns of a table, you can minimize the number and size of log records by:

• Selecting just the columns you need for the application, rather than using select * in queries that insert data into the tables

• Limiting the rows selected to just the rows that the applications requires

Both of these suggestions also keep the size of the tables themselves smaller.

Optimizing temporary tables

Many uses of temporary tables are simple and brief and require little optimization. But if your applications require multiple accesses to tables in tempdb, you should examine them for possible optimization strategies. Usually, this involves splitting out the creation and indexing of the table from the access to it by using more than one procedure or batch.

When you create a table in the same stored procedure or batch where it is used, the query optimizer cannot determine how large the table is, the table has not yet been created when the query is optimized, as shown in Figure 18-3. This applies to both temporary tables and regular user tables.

The optimizer assumes that any such table has 10 data pages and 100 rows. If the table is really large, this assumption can lead the optimizer to choose a suboptimal query plan.

These two techniques can improve the optimization of temporary tables:

• Creating indexes on temporary tables

• Breaking complex use of temporary tables into multiple batches or procedures to provide information for the optimizer

Creating indexes on temporary tables

You can define indexes on temporary tables. In many cases, these indexes can improve the performance of queries that use tempdb. The optimizer uses these indexes just like indexes on ordinary user tables. The only requirements are:

• The table must contain data when the index is created. If you create the temporary table and create the index on an empty table, Adaptive Server does not create column statistics such as histograms and densities. If you insert data rows after creating the index, the optimizer has incomplete statistics.

• The index must exist while the query using it is optimized. You cannot create an index and then use it in a query in the same batch or procedure.

• The optimizer may choose a suboptimal plan if rows have been added or deleted since the index was created or since update statistics was run.

Providing an index for the optimizer can greatly increase performance, especially in complex procedures that create temporary tables and then perform numerous operations on them.

Creating nested procedures with temporary tables

You need to take an extra step to create the procedures described above. You cannot create base_proc until select_proc exists, and you cannot create select_proc until the temporary table exists. Here are the steps:

1. Create the temporary table outside the procedure. It can be empty; it just needs to exist and to have columns that are compatible with select_proc:

   select * into #huge_result from ... where 1 = 2

2. Create the procedure select_proc, as shown above.

3. Drop #huge_result.

4. Create the procedure base_proc.

Breaking tempdb uses into multiple procedures

For example, this query causes optimization problems with #huge_result:

create proc base_proc 
as
    select * 
        into #huge_result 
        from ...
    select * 
        from tab, 
        #huge_result where ...

You can achieve better performance by using two procedures. When the base_proc procedure calls the select_proc procedure, the optimizer can determine the size of the table:

create proc select_proc 
as
    select * 
        from tab, #huge_result where ...

create proc base_proc 
as
    select * 
        into #huge_result 
        from ...
    exec select_proc

If the processing for #huge_result requires multiple accesses, joins, or other processes, such as looping with while, creating an index on #huge_result may improve performance. Create the index in base_proc so that it is available when select_proc is optimized.