Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When attempting to run my Maintenance Plan, I receive the following error:

Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized because page level locking is disabled."

We currently have Row Level locking enabled on this Index. I can enable Page Level locking, but I am unsure what the repercussions are.

My Question Is: What is the difference between the two locking schemes, and what are their real-world (in production) consequences?

share|improve this question

migrated from serverfault.com Oct 4 '11 at 19:06

This question came from our site for professional system and network administrators.

    
As my answer wasn't the direction that I needed it to go, I've blown it away. I'll try and get back and put the answer as I intended it to be. –  mrdenny Oct 5 '11 at 18:37

2 Answers 2

up vote 11 down vote accepted

Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized because page level locking is disabled."

The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. The only way to defragment without page locks is to lock the entire partition, which is not possible for REORGANIZE as its online only.

What is the difference between the two locking schemes, and what are their real-world (in production) consequences?

You need to grasp what a record and page are to evaluate the impact of disallowing a particular lock type. If you are unfamiliar with SQL Server storage internals, start with Anatomy of a Record and Anatomy of a Page. Put very simply:

  • rows = records
  • rows are stored in pages of 8kb

If you were to alter the permitted lock types:

  • Disable page locks = Row and table locks only
  • Disable row locks = Page and table locks only
  • Disable both = Table locks only

There are two scenarios I'm aware of where it can be beneficial to disallow a lock type. Doesn't mean there aren't others, hopefully someone else will step in with examples.

A frequently accessed lookup table, that changes infrequently - By disabling both page and row level locks, all readers will take a shared table lock. This is faster/cheaper rather than the usual intent-shared on the table, followed by intent-shared on a page and finally a shared lock on a specific row or rows.

Preventing a specific deadlock scenario - If you encounter deadlocks caused by concurrent processes acquiring locks that are frequently on the same page, disallowing row locks results in page locks being taken instead. Only one process can then access the page at a time, the other must wait.

The first example is micro-optimisation and unlikely to yield measurable benefit on a typical system. The second will solve that particular deadlock scenario but may introduce unexpected side effects e.g. killing concurrency in a different section of code. Difficult to assess the impact fully, approach with caution!

The default is for both to be enabled and this should not be changed without good cause.

share|improve this answer

Probably nothing. I'm sure MS knows better than you or I

I've worked on high volume OLTP systems and never felt a need to change the settings. A deadlock should be retried because they'll happen anyway

Quoting from SQL Server Storage Engine blog, "Lock Escalation in SQL2005"n which is worth reading completely anyway.

By default, we have both ROW and PAGE locks enabled...SQL Server chooses ROW lock granularity for most cases but may choose PAGE lock where appropriate. So for the case you specified, ROW lock is likely. There is no way to turn off PAGE locking at database or instance level. Are you encountering blocking due to PAGE locks?

I reckon that if you force rowlocks only then you'll consume resources that could be used more effectively elsewhere. If your load is high enough that it matters, then why consume memory? The blog article goes into this

I suspect there is some superstition behind this, just like these:

share|improve this answer
    
+1 but: deadlocks in OLTP systems can be prevented; my system runs deadlock-free for months, although we deploy 2-3 times a week. –  AlexKuznetsov Mar 2 '12 at 18:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.