PAGELATCH_UP lastwaittype

Hi,
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
cmd : AWAITING COMMAND
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.

Thanks,
Goutham

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

Thanks,
Goutham

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" ???

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?

Thanks,
Goutham

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.

Gaurav

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

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.

Thanks,
Goutham