SQL Server Performance

Waitresource tempdb.sysindexes

Discussion in 'Performance Tuning for DBAs' started by Link, Dec 16, 2004.

  1. Link New Member

    hi there,
    when I'm checking the sysprocesses table, most of the time I see lastwaitype PAGELATCH_UP and waitresource 2:1:112 which is the sysindexes table in tempdb. is it perfectly normal or I should do something about it.

    thanks
  2. thomas New Member

    What does sp_lock look like?

    Do you have performance problems? If not, don't fret.



    Tom Pullen
    DBA, Oxfam GB
  3. Link New Member

    I knew we don't have locking/blocking issue here. deadlocks hardly happen since we migrated to SQL 2000.
    thanks!
  4. satya Moderator

  5. rlahoty New Member

  6. Link New Member

    thanks for your links.<br />I think it is caused by temp tables with indexes created by sprocs (who will create an index on a small temp table???). user objects donot show up on the table (nor on dbcc page result), so donot know if it is right or not.<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. satya Moderator

    So do you think the application creates such indexes on temp tables, it is good thing for performance but not always.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. Link New Member

    I was surprised that I couldn't find created index statement from our stored procedures/triggers (app based).<br /><br />have you guys noticed any 2:1<img src='/community/emoticons/emotion-12.gif' alt=':x' />xx waits? ( excluding 2:1:1 or 2:1:3 )<br />
  9. satya Moderator

    If the temp table is created by defining primary key, the SQL will create an index on that Pkey column by default.

    In one article Kalen referred:
    I also introduced the undocumented DBCC EXTENTINFO command. This command can show you which extents SQL Server has allocated to an object as uniform extents (eight consecutive pages) and which pages it allocated from mixed extents—that is, one page at a time. Knowing when and how often SQL Server allocates mixed extents can help you determine whether mixed-extent allocation is causing concurrency problems. Let's examine the reasons that too many mixed-extent allocations can be a problem, then look at several possible solutions.

    However, single-page allocation has a drawback. If you're creating many small tables concurrently, you can encounter a special kind of contention that can be hard to troubleshoot. Brian Moran discussed this problem in his October 23, 2003, SQL Server Perspectives column, "Is Your Tempdb Stressed Out?" (InstantDoc ID 40615). Moran described a situation in which many users were running a stored procedure that created tens of thousands of temporary tables quickly in a client's tempdb database. As I mentioned last month, each object—including temporary tables—needs to allocate two pages from mixed extents when it gets its first row, so in this situation, you have tens of thousands of simultaneous requests for page allocation.


    Refer to the SQL Server magazine for referred instant doc. for more information (I think that article will be marked for subscribers only).
    HTH




    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. Link New Member

    You are right Satya - primary key is also one thing to check. we have only one sp using a temp table with primary key and it is not used very ofter. we have greatly reduced the amount of temp tables by using table variables too.

    I do have the access to the magazine and I've seen the article before but probably missed the point and didn't pay too much attetion to it. thanks for pointing it out and I think that is definitely something I need to check for this issue.

  11. Link New Member

    Hi Satya,<br />the article is still related to the 2:1:1, 2:1:3 symptom. <br />maybe I should not worry about the 2:1<img src='/community/emoticons/emotion-12.gif' alt=':x' />xx waits at all as Thomas suggested.<br />
  12. satya Moderator

    True as long as the temp table creation doesn't dent performance of the long running queries.
    Keeping up the memory and good placement of data/log files in preferred disks will fetch you optimum performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page