Normally in the ASE Database Server we think of the tempdb database as a scratch pad database. A database that gets rebuilt upon a SAP Sybase ASE Server recycle from the model database, a database that is used by the ASE SQL optimizer to store internal work tables and users for the creation of temporary tables. The design buzzwords when configuring any tempdb are size and speed. Size is determined by the number of users concurrently running SQL queries and creating temporary tables in the tempdb space. The busier the database system, the space required for both the tempdb data and tempdb transaction log increase. Lets answer some common questions regarding tempdb:
How big do I make tempdb?
While intellectually we can all appreciate the response of 'it depends' but practically we must give an answer; something that is small enough to not waste space and something that is large enough to not be constantly adding more tempdb disk after a short time later. Typically a good starting point is 25% of the largest database with a maximum of 100 GB. We need to put a maximum in our calculation as the idea of having a 25% tempdb for a 15 TB user database would be too much in most cases.
Once we have a starting point we can monitor usage and make adjustments either up or down. Use the program 'exec sp_spaceused ' in the tempdb database and examine the unused portion (minus the transaction log size) to help with monitoring space. If we are seeing 50% unused then the logical step would be to adjust the total size of tempdb down by 25%. If we are seeing greater than 75% then adjust the size up be 25%. At this point in time its easier to increase tempdb than it is to shrink tempdb. While there exists "alter database" commands to expand and shrink, only the shrink is meant for user databases. There are ways we can shrink the tempdb database however this does require you to open an SAP Support Ticket to ensure the correct procedures are followed. A hint for this is to put tempdb on many devices to allow one to shrink or grow the size in measured steps.
We have a single tempdb sized, when do we design for multiple tempdbs?
There will be several defining events that will tell us when we should design for and implement multiple tempdbs. The top events are:
- tempdb sizing appears to be inadequate after a period of time
- tempdb activity slowing down overall system performance
- blocking locks in the tempdb system tables
1. tempdb sizing appears to be inadequate
If you find the tempdb database is appropriately sized given our tempdb sizing guidelines but at certain points tempdb is full or over 80% then to avoid these situations one is faced with two choices: increase the single tempdb or add more tempdbs to the ASE Server. If this is the only criteria, the choice is not clear and we need to access the other events for an answer.
2. tempdb activity slowing down overall system performance
This event can be an issue with slow disks for tempdb files or the absence of a named cache for tempdb. These configuration issues should be address first before considering multiple tempdbs. It is recommended that tempdb have SSD hardware and every tempdb have a named cache. This is strictly for performance.
3. blocking locks in the tempdb system tables
This event is perhaps the easiest one in our decision making process. If we see blocking locks or contention on the system tables in tempdb then multiple tempdbs will certainly help, no question. Periodically execute the sp_object_stats procedure with the sp_sysmon procedure to monitor contention on tempdb system tables.
What are the size of the multiple tempdbs?
Sizing for multiple tempdbs is another 'it depends' scenario. Lets assume we started with a 100 GB tempdb and we will make the assumption that all users are equal in load. In this case I would design for the following: (size of tempdb original + (.05 * size of tempdb original))/proposed number of tempdbs. The 5% is purely for overhead. There will be a minimum amount of tempdb that will only work in our environment.
How many tempdbs should I configure?
The actual number of tempdbs can vary but a good starting rule of thumb can be taken from the output of sp_object_stats. The minimum of multiple tempdbs is 2, the maximum multiple tempdbs will match the number of on-line engines. In-between these two values we can only provide a rough order of number of tempdbs by a monitoring and correction method. By adding single tempdbs and monitoring during the burn-in period; if sp_object_stats does not report contention on the tempdb system tables then we have arrived at the correct number of tempdbs for our current system.
The advantage to this is that we can easily create additional tempdbs on-line while our ASE Server is operational. We'll assume that the round-robin approach to tempdb usage; that should be our starting design assumption: all users are created equal.
What is the best environment for tempdbs?
Tempdbs should be on fast disks. Several implementations favor SSD for holding the additional tempdbs. Additionally one can make the additional tempdbs into in-memory databases. This has been done with great success and remains a favored option.
In summary, multiple tempdbs are a great boon in Systems that feature increased temporary table usage either by SQL programming or the optimizer, thousands of users and/or an increased engine/CPU count.
