Extent lock

Question

Post reply

Extent lock

Gail Shaw

SSC Guru

Points: 1004504
More actions
October 14, 2005 at 5:43 am

#95443

Given an extent lock reported in the fashion EXT 1:408680 (sp_lock or deadlock graph) is there any way to find out what object the lock was placed on?
With a page lock I can use DBCC Page to get the object ID. Is there any such method to get the object that owns an extent (or objects, in the case of mixed extents)?
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

How about:

DBCC PAGE (dbid, 1, 408680, printopt)

DBCC PAGE (dbid, 1, 408681, printopt)

DBCC PAGE (dbid, 1, 408682, printopt)

DBCC PAGE (dbid, 1, 408683, printopt)

DBCC PAGE (dbid, 1, 408684, printopt)

DBCC PAGE (dbid, 1, 408685, printopt)

DBCC PAGE (dbid, 1, 408686, printopt)

DBCC PAGE (dbid, 1, 408687, printopt)

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

And another alternative is of course DBCC EXTENTINFO ('DBNAME')

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Thanks. Didn't know that the number was actually a page number.

Never seen that DBCC command before either. (even in the list of DBCC commands that I found)

Thanks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Extent numbers always refer to the first page (of 8) in the extent. So an extent number % 8 always equals 0.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

mikeandanne SSC-Addicted Points: 425 More actions · Answer 5

Hello,

Would any of you know how to reduce or prevent EXTENT locks?

Processing records on a large table in my db is creating tens of thousands of EXT locks which I think may be affecting performance.

I tried to force a table lock on my table in my query hoping it would reduce the EXTENT locks with:

select TOP 1 * from #ledger with (TABLOCKX,holdlock)

I can see with sp_lock that I have a TAB type lock with mode: X but still am getting thousands of EXT locks.

Any assistance would be appreciated and thank you in advance.

Mike