June 4, 2009 at 4:46 am
Hi,
I would like to know if there is a link between any of these 3 things.
Specifically the following:
1. If the index's ALLOW_PAGE_LOCKS is ON, can this lead to more deadlocks?
2. Will setting ALLOW_PAGE_LOCKS to OFF reduce the number of deadlocks?
3. If the index's ALLOW_PAGE_LOCKS is OFF, can I still rebuild the index?
4. Does the ALLOW_PAGE_LOCKS value have any affect on the rebuilding of indexes?
Please help with these 4 questions..
June 4, 2009 at 7:09 am
Hey
http://sqlblog.com/blogs/kalen_delaney/archive/2009/05/03/controlling-lock-granularity.aspx
At the bottom of the blog gives you some info on the questions you have asked. Keep the blogger bookmark.. He writes some good stuff
JL
June 4, 2009 at 7:29 am
Kalen is a lady.
According to BOL, An index can be rebuilt with ALLOW_PAGE_LOCKS set to off, but it can't be reorganized.
It's not recommended to mess with the locking mechanisms in indexes. The optimizer does a pretty good job. More often than not, if you're experiencing lots of deadlocks, you need to adjust your code and your structure, not try to control the locking mechanisms. While turning ALLOW_PAGE_LOCKS to off can help with deadlocks, it could also make them worse. Once ALLOW_PAGE_LOCKS is set to off, the engine has to acquire a table lock in order to modify data. If you thought page locks caused contention problems, just wait until you see what table locks do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2009 at 7:36 am
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic728780-360-1.aspx
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply