I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB.

Forgetting about lock escalation, I just want to check the default level of lock granularity in SQL Server 2008? How do I do that?

  • 1
    In what respect? The default is row (which applies to Key and RID) but it can be changed by escalation, or by table/index options. Also see dba.stackexchange.com/q/6512/630 for more
    – gbn
    Feb 9, 2012 at 13:04
  • Ok I assume I have row. How do I check if any table / index options could have changed this?
    – dublintech
    Feb 10, 2012 at 13:34

1 Answer 1


After comments on questions

You can query sys.indexes, looking at the allow_row_locks and allow_page_locks columns. Also, sys.tables has a column lock_escalation

I think that will allow you to find all non-default options about locks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.