Configuring tempdb for Performance

- Tempdb is the only Sybase device which is better served by being created on
Flat files.  Putting tempdev on a file system can easily result in
a 33% performance increase (I've documented such results in my last
two P&T clients).  This is because tempdb is accessed in a serial,
synchronous manner by Sybase.  And, since tempdb is dropped and
recreated up on each reboot of Sybase, there is no recovery concerns
(like the ones raised above).

- It is also recommended to remove the first two MB of tempdb from the 
master device.  The reasoning is; if you're extending tempdb onto a 
more apt device (flat file, SSD, RAM disk, tempfs, etc) then why allow
the first 2mb to be written and rewritten over and over on the non-tuned
master device?  Additionally, if you mirror your Master device, you 
would not want to mirror tempdb database hits.

There are well documented procedures for doing this 
at the Sybase FAQ: http://www.isug.com/Sybase_FAQ/, specifically ASE 
question 1.1.3.  The recommended method is to merely drop the tempdb 
segments, not to edit sysusages like in the past (since editing sysusages
makes disaster recovery very difficult).  

The old way was to do this:
update sysusages
set segmap = 0
where dbid =2
and lstart = 0

then either bounce the server or run dbcc dbrepair('tempdb','remap').
But, as said above, this makes disaster recovery almost impossible.

- Seperate the three segments of Tempdb (system, default, and log) onto
three seperate Tempdb flat files.  Not seperating at least default and
log can lead to ULC problems. 

- Configure a seperate named cache for tempdb.
- Create indexes on the temp tables you create so they perform better.
- Eliminate the use of "select into" in tempdb tables.

- tempdb is configured to automatically be "trunc log on checkpoint" no
matter what the database option is.  This is hard coded into the system.

- Tempdb Sizing: a rough rough estimate is 25% of largest db.  

---
Other Options:

- Solid State disks (SSDs)/RAM disks

A Solid State Disk is essentially a RAM-only device (actually its DRAM; 
Dynamic Random Access Memory) that is seen as a
conventional Disk by the Operating System.  It will provide incredibly
fast performance for a flat-file based tempdb device.  You don't have
to expend money for the entire tempdb; just the first 1 gb or so, since
tempdb is accessed synchronously.

---
- raw devices; 
Raw devices are the last choice for a temp device.  Raw devices are
designed for Asynchronous I/O and do nothing for Tempdb-intensive
activities.

----
- Unix tempfs/swap.

(See DocID 20448 at Sybooks; http://my.sybase.com/detail?id=20448)

Configuring tempdb flat files in /tmp (or whatever swap uses on your system)
will not only take advantage of synchronous access, but they'll be primarily
memory resident, thus additionally fast.

You'll have to put some additional code in your Sybase startup scripts to
account for /tmp devices; /tmp gets wiped out each reboot (under most OSs
that is; it depends on your rc file setup), and Sybase will
not start if it cannot read its tempdb devices.

If you put tempdb devices in /tmp (over swap) be aware that you're sharing
the swap space with the system, and that files in /tmp are always going
to get second-best treatment from the O/S.  This means, Solaris will,
in periods of swap space emergencies, summarily delete files out of
/tmp to make room.  It deletes files based on timestamp (oldest getting
deleted first).

I was at a client that had tempdb over tempfs.  They had configured Sybase
to have two tempdev devices, tempdev1 and tempdev2.  They both were
created as flatfiles in /tmp.  Periodically they would have these odd
Sybase crashes....what was happening was, they were not exercising the
Sybase server enough to ever touch the second tempdb device, and thus
the tempdev2 file in /tmp had a date/time stamp of whenever the Sybase
server had last been started.  The aforementioned behavior kicked in
periodically, saw a weeks-old fairly large (many 100s of megs) file and
deleted it...which sent Sybase's checkpoint process into a fury.  I believe
we solved the problem by tuning down tempdb's size until full testing/
deployment was at hand (and then at that point, a cron job to periodically
"touch" the two tempdev devices and keep their date/time stamps current).

---
How much swap to configure for sybase servers?  Rule of thumb; allow for
at least 4 times your total memory parameter in swap space.  This is 
before considerations for tempdb on tempfs.

---
Questions:
- What happens if you make a directory under /tmp; would this phenomena
still happen?
- If a 300mb tempdb device exists in /tmp, will the entire file always
be memory resident or just the parts of it being accessed actively?