Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2003
    Location
    Philippines
    Posts
    8

    Unanswered: Tempdb systables Locking

    I've experience TempDB Locking on our server ( system tables ) , the problem occurs when the store procedure executes ( we have already fine-tune the store Procs by using insert into and create #table instead of select-into command and do a dirty reads on some sp as what we have read on Sybase trouble-shooting manuals but still locking occurs)

    We are using ASE 12.0 in Solaris 8 platform



    Is there a way we could eliminate the system tables locking or is the problem that i've experience is a bug of Sybase 12.0 ?


    I would appreciate all the help i can get, thanks ...

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    1) Avoid creation/drop of objects into heavily used SP
    2) multi-tempdb (12.5.0.3) could help you a little bit

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208

    Re: Tempdb systables Locking

    I recall tempdb contentions were a hot topic on 11.9.2 and 12.0

    Discussions and liaison with Sybase apparently a lot of these were addressed in later updates(patches) of ASE 12.0. Typically, if I remember correctly Error: 216's.

    I can't remember which release, but what used to happen was Sybase treated dropping of # tables in tempdb same as a normal user table. This means the system tables in tempdb were getting locked for the time of the drop. On a highly tempdb intensive server this was resulting in contention in tempdb.

    During those days one fo the suggestion that was discussed was to truncate your # table in your query before exiting the session. This way the drop was much quicker and the lock period was less.

    If the truncate table does not work for you. I guess then it is a good example to demonstrate to Sybase Tech Support that tempdb contention still exists. Of course, the first thing that you'll be asked to apply the latest ASE patches and fair enough too.

  4. #4
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Re: Tempdb systables Locking

    Originally posted by willy_and_the_ci

    I can't remember which release, but what used to happen was Sybase treated dropping of # tables in tempdb same as a normal user table.
    Problem in 12.5.0.2
    Fixed in 12.5.0.3

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Another thing with the advent of 'inline views'/'derived tables' depending on the situation you might be able to avoid the use of temp tables altogether.

    e.g.
    select *
    from( select * from foo )
    Thanks,

    Matt

  6. #6
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    Originally posted by MattR
    Another thing with the advent of 'inline views'/'derived tables' depending on the situation you might be able to avoid the use of temp tables altogether.

    e.g.
    select *
    from( select * from foo )
    Derivative tables are coming with 12.5.1, not using 12.0

  7. #7
    Join Date
    Nov 2003
    Location
    Zurich, Switzerland
    Posts
    7
    Are you using tempdb in ramdisk ?, I have seen this on quite a few occasions, sometimes it is recommended to set up a small hot cache in tempdb to stop sys catalogs contention.

    The best cure for this though is to create permanent temporary tables in tempdb, you can either create them in model, or have a creation script as part of the server startup process.

    (Even though my satus is "Junior", I have been a Sybase repserver/ASE DBA for over 10 years)

  8. #8
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353

    Re: Tempdb systables Locking

    Originally posted by Cyberdude
    I've experience TempDB Locking on our server ( system tables ) , the problem occurs when the store procedure executes ( we have already fine-tune the store Procs by using insert into and create #table instead of select-into command and do a dirty reads on some sp as what we have read on Sybase trouble-shooting manuals but still locking occurs)
    You'll continue to have locking issues if you create/drop tables and the number of concurrent users is relatively high.

    I have in the past solved this problem by having "permanent" temp tables. This avoids the locks on the system tables due to create/drop, and using a decent index on that table can make it work quite well even though you have more IO on that table (you need to delete data from the table instead of dropping it).

    Michael

  9. #9
    Join Date
    Oct 2003
    Location
    Philippines
    Posts
    8

    Thanks ..

    Thanks for all your help, i already tried creating a permanent temp table in tempdb but the result is slow speed but no locking.

    by the way we are using 12.0.0.6 ( already installed patched )
    but i downloaded the latest patch 12.0.0.7 and testing it right now ..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •