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:

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment on How to decide on Index - ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer