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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Kevin Sherlock

    Fair enough.  Multiple tempdb’s are certainly a perfect solution.  But they have to be managed (bound to each new login/app, etc).  You briefly explained the obvious disadvantages to removing the tempdb allocations to the master device (never advised as your experience with corruption points out).  But, you haven’t dispelled the more common approach for the “non-multiple-tempdb” users out there, simply dropping the system, default, and logsegment segments from the master device (which is much more easily remediated in the corruption case).  What exactly is the disadvantage to that approach?

    (0) 
    1. Bret Halford

      I think the main disadvantage to just dropping the segments from the initial fragment on master is that the initial allocations for all the system tables are still on the master device and can still see a fair amount of activity – every time a temp table is created or dropped you have rows inserted or deleted in sysobjects, sysindexes, syspartitions, syscolumns, etc.

      (0) 
      1. Kevin Sherlock

        Bret, as of 15.0.2, basically the wash marker in the cache is the only thing left to cause physical io to the disk in tempdb (no checkpoints of dirty pages any more, ie, lazy writes).  While that’s doesn’t completely address the issue you raise (obviously, this depends on cache turnover and activity), more recent versions of ASE make physical IO in tempdb much less of a concern.

        As I said, I agree with the OP, multiple tempdbs are a great solution to move completely away from master device.  They do add another “thing” to manage though and every site is different of course wrt TCO.  Just trying to establish the cost/benefit part of the arguments being made.

        (0) 
    2. Hank Du

      Advice for production DBAs: do NOT remove tempdb off of the master device, here is an example of the consequences.

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Error: 1105, Severity: 17, State: 4

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Can’t allocate space for object ‘syslogs’ in database ‘tempdb’ because ‘logsegment’ segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log.

      Otherwise, use ALTER DATABASE to increase the size of the segment.

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Error: 3619, Severity: 17, State: 1

      @

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Can’t allocate space for object ‘syslogs’ in database ‘tempdb’ because ‘logsegment’ segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log.

      Otherwise, use ALTER DATABASE to increase the size of the segment.

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Error: 3619, Severity: 17, State: 1

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Unable to write CHECKPOINT record in database 2 because the log is out of space.

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Error: 1105, Severity: 17, State: 4

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Can’t allocate space for object ‘syslogs’ in database ‘tempdb’ because ‘logsegment’ segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log.

      Otherwise, use ALTER DATABASE to increase the size of the segment.

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Could not create temporary database (dbid = 2). If out of space, please extend and reboot. Otherwise contact Sybase Technical Support.

      00:0002:00000:00001:2014/02/06 14:25:09.17 server  Error: 1619, Severity: 21, State: 1

      00:0002:00000:00001:2014/02/06 14:25:09.18 server  Could not open TEMPDB, unable to continue.

      (0) 

Leave a Reply