Binding tempdb to its own cache

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

Assigning a temporary database 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

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.

For instructions on configuring named caches and pools, see Chapter 4, “Configuring Data Caches” in the System Administration Guide: Volume 2.

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