sjimmo (5/12/2010)

---

Unfortunately, many think that a hint is law, and have spent many hours debating hints, as well as showing hints that work and then not work because the optimizeer deemed that it knew better.

sjimmo (5/12/2010)

---

Hugo,

Mine has been more in the rowlock, nolock and tablock hints. I have had cases where the optimizer determined that the tablock was more effective than a rowlock and escalated it as such. Possibly a bug, won't wswear to it as my understanding has been that this is the way that it should be.

Thanks

This is not a bug. Rowlock and pagelock are mutually exclusive, and both state to use either row locks or page locks as long as escalation to page locks is not required. The pagelock hint tells SQL Server to simply forego the stage of locking individual pages or individual rows and lock the entire table right at once.

This is not a bug. Rowlock and pagelock are mutually exclusive, and both state to use either row locks or page locks as long as escalation to page locks is not required.

Adi Cohn-120898 (5/12/2010)

---

I think that lock escalation can't escalate to page lock. If I'm not mistaken until SQL Server 2008 lock escalation could escalate directly to table lock and since SQL Server 2008 if the table is partitioned, it can escalate to lock a partition in the table (or few partitions if needed).

Adi

Hugo Kornelis (5/12/2010)

---

Well, actually hints are law. The term "optimizer hint" is highly misleading; "optimizer directive" would have been better. If you use an index hint, the optimizer WILL use that index for the query. If you specify to use hash joins, you WILL get hash joins. No matter how high the cost and how much cheaper an unhinted plan would have been. If you have any evidence to the contrary, please let me know - it's quite likely to be a bug.