tempdb index/performance question

[pedrettig]

In Current Activity, Locks/Object, under the tempdb, we have many many processes showing extent locks for an index named ##lockinfoXX (the number will change from time to time, but the ##lockinfo... stays the same). The process numbered in XX is usually the process for the Enterprise Manager session that we are monitoring from. Is this ##lockinfoXX "index" just the way Enterprise Manager shows us the other locks on the system? What is this about? Many thanks, Gary

 

[nigelrivett]

the spid will be the spid that enterprise manager is using to connect.
It creates the global temp table to gather info.

You should see a ##lockinfo and ##procinfo for that spid.
You can query them to see what is in them.

I would advise against using enterprise manager to look at activity. The times when you really need to find out what is going on it probably will not work or cause more problems.

see

http://www.nigelrivett.net/sp_nrSpidByStatus.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MDXer]

you can take the XX and execute dbcc inputbuffer(XX) to see what that spid is actually doing.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck