Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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 for more – gbn Feb 9 '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
Added answer which may help... – gbn Feb 14 '12 at 15:52
up vote 4 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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