ALLOW_PAGE_LOCKSand ALLOW_ROW_LOCKS

Question

ALLOW_PAGE_LOCKSand ALLOW_ROW_LOCKS

salmanucit

SSC-Addicted

Points: 400
More actions
September 18, 2008 at 9:49 pm

#191043

Hi,
Online books don't explain these concepts and pros and cons of using this. cany anyone tell me about any technical articles which explain what are these options for related to index creation and alteraion? what do these two options do and what is the benifit i they are turned ON or OFF
Thanks
Salman

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

They affect what logs are allowed on the indexes. SQL can take locks at various granularities depending on the amount of rows affected. It can lock at the row, page, extent or table level.

Those options affect whether or no SQL is allowed to take a row or a page lock. Rather don't fiddle with them unless you have a good reason and even then test heavily in a dev environment.

I had a situation recently where a DBA decided to switch the 'allow page locks' off, on production, without testing. SQL began taking table locks instead of page locks and the entire system ground to a halt.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

salmanucit SSC-Addicted Points: 400 More actions · Answer 2

Hi,

Thanks for your reply.

1. What happens if some one turns off ALLOW_PAGE_LOCK but ALLOW_ROW_LOCK is turned on.

2. What happens if some one turns off ALLOW_ROW_LOCK but ALLOW_PAGE_LOCK is turned on.

3. What happens both are turned off.

What kind of locks are qcquired in each of above scenarios.

Thanks

Salman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Depends on how many rows your query affects and how much lock memory SQL has available.

If both are switched off, then you'll probably be getting table locks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

salmanucit SSC-Addicted Points: 400 More actions · Answer 4

Thanks Shaw

Can you, or anyone else explain three questions i asked?

Salman

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

salmanucit (9/19/2008)
Thanks Shaw
Can you, or anyone else explain three questions i asked?

I did. It depends on the number of rows affected by the query and the amount of lock memory that SQL has available.

Whichever lock option you've disabled will not be taken. Which of the remaining ones gets used will be decided by the lock manager based on what percentage of the table has to be read and how much memory is available for taking locks. If the entire table needs to be read and there's very little lock memory available, then most likely a table lock will be taken. Conversely, if only a couple of rows need to be read and there's lots of memory available, then the lock manager will likely take row locks (if that option is available) or page locks.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

salmanucit SSC-Addicted Points: 400 More actions · Answer 6

hi

i am not sure whether i am not able to explain what i want or you are not able to understan what i want. I dont think you answer has explain that what is the difference between ALLOW_PAGE_LOCKS = OFF and ALLOW_PAGE_LOCKS = ON and same, what is different between ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = ON. If you say that "it depends", then what is the diference between to if "it depends" on that you just explained.

Salman

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

In SQL Server, when performing large transactions on a given table, the engine can decide that it's more efficient to grab a few page locks than many row locks. When the transaction is even larger, it will decide it's more efficent to lock the whole table than many pages of the table.

Locks on a table are managed through the indexes, and it's possible in SQL Server to use the ALTER INDEX statement to affect how the engine makes it's decisions to manage the locks. If you have a table that is maintained by reloading the full table or large parts of the table rather than row by row through an OLTP type system, then it may make sense to set ALLOW_ROW_LOCKS = OFF and possibly ALLOW_PAGE_LOCKS = OFF so that the engine doesn't have to go through the process of aquiring large numbers of row locks before it decides to escalate them to page level locks. In a row by row OLTP system, just leave them set ON and let the engine manage it's own lock resources.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

If ALLOW_PAGE_LOCKS is off, then SQL will not take page locks on that index. It will only take locks at the row or table level.

IF ALLOW_ROW_LOCKS is off, then SQL will not take row locks on that index. It will only take locks at the page or table level.

If both are off the SQL will not take locks at the row or page level. It will only take locks at the table level

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass