SQL Noob (10/26/2009)
is there any hard documentation on what the tipping point is for an index seek to become an index scan? is it 5% of rows affected, 10% or some other percentage?
It usually tips somewhere between 0.5%-1%. It's not a hard-coded value, it depends on a whole bunch of factors, but it is a very, very low percentage.
This is covered in either part 2 or 3, there's also a post on my blog - http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability