Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
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 '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

1 Answer 1

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

 
discard

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.