March 18, 2008 at 3:04 pm
Hello,
Quick question about ALLOW_PAGE_LOCKS. Should I set this to ON for all indexes in a heavy transaction based system? I am finding very little clear documentation elsewhere on when to use and when not to use. I know you can't reorganize indexes with it on. Will having it set to ON cause any issues for multiple users making updates to the same table at one time?
Any extra information anyone has is greatly appreciated!!
Thank you,
GP
March 18, 2008 at 9:59 pm
Very difficult to advise you when we don't know what constitutes "A heavy Transaction Based System" as you have stated!
How many Transactions are you talking about? Have you measured that yet?
At runtime, the Database Engine/Query Optimizer will choose as to whether a PAGE LOCK will be required.
The basics of this is that the database Engine/Query Optimizer will choose to lock all rows on a Page if it's optimally better to do so! Otherwise row locks will be used instead.
As for your question on multiple users accessing data when a page lock has occured, well that depends on the nature of the query been executed that has caused the page lock in the first place!
Can you elaborate on this?
March 20, 2008 at 6:59 am
Thank you for your reply! I guess to many of the readers out here my idea of a 'heavy transaction based system' might seem light. In any given second there wouldn't be more than 30-40 unique transactions occuring but the data within the tables is constantly subject to change.
Maybe if you could let me know when SQL Server would think it's optimally better to lock a page rather than a row that might help me understand. I guess my concern is on our largest table new updates/inserts occur often. If multiple people are updating at the same time (while other users are selecting) I want to avoid any timeouts they may be recieving (the time-outs are from the .NET code not the DB, but we want to keep the time-out limit the same). The good thing is each user will only be updating their own particular data so we don't have to worry much about one user overwriting another's data during concurrent transactions.
There are other optimization improvements we are applying to the DB to boost overall performance but I was worried if having page locks would be slowing performance for our users, including those over-seas who already get slower responses.
Thank you for your help!! I have much to learn.
GP
March 20, 2008 at 7:36 am
Generally, in my experience, if the code is written optimally and the indexes are well designed, there's little need to fiddle with the locking mechanism.
Are you seeing lots of blocking?
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
March 26, 2008 at 1:30 pm
Hi Gila,
Yes we are seeing occasional blocking situations occurring. I am still trying to identify the root cause of the blocks. There are definite improvements that can be made to both the code and the indexes. During this venture I was reorganizing the indexes which brought me to the ALLOW_PAGE_LOCKS flag and I realized I knew nothing about it. I also haven't been able to find much definitive information elsewhere.
Rebuilding indexes seems to be much more effective (if you can schedule the time) than reorganizing. So if it's better to to leave them off and focus on index optimization I would rather do that.
Thank you!
February 16, 2011 at 9:14 am
Good (and pretty old) question indeed. Now if we choose to set them OFF in order to maintain the indexes properly what will happen? If reorganizing indexes would improve I/O performance significantly then why not setting them OFF?
__________________________
Allzu viel ist ungesund...
February 16, 2011 at 4:56 pm
One issue with this is by not using ALLOW_PAGE_Locks will result in it not being possible reorganize an index and any re-org job will fail for your database and you shall have to rebuild instead.
February 16, 2011 at 6:33 pm
GP-395290 (3/20/2008)
Maybe if you could let me know when SQL Server would think it's optimally better to lock a page rather than a row that might help me understand. I guess my concern is on our largest table new updates/inserts occur often.
In simple terms for any one transaction SQL lock the row or rows it needs to preserve data integrity. However each row lock uses some resources (memory), so if too may rows on a page are locked it becomes more economical to escalate the lock to a page lock, replacing multiple row locks with one page lock and using less resources (memory). This escalation can go up to table level if you do a big enough update.
Generally you don't want page locks, particularly not on indexes which typically have a lot of small records on each page. My recommendation is you allow SQL to manage locking. A well designed OLTP application should be making updates to very few records at a time and ideally updating indexed columns as seldom as possible (as opposed to creating new index records on an insert). This should result in row level locking only (most of the time).
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
February 16, 2011 at 6:42 pm
GP-395290 (3/18/2008)
Hello,Quick question about ALLOW_PAGE_LOCKS. Should I set this to ON for all indexes in a heavy transaction based system?
To extend my earlier post, the default setting is ON. I don't recommend you turn it off.
If you are seeing page locks on an OLTP system rather try see what is causing them and fix the cause.
If you only allow row locks you could end up using more memory to maintain locks and negativly impact on performance in other ways.
Also not being able to reorg your index is a serious drawback.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
February 17, 2011 at 2:22 am
Bungholio (2/16/2011)
Good (and pretty old) question indeed. Now if we choose to set them OFF in order to maintain the indexes properly what will happen? If reorganizing indexes would improve I/O performance significantly then why not setting them OFF?
How would setting page locks off maintain in indexes properly?
Allow page locks needs to be ON to allow index reorganisation.
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
February 17, 2011 at 2:38 am
I meant to say ON of course..just mixing up things here a little. 🙂 You're correct.
__________________________
Allzu viel ist ungesund...
February 17, 2011 at 6:11 am
The missing indexes engine is a good place to check for candidate NC indexes. Make sure to check for similar indexes already existing though - that subsystem doesn't know anything about duplicates.
Evaluating IO-costly plans and the ones causing a lot of blocking (or being blocked a lot) can lead you to good indexing opportunities too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 17, 2011 at 6:34 am
🙂 It's great to see this old post get so much attention. I think in the end we were able to reduce Page Locking considerably through query (sp) optimization.
Thanks!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply