THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Data Tales #5: The Case of the Rogue Index

This blog has moved! You can find this content at the following new location:

http://blog.greglow.com/index.php/2015/12/17/data-tales-5-the-case-of-the-rogue-index/

Published Thursday, December 17, 2015 8:06 AM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jchang said:

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.

December 17, 2015 10:39 AM
 

Greg Low said:

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).

December 17, 2015 4:40 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement