Data Tales #5: The Case of the Rogue Index

2 thoughts on “Data Tales #5: The Case of the Rogue Index”

1. hello Greg
   Not that I think it is a smart idea to disable row locks willy nilly, but if you want to speculate why,
   in my slide from CMG2004, slides 50-53
   http://www.qdpma.com/ppt/QuantitativePerformanceAnalysis_CMG2004.ppt
   in query performance of the key lookup operation versus rows, there is a major collapse in the neighbor of 3000-5000 rows.
   My speculation is that when the query optimizer estimates up to 5000 rows, its starts by taking row locks. If there is an excess number of locks, it might escalate the lock with the execution in progress, which incurs a serious performance hit, more so than just continuing with row locks. However if the estimate is greater than 5000 rows, then it starts with table lock.
   Really just a guess as I was only interested in quantifying performance at the time.
   btw, do you know why flights from the US to Aus so are expensive? A similar distance flight to some where in Asia is cheap.

   Reply

2. Sounds plausible Joe.
   No idea. I think it's lack of competition. The irony is that flying USA -> AUS return is often half the price of AUS -> USA return. (Just because they can charge it and because of arrangements with local tourism bodies).

   Reply