SQL Server Performance

PAGELATCH_UP lastwaittype

Discussion in 'Performance Tuning for DBAs' started by mgoutham, Apr 27, 2003.

  1. mgoutham New Member

    We have a very large OLTP application and we use com+ as our transaction manager.
    Occasionally we are seeing an orphan transaction (we think, but not sure) which blocks
    all other connections for a specified duration (transaction timeout is configured in
    com+). We also use tempdb (global temp tables) very frequently within the transactions.
    The only thing we feel consistent is the connection that is blocking others has
    following charcterstics (from sysprocesses)

    status : sleeping
    open_tran : 1 (in transaction)
    waitresource: 2:1:512
    waittype : 0x0000
    waittime : 0
    lastwaittype: PAGELATCH_UP

    It looks like it is something to do with tempdb as specified in waitresource. Does
    waitresource is the current or the last resource this connection waited for? Since
    waittime is 0 does that mean this connection is not waiting for anything?

    Any help is really appreciated.


  2. satya Moderator

  3. mgoutham New Member

    quote:Originally posted by satya

    Query agianst SYSLOCKINFO table and see any deadlocks hanging which are undetected.
    I'm not sure whether this KBA is related http://support.microsoft.com/default.aspx?scid=KB;en-us;Q293232] to the current situation, but refer thru for any information which may help.

    Satya SKJ

    Thanks for the link. We checked syslockinfo and nothing looks suspicious. The blocking
    connection has couple of application tables to itself and other connections are waiting for
    the blocking connection to release them. Our only question is whether the blocking thread
    is waiting for any other (system/tempdb) resources. We are not sure whether we are in the
    right direction in identifying our blocking issue. As mentioned earlier the only thing we
    can link between these blocking connections is the PAGELATCH_UP waittype.
    Any help is greatly appreciated.

    If it helps, here is the output from dbcc waitstats for pagelatch_up.

    Wait Type Requests Wait Time Signal Wait Time
    -------------------------------- -------------- -------------- ----------------
    PAGELATCH_UP 212039.0 80099.0 76867.0

  4. josephobrien New Member

    In reading the Q article about Syprocesses , Waittype and Lastwait type fields for
    Sql Server 7.0 it defines lastwaittype as the "last or current waittype of a spid"
    so waittime is zero it is the last wait and if waittime> 0 it is the current.

    I would be curious to see what object is part of page 2:1:512. You can use
    the dbcc page command to determine the object that page 2:1:512 is a part of
    like so: dbcc page(2,1,5512). I am guessing it would be sysobjects, syscolumns, or sysindexes.

    Could you be accessing regular temp table by doing a large
    "select into #mytemptable" ???
  5. mgoutham New Member

    We do create a global temporary table dynamically for each transaction like
    ##TRANS_{SPID} and then use this table to communicate between stored procedures
    within the batch (each batch may contain 5 to 10 procs). At the end of the batch
    we drop this table. All references to this global temporary table are dynamic sql
    statements. Can we use dbcc page() on a production server?

  6. gaurav_bindlish New Member

    I would like to add one input here. Try executing DBCC OPENTRAN() in blocked database and DBCC INPUTBUFFER() with spid of the blocked resource. This may give an insight into the last command that was executed when the blocking took place.

    Also one suggestion. Can you use a permanent table in place of ##TRANS_{SPID} with an addtional column having SPID. I feel this may be helpful.

    Hope this helps.

  7. sqljunkie New Member

    I've seen lots of problems with Stored Procs that create temp tables. The sp will be recompiled each time it is executed. I would follow Gaurav's suggestion and create a permanent table.
    The fact that the waittime is not greater than 0 on your pagelatch_up means that there is probably not an issue there yet.
    If you clear the waitstats and run it again after a few minutes, what are:
    the top 5 most requested wait types?
    the top 5 highest wait times?

    When looking at the sysprocesses table I would use:
    select * from sysprocesses where waittime>15 and spid>50
  8. mgoutham New Member

    Thank you all very much. I will try to chnange the design and use
    permanent table instead of global temporary table.

    I really appreciate your input.


Share This Page