Recently a colleague of mine posed the question of the pros and cons of having the tempdb database partially on the master device. By default, during the installation of an ASE Server, a small fragment of tempdb is placed on the master device (a device is a physical area of disk where a database sits upon). Typically it is the size of the model database which is 2M to 4MB depending upon the ASE Server version.
This small fragment contains the default, system and log segments. Segments are simply pointers that tell the SAP ASE Server where to locate the system tables, user tables and transaction log (syslogs) table respectively.
Onto the debate: since version 4.2 of the ASE Server (many years ago), there has been a school of thought that says one should move the tempdb off of the master device onto a faster disk to effect better performance. The premise being that the master device was a very secure area of disk with a RAID system that promoted mirroring and error checking. All of this security comes at a price and that translated to slower disks. Alternately tempdb, where internal worktables were stored, is typically placed on faster more volatile disks to promote better performance. The debate was: to squeeze further performance gains from the SAP ASE Server, one should avoid placing any tempdb activity in the master device. A classic hotspot avoidance strategy.
This strategy had many manifestations, some workable, many not. You may recognize a couple of these.
Creation of dummy tables. The idea was we would fill up the first 2M or 4M of the tempdb database on the master device with a dummy table. Once filled up, this space on the master device would be non-accessible and therefore any growth would take place on the faster tempdb devices.
Dropping the system, default and log segments from the master device. This strategy effectively removes any object location from the master device. Objects are placed according to the underlying segments. Removing the segments effectively removes the placement of the objects the segments refer to.
Dropping the tempdb area of disk from the master device. ASE DBAs are forever tinkering with the system tables (caveat: not recommended for obvious reasons). This strategy involves going into the sysusages table in the master database and removing the record that records the relationship between the tempdb database and the master device. No record therefore no relationship.
There are more strategies but these are the most common. The common thread for these strategies are they are ineffective either by their assumptions of performance gains or by the fact they are simply not recommended given the fact there are better architectures available today.
Personally I have had past issues with the presented strategies: my Client removed the tempdb database from the master device and the ASE Server experienced a corrupt tempdb database. I was called in to fix this issue. A couple of special trace flags in the RUNSERVER files and rebuilding the tempdb database on the master device brought the ASE Server back to an operational state. This took a considerable amount of effort and time. This all could have been avoided if the tempdb database was left as originally placed on the master device.
Getting back to the debate, how does one balance performance by placing tempdb on faster disks verses the need for a stable and robust system?
Here is a strategy that gives us both. As the DBA, when I log onto the system I typically run maintenance scripts such as update statistics or rebuilding tables, etc. Following best practises, I would be using not the 'sa' login but I would be used another account such as 'sapsa.' My Client logins would also have their own login names.
Using multiple or named tempdbs I would place these tempdbs on faster disks and I would assign my 'sapsa' user to a named tempdb and my normal Clients to another named tempdb. Depending upon performance needs I would have a variation on the above. The original 4K of the tempdb database on the master device remains unused and only accessed by the 'sa' login. This strategy give the Client Applications and the 'sapsa' login the performance of a faster tempdb and protects the system from any tempdb failures by having the original 4M tempdb on the master device to aid in any rebuilding methods. This tempdb architecture combined with a hard relationship between logins and tempdbs offers us both stability and performance.
This Field Notes Series is dedicated to observations from the field taken from personal consulting experiences. The examples used have been created for this blog and do not reflect any existing SAP Customer configuration.
