Investigating index locking with SQL Server Profiler

NaviDeveloper_NL
NaviDeveloper_NL Member Posts: 42
edited 2008-01-11 in SQL Performance
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.

Comments

  • stryk
    stryk Member Posts: 645
    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 ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • NaviDeveloper_NL
    NaviDeveloper_NL Member Posts: 42
    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
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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.
    Marije Brummel | Freelance Business Central Specialist
  • kriki
    kriki Member, Moderator Posts: 9,121
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Categories