Investigating index locking with SQL Server Profiler

NaviDeveloper_NL

I am trying to find out what indexes are locked and how long they are locked when executing a query. I want to use Profiler for this. I choose the events Lock:Acquired and Lock:Released.

When I see the results in Profiler, I do not find the objectid of the index being locked. Is it possible to do this like for example with sp_lock? (column ObjId).
Sp_lock does not give me the desired information because this only gives the current locks when it runs, not during a transaction.

Also sp_lock gives me the type of lock like key, page or table.

stryk

What is the purpose of this? SQL Server is locking permanently various kinds of system resources, even though most of these locks just take few microseconds ...

IMO it is important to keep track about the blocks that result from the locks; means when another process is affected by a lock.

If you're looking for an "Automatic Block Detection" I could give you some hints ...

NaviDeveloper_NL

The purpose of this is that I want to see the effect of code changes on locking behaviour.
Less locking would mean less blocking.

Keeping track of the blocks that result from the locks normally has to be done if the code is LIVE already.

Marije_Brummel

Blocks are the result of transactions that take to long.

Transactions take to long because, a) It takes to long to update indexes and sift levels, b) the transaction has slow reading speed, c) the hardware is configured wrongly.

Try to speed up the processes first.

kriki

Mark Brummel wrote:

Blocks are the result of transactions that take to long.

Transactions take to long because, a) It takes to long to update indexes and sift levels, b) the transaction has slow reading speed, c) the hardware is configured wrongly.

Try to speed up the processes first.

d) the C/AL is programmed badly (for performance at least)
e.g. Change/delete/insert some record(s) at the beginning of a function, then a lot of reading/processing or a CONFIRM-message( :evil: ) and in the end something is written and then a COMMIT is done.