Multiple Temporary Databases

[Former Member]

We're currently running ASE 15.7 SP 63.

We've recently had what might be some contention issues in tempdb, we're still investigating.

However, this brought up a question: are there any good guidelines as to how many temporary databases there should be?

Currently on the system in question we have 30 engines, 6 tempdbs in the default pool, 1 tempdb for DBAs, and 1 tempdb for a monitoring tool. 8 total tempdbs. Is this enough, or should there be more?  What are the main considerations for how many tempdbs there should be?

[former_member89972]

Basic thumb rule for number of temporary databases is minimum of two (one for sa and DBA activities) and another for non-sa end users. This allows sa/DBAs to connect even when user tempdb is full.

If applications/SQL are really well written to share the tempdb in a responsible cooperative way you do not need more than 2 tempdbs.

Beyond that if a set of users/applications want to run a free-for-all in their own user tempdb then it is just like a scratch/work database for them. And the number of tempdbs can be as many as demanding users/applications you have with as many as your hardware can support.

Except recovery, some lazy/delayed writes and automatic housekeeping,  SAP/Sybase  treats tempdb just like any other database but with a flexible user security.

HTH

Avinash

[Former Member]

I agree on the small sa tempdb.

Generally, I recommend setting up multiple tempdbs, 4 of similar sizes in a round robin fashion typically works the best.

As the above have stated anything more than this will all depend on the volume you are putting on to your current tempdb.

[Former Member]

Well the question wasn't really about the contention.  The question is: "Are there any rules of thumb for how many tempdbs to have?"  So far I've come up completely blank from looking through documentation and searching online.

[Former Member]

Erick,

Avinash is correct in his rule of thumb. Aways have an "sa" tempdb in order to access the server in emergencies, and then perhaps an application tempdb.

However, Mark is correct. To really tune your environment, more input about it is necessary. Perhaps on tempdb for all applications is just the status quo, but real performance gains might be achieved by providing on to missing critical OLTP applications with high sorting and aggregation requirements.

Regards,

Jean-Pierre