3

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?

Leigh Riffel
  • 23,854
  • 16
  • 78
  • 152
dublintech
  • 1,519
  • 8
  • 21
  • 26
  • 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 http://dba.stackexchange.com/q/6512/630 for more – gbn Feb 09 '12 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 '12 at 13:34

1 Answers1

4

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.

gbn
  • 69,809
  • 8
  • 163
  • 243