October 31, 2013 at 4:00 am
I have inherited a database which is quite historic. It has over 200 indexes of which 153 do not have page locking enabled so they can not be rebuilt. I came across this as a result of poor performance on the database, the indexes have never been maintained.
I have spoken to the application owner and they see no reason why it would be set like this.
I found out that SQL 2005 SP1 row locking was selected as default.. but in 2005 sp2 it changed to page locking. So came to the conclusion that all of the indexes which do not have page locking enabled must of been created before SP2.
What would be your advise on changing this? Should i enable page locking as well as row locking? Or remove row locking and just have page locking? I am going to take a full backup prior to the change but I wasn't sure if it would have further implications if I had both page and row locking enabled.
Thanks
October 31, 2013 at 5:29 am
Hi,
Since an index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF you cannot perform ONLINE operations over that index such as REORGANIZE for e.g.
The default setting for ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS is "ON".
ROW locking is lower level than PAGE locking. SQL Server has its own mechanisms for controlling locking.
If an escalation occurs then, it'll switch from ROW to TABLE lock, skipping the intermediate PAGE level which is currently OFF. You'd better have PAGE level set to ON.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
October 31, 2013 at 5:48 am
HI,
So you would advise to turn Row Locking off?
I was going to enable page locking either way but wasn't sure whether to have both enabled or not.
October 31, 2013 at 5:52 am
SQLSteve (10/31/2013)
So you would advise to turn Row Locking off?
I wouldn't recommend that, not without a very good reason (of which to be honest there aren't many)
Default has both turned on, they should stay on unless you have some really good reason to disable one.
Indexes can be rebuild with page locking off. What doesn't work with page locking off is reorganise.
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
October 31, 2013 at 5:54 am
removed
October 31, 2013 at 5:55 am
IgorMi (10/31/2013)
If an escalation occurs then, it'll switch from ROW to TABLE lock, skipping the intermediate PAGE level which is currently OFF. You'd better have PAGE level set to ON.
Escalation is always straight to table, no matter what the settings of the index locking modes.
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
October 31, 2013 at 5:56 am
GilaMonster (10/31/2013)
SQLSteve (10/31/2013)
So you would advise to turn Row Locking off?I wouldn't recommend that, not without a very good reason (of which to be honest there aren't many)
Default has both turned on, they should stay on unless you have some really good reason to disable one.
Indexes can be rebuild with page locking off. What doesn't work with page locking off is reorganise.
Ah right ok I didn't realise that they could be rebuilt! I thought maintenance of them was completely out of the window.
Thanks for that
October 31, 2013 at 6:30 am
SQLSteve (10/31/2013)
HI,So you would advise to turn Row Locking off?
I was going to enable page locking either way but wasn't sure whether to have both enabled or not.
No, Row locking should stay ON. I additionally recommend setting Page locking to ON as well.
Igor Micev,My blog: www.igormicev.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply