Chapter 13
Multiple Temporary Databases
Multiple temporary databases were first introduced in ASE 12.5.0.3. Due to the limited exposure offered on this subject and the impacts of ASE 15 on tempdb, this chapter addresses how multiple temporary databases may benefit your organization. This chapter answers the question “Why use multiple temporary databases?”
Introduction
Whether you are doing ad-hoc queries or full applications in ASE, the tempdb is an integral component of your environment. ASE-generated temporary worktables used for resolving order by and group by clauses are always created in tempdb. In addition, your application might create temporary tables in order to provide a facility to manage and make subsets of data available for improved application performance. Multiple temporary databases allow the DBA to separate the application workloads by assigning each application to a specific temporary database. The temporary database(s) to which the application is assigned may be shared by several applications. Even if no application is assigned to a particular temporary database or temporary database group, if multiple temporary databases do exist, users will be delegated to only one temporary database for their current session.
In this chapter, we discuss the reasons for considering multiple temporary databases. At the end of the chapter, a sample implementation is presented.
Purposes for Multiple Temporary Databases
There are two major purposes for choosing to utilize multiple temporary databases. The main purpose is to provide a method of addressing lock contention on system tables when many concurrent #temp tables are being created and dropped quickly and frequently within an application. This has been a major problem with systems where application performance is a critical issue. The second purpose of multiple temporary databases is to provide a level of load balancing and resource sharing methods that cannot be achieved with a single tempdb or through the use of resource limits.
Before proceeding with implementing temporary databases, you need to clarify the business or application needs that will be addressed. A method for evaluating and implementing multiple temporary databases is presented later in this chapter.
Prior to ASE 15
The tempdb database has been an essential component of ASE since it was first developed in 1984. The purpose of the database is to manage temporary subsets of data that only need to persist for the SQL statement, a portion of the statement, the entire user session, or across user sessions. Any data that is stored in tempdb is considered to be discardable in the event ASE is shut down for any reason. As you may know, this is the only database that is refreshed each time ASE is started.
Each time the ASE server is restarted, the model database is copied and used to create tempdb just like any other database is initially created. This behavior has existed since version 3 of Sybase. The tempdb database is created prior to any user-defined databases being recovered during system startup.
With ASE 15
In ASE 15, all temporary databases are rebuilt at ASE startup just like the default temporary database — tempdb. Unless the recovery order is modified using sp_dbrecovery_order, all user-defined temporary databases are created in the order of their database ID, just as if they were going through the normal server recovery process. If a user-defined database has a lower database ID, it will be recovered prior to the temporary database being recovered. Temporary databases are not necessarily recovered before a user-defined database. There is a limit of 512 temporary databases that can be created in each ASE server environment.
System Catalog Changes
In ASE 15, table names can now be 255 characters in length. This change may affect the size of the sysobjects, sysindex, and other system tables within any database. For tempdb, the effect may require additional space to be allocated to the system segment if you are using a lot of temporary tables in your application. In most environments, this will not be an issue. Only when you create a lot of temporary tables that all use full length table names and full length column names might you see a noticeable difference. If the majority of your applications use table and column names of less than 50 characters, this should not be of concern in your environment. As a side note, only 238 characters of the name you assign will be used to identify the table in tempdb. An additional 17 characters are appended to the name to uniquely identify the table.
directio Support
The directio parameter is used to bypass writing updated pages to the operating system’s buffer cache, thereby giving you raw partition performance even though the temporary database devices are defined in the file system. The directio option is only effective in those operating systems (OS) where direct I/O is supported and has been activated by the OS administrator. Currently, it is supported only in Unix and Linux operating systems. directio is a static option and requires a server reboot in order for it to take effect. It can be set at the device level with disk init, disk reinit,orsp_deviceattr. If you have defined your devices for tempdb or other temporary databases with dsync=false, the use of the directio option is available since these two options are mutually exclusive (both of these options cannot be set to true). With directio=true, the write to buffer cache is bypassed and the write to disk for the page is forced. For temporary database devices, this is not the behavior that is most efficient. With temporary databases, the need to guarantee the write to disk is unnecessary as recoverability is not an issue. Therefore, you will want to set directio=false. Using the directio=false option and dsync=false will provide improved performance for those databases where recoverability is not an issue and where the server has I/O contention at the disk level. You can tell if directio is active once the device option has been set to true or false by the message written in the ASE errorlog at startup when the device is initialized. The following example indicates that dsync was set to false and directio was set to true.
1> sp_deviceattr tempdb_d1, directio, true
2> go
'directio' attribute of device 'tempdb_d1' turned 'on'. Restart Adaptive
Server for the change to take effect.
(return status = 0)
Excerpt from errorlog:
Initializing virtual device 4, '/tempdb_dev/tempdb_d1' with dsync 'off'.
Virtual device 4 started using asynchronous (with O_DIRECT) i/o.
The next example is the errorlog after the device’s directio option has been set to false with sp_deviceattr.
1> sp_deviceattr tempdb_d1, directio, false
2> go
'directio' attribute of device 'tempdb_d1' turned 'off'. Restart
Adaptive Server for the change to take effect.
(return status = 0)
Excerpt from errorlog:
Initializing virtual device 4, '/tempdb_dev/tempdb_d1' with dsync 'off'.
Virtual device 4 started using asynchronous i/o.
As you can see, there is no way to tell, just by looking at the errorlog, that the device has directio set to false. Only by looking at the output from sp_helpdevice can you be sure of the settings.
1> sp_helpdevice tempdb_d1
2> go
device_name physical_name description
status
cntrltype vdevno vpn_low vpn_high
----------- --------------------------------
-----------------------------------------------------------------------
-----
--------- ----------- ----------- ----------
tempdb_d1 /sybase_dev/DCDR04/data/tempdb_d1 special, dsync off, directio
off, physical disk, 50.00 MB, Free: 0.00 MB 2
0 4 0 25599
(1 row affected)
(return status = 0)
Even if sp_helpdevice shows that directio has been set to false, only the OS administrator can determine if direct I/O is available at the physical device level.
Therefore, for temporary databases, it is recommended that you set both dsync=false and directio=false for the devices on which the databases are allocated. These devices should only be used for their assigned databases and for only temporary databases. As with any recommendation, you should run a performance baseline, make your changes, rerun your performance tests, and then compare the results to those of your baseline.
Note that with ASE 15, temporary databases no longer write log pages to disk unless the pages are flushed out of cache, thereby having the effect of reducing the impact on the write load.
For more detail on the directio and dsync options, see the chapter on initializing databases devices in Sybase’s System Administration Guide.
update statistics
In ASE 15, the update statistics command creates a worktable in tempdb. The worktable is used to store the statistics for each data partition of a table. Since all tables are now created with a minimum of one partition, this worktable creation should be accounted for when resizing tempdb for ASE 15. Although the impact on size is minimal, if you are running multiple update statistics concurrently against several databases, you might want to consider adding additional space — especially if you are updating the indexes and all column statistics. With the update index or update all statistics options, it is possible for the temporary worktables to be quite large. The amount of additional space will depend on the size of the largest table in each database.
Insensitive Scrollable Cursors
When an insensitive cursor is defined, ASE copies all of the resulting rows to a worktable in tempdb. The worktable is used to process fetch commands so that the original base table is no longer locked. For more in-depth information on ASE 15 cursors, see Chapter 4, “Scrollable Cursors.”
Semi-sensitive Scrollable Cursors
When a semi-sensitive cursor is defined, ASE creates a 16 KB cache for handling the initial set of fetched rows. Once it exceeds the 16 KB cache, a worktable is created in tempdb, but data rows are not copied to the worktable. The worktable is populated with data rows as the rows are fetched. When changes are made to rows read from the worktable, the updates are applied to the base table. In this type of cursor, the updates are visible via the worktable if the data row is fetched. Locking on the base table is not released until the last row in the cursor’s result set is fetched.
Sensitive Scrollable Cursors
With a sensitive cursor, the ASE behavior, as it relates to tempdb worktable usage, is the same as for a semi-sensitive cursor. Only after the cursor fills a 16 KB in-memory cache does ASE create a worktable in tempdb. At that time, the worktable starts getting populated with data rows as the rows are fetched from the base table. When changes are made to rows read from the worktable, the updates are applied to the base table. For the sensitive cursor, the updates are visible via the 16 KB cache and the worktable if the data row is fetched. Locking on the base table is not released until the last row in the cursor’s result set is fetched.
How to Decide When to Add a Temporary Database
When considering the use of multiple tempdb databases, you have to understand the strategies available for their use.
Strategies
With the initial introduction of this feature, the strategies provided allow for limited flexibility. As the functionality matures, additional strategies need to be available for use.
A basic strategy for using multiple temporary databases is to provide the system administrator (“sa” login) with the ability to log into the ASE server and run system stored procedures — like sysprocesses and syslogshold against virtual tables — when the main tempdb database is full. This is probably the first reason a DBA will use to justify adding a temporary database. When the main tempdb is full, the “sa” may not be able to determine what process is filling up tempdb. Without knowing which process to “kill,” the usual corrective measure is to recycle the ASE server, which has further and perhaps detrimental implications. Once the ASE server is recycled, there may be limited or no information about what caused the tempdb to fill up. By creating a tempdb database for “sa” usage only, the database administrator will be assured of being able to log into the ASE server and determine the cause of tempdb filling up. This will allow the database administrator the ability to better determine the cause of a problem that has affected tempdb and evaluate alternative solutions other than recycling the ASE server. As a side note, the MDA tables as discussed in Chapter 9 may be able to provide information as to the culprit process that filled up tempdb. Also, with ASE 12.5.2, if the number of user connections has been exceeded and no additional users can log into the server, a special reserved connection is kept available by the server for the “sa” to use to log into the server.
A second strategy deals with having multiple temporary databases for all users to share. This strategy performs some load balancing by assigning each new user who logs in the next available temporary database in the group of multiple temporary databases using a round-robin methodology. In the case where a temporary database in the group is full, that database will be bypassed and will not be used until space has been freed up.
A third strategy addresses separating applications onto different temporary databases or temporary database groups. The purpose of this strategy is to provide temporary databases that are not shared across applications. (i.e., OLTP application users would not share their temporary database with ad-hoc users). In this manner, searchintensive applications that heavily exploit temporary space can be segregated from normal applications. Furthermore, different storage systems can be used for different purposes. For example, a high-speed SAN might be used for OLTP users, while ad-hoc temporary databases could be placed on slower, less expensive storage.
By choosing the proper mix of strategies, a flexible environment can be created whereby the resources are maximized for the applications and users utilizing the ASE server.
What Are Your Needs?
As with any feature of Sybase ASE, you have to determine your needs before you proceed with implementing the functionality. If you do not properly define your need for multiple temporary databases, the resulting environment may cause resource management issues.
When determining your needs, the following questions should be answered:
What goal am I trying to accomplish by having multiple temporary databases?
Who will benefit from this feature?
Do I have the necessary resources to effectively implement this feature?
What are the short-term and long-term implications of using this feature?
Once you have answered these questions and have determined your need for multiple temporary databases, the next step is implementation.
Implementation Steps
The following steps are intended to ensure that you have properly installed the feature. These steps are generalized to allow for leeway in your implementation.
1. Determine whether the temporary databases need to have separate data and log segments.
2. Determine the amount of space necessary for each temporary database that will be created.
3. Define file systems with usable space equivalent to the space requirements. The use of file systems over raw partitions is selected since the databases are temporary databases and you will want the extra performance from the file system buffering. Be sure to specify the dsync=false ASE option on the disk init command.
4. Define devices within ASE for each of the database devices.
5. Create the temporary databases using the temporary option of the create database command (e.g., create temporary database uc_tempdb_01 …).
6. Using the sp_dbrecovery_order system stored procedure, change the recovery order of the databases so that the temporary databases are recovered before the application databases.
7. Create temporary database groups and assign the new temporary databases to the proper groups. If one of the databases is for “sa” use, do not assign it to any group.
8. If one of the temporary databases is for “sa” use, use the sp_tempdb system stored procedure to associate the “sa” login to this database.
9. If desired, use sp_tempdb to associate an application to a temporary database group.
Determining Available Temporary Databases
There are four ways to determine the available temporary databases. The first method uses the traditional sp_helpdb stored procedure.
The second method uses the new stored procedure sp_tempdb. The sp_tempdb procedure is used to manage the multiple temporary database environments. The syntax for sp_tempdb is:
sp_tempdb [
[{ create | drop } , groupname] |
[{ add | remove } , tempdbname, groupname] |
[{ bind, objtype, objname, bindtype, bindobj
[, scope, hardness] } |
{ unbind, objtype, objname [, scope] }] |
[unbindall_db, tempdbname] |
[show [, "all" | "gr" | "db" | "login" | "app" [, name]] |
[who, dbname]
[help]
]
For more details on each of the parameters, consult the Sybase product manuals.
The third method uses a new dbcc function that allows a login with sa role to get a list of available temporary databases. The new command is dbcc pravailabletempdbs. Since this is a dbcc command, you will need to specify the traceflag that is appropriate for viewing the output. If you want the output to display at your terminal, specify dbcc traceon (3604). Otherwise, the output will be written to the ASE errorlog. See the example below for a sample of the output from this new command. Be sure to note that only the database ID is displayed.
Example:
use master
go
dbcc traceon (3604)
go
dbcc pravailabletempdbs
go
Output:
Available temporary databases are:
Dbid: 2
DBCC execution completed. If DBCC printed error messages, contact
a user with System Administrator (SA) role.
The fourth method is to select the status3 bit from master..sysdatabases. If the hexadecimal value is 0x0100 (decimal 256), the database is a user-created temporary database.
Sample Setup for Temporary Database for “sa” Use Only
Determine if separate data and log devices are needed.
Since the temporary database is for “sa” use only, it is unlikely that the database would need to be large or that it will need to have separate data and log devices. However, it is always a good policy to create a separate data and log device. For this example, the database will be created with one data device and one log device.
Determine the amount of space needed for each temporary database that will be created.
Given that the “sa” will sometimes need to read large amounts of data, we will use a 100 MB device for data and a 10 MB device for log.
Define raw partitions or file systems with usable space equivalent to the space requirements.
For this example, we will assume that there is enough file system space in an existing directory for us to create the two ASE devices.
Define devices within ASE for each of the database devices.
use master
go
disk init
name='sa_tempdb_data',
physname='/sybase/data/sa_tempdb_data',
size="100m",
dsync=false
go
disk init
name='sa_tempdb_log',
physname='/sybase/log/sa_tempdb_log',
size="10m",
dsync=false
go
Create the temporary databases using the create temporary database command. This is a full command in itself and should not be confused with the create database command.
use master
go
create temporary database sa_tempdb
on sa_tempdb_data = 100
log on sa_tempdb_log = 10
go
Create temporary database groups and assign the new temporary databases to the various groups that each needs to be defined to. The goal of temporary database groups is to provide a method whereby the DBA can allocate temporary databases based on the varying needs of different applications that share data on a server. If one of the temporary databases is for “sa” use only, it should not be assigned to any groups.
The group “default” is a system-generated group and the database tempdb is automatically a member of the group. Normally at this point, the DBA would use the sp_tempdb system stored procedure to define a new temporary database group and assign the new temporary database to one or more groups. Since the newly created temporary database — sa_tempdb — is not for use by all users, it is not added to the “default” group or any other group. Using the who option of sp_tempdb will display a list of active sessions that have been assigned to a temporary database.
use master
go
sp_tempdb 'who', 'tempdb'
go
sp_tempdb 'who', 'sa_tempdb'
go
Using the sp_dbrecovery_order system stored procedure, change the recovery order of the databases so that the temporary databases are recovered before the application databases.
use master
go
sp_dbrecovery_order sa_tempdb, 1
go
Use the sp_tempdb system stored procedure to associate the “sa” login to this database.
use master
go
sp_tempdb 'bind', 'lg', 'sa', 'db', 'sa_tempdb'
go
At this point, your sa_tempdb temporary database is ready for use by the “sa” login only.
Other Issues
Dropping Temporary Databases
If is determined that a temporary database is no longer necessary, it can be dropped. The normal drop database command is used to drop a temporary database. There are, however, restrictions.
There can be no bindings to the temporary database at the time the drop database command is issued. An error will be issued if an attempt is made to drop a temporary database and it has binding.
There can be no sessions actively using the temporary database. An error will be raised if there are active sessions against the temporary database. You can use sp_tempdb who, dbname to determine active sessions using the database. These sessions can then be terminated or the DBA can wait until no user sessions are active against the database.
No columns in temporary tables on the database can refer back to Java classes in the source database. This will occur when select into #temp-table is issued and the source table uses Java classes.
Altering a Temporary Database
A temporary database can be altered. It will have the same restrictions and limitations as any other database. If an attempt is made to alter the model database to a size larger than the smallest temporary database, the alter on model will fail.
@@tempdb
A new system variable has been defined for use in determining which temporary database has been assigned to a user’s connection. @@tempdb returns the name of the temporary database to which the user’s connection is associated.
Summary
The multiple temporary databases feature existed prior to ASE 15 but has been underutilized. The use of this feature should be considered for applications where contention exists for tempdb. The utilization of multiple temporary databases allows the DBA to segregate applications from each other and from the “sa” to allow for tempdb space and resources to not be overused by any one individual or application.
When deciding to use additional temporary databases, the DBA needs to determine the needs of the business and consider the positive performance impacts additional temporary databases can have on a database application environment.