Search sqlserver-dba.com

Follow sqlserver-dba.com

*Use the Comments section for questions

Daily & Exclusive Content

SQLServer-DBA.com Links

How to decide on Index - ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS

21 June,2012 by Tom Collins

Question: What does the ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS mean on the CREATE INDEX statement ? What is the cost\benefit of ON|OFF? Is there a performance gain?

USE [DB]
GO
CREATE NONCLUSTERED INDEX [ui_1_temp_nc] ON [dbo].[myTable] 
(
	[col1] ASC,
	[col2] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Answer:

1) SQL Server takes locks at different levels – such as table, extent, page, row. ALLOW_PAGE_LOCKS and ALLOW_ROW_LOCKS decide on whether ROW or PAGE locks are taken.

2) If ALLOW_PAGE_LOCKS = OFF, the lock manager will not take page locks on that index. The manager will only user row or table locks

3) If ALLOW_ROW_LOCKS = OFF , the lock manager will not take row locks on that index. The manager will only use page or table locks.

4) If ALLOW_PAGE_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF , locks are assigned at a table level only

5) If ALLOW_PAGE_LOCKS = ON and ALLOW_PAGE_LOCKS = ON , SQL decides on which lock level to create according to the amount of rows and memory available.

6) Consider these factors , when deciding to change the settings. There has to be an extremely good reason , backed up by some solid testing before you can justify changing to OFF

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

Posted by Tom Collins at 8:27 AM in Configure, Indexes, Locking | Permalink | Comments (9) | TrackBack (0)

Searches: database lock, lock in sql, lock sql server, lock sql table, lock table, no lock, row level locking, sql 2005 lock, sql server lock sql lock table, sql with lock

Hi,

in one of our databases (version - sql server 2005), we had a setting like allow_row_locks ON and allow_page_locks is OFF. after that
we have upgraded to SQL server 2008 and we have both hints set to ON. but found our reporting db is performing slow. What could be the reason?

Thanks

Posted by: ananda | Nov 12, 2013 5:36:09 AM

Hi
There could a number of reasons. 1) Is the allow_page_locks the only change made? are there any other changes?
2) Have you kept the same maintenace plan?
3) Can you identify a query , with a recorded response time from 2005 and can compare the response time to the 2008 version?
4) Have you compared the execution plans from those 2 queries?

Posted by: Jack Vamvas | Nov 18, 2013 10:35:00 PM

Hi, I think this should be

4) If ALLOW_PAGE_LOCKS = OFF and ALLOW_ROW_LOCKS = OFF , locks are assigned at a table level only

5) If ALLOW_PAGE_LOCKS = ON and ALLOW_ROW_LOCKS = ON , SQL decides on which lock level to create according to the amount of rows and memory available.

Posted by: Juan | Jun 23, 2014 9:53:45 AM

Hi Juan, I'm confused by your comment. The suggestion is what is what is in the post . Check point 4 and 5. Did you mean to say something else?

Posted by: Jack Vamvas | Jun 25, 2014 10:19:18 PM

if I have a table which have only insert operations, can I set allow_page_locks = off and allow_row_locks = off?

Posted by: agnaldo | Oct 26, 2016 12:39:19 PM

@agnaldo - what your suggesting means that locks will be assigned at a table level ONLY. There will be no page or row locks. This may be what you want , but there is a DANGER that the table lock will cause all sorts of contention issues. When you leave it at ALLOW_PAGE_LOCKS = ON and ALLOW_ROW_LOCKS = ON , SQL Server will decide on between performance & contention.
Are there are any SELECT statements that will run on this table?

Posted by: Jack Vamvas | Oct 29, 2016 2:46:23 AM

My Index has ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90 setting. Still dead lock happend. This table has select, insert, update parallel. For Select I have specified nolock but rest I cannot as they are perfomed by etl tool(Informatica). Please suggest.

Posted by: subhash | Mar 9, 2017 3:02:45 AM

subash , deadlocks occur because occurs because at least two tasks block each other permanently.There a number of different scenarios that lead to deadlocks. Read this guide on blocking and deadlocking http://www.sqlserver-dba.com/2012/11/sql-server-how-to-detect-and-troubleshoot-blocking-and-deadlocks.html

Posted by: Jack Vamvas | Mar 13, 2017 10:02:07 AM

In points 4 and 5 the same ALLOW_PAGE_LOCKS repeated twice.
4) If ALLOW_PAGE_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF , locks are assigned at a table level only

One should be changed to ALLOW_ROW_LOCKS

Posted by: Michael Freidgeim | Nov 21, 2018 11:03:13 PM

Verify your Comment

Previewing your Comment

Posted by: |

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:

Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.