Hi Folks,
The fifth in my Data Tales series has been published at SQL Server Magazine. It’s the Case of the Rogue Index. You’ll find it here:
http://sqlmag.com/database-administration/data-tales-5-case-rogue-index
Enjoy!
Thoughts from Data Platform MVP and Microsoft RD – Dr Greg Low
Hi Folks,
The fifth in my Data Tales series has been published at SQL Server Magazine. It’s the Case of the Rogue Index. You’ll find it here:
http://sqlmag.com/database-administration/data-tales-5-case-rogue-index
Enjoy!
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.
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).