Bhuvnesh (10/1/2013)
Paul White (9/5/2013)
As Erland mentioned, a general-purpose index optimization pre-sorts rows on ClaimID keys for the lookup into the clustered index.Paul,
Thanks for this detailed informatrion . i read it almost 5 times to grab as much as i can but . i have one confusion on above quoted text. Why the clustered index data will be sorted here as clustered index is sorted by nature.
The rows arriving at the Key Lookup (a singleton seek into the clustered index) will not be in clustered index order without the sort. Sorting the lookups into clustered key order promotes a sequential access I/O pattern. See the following link for more information:
http://blogs.msdn.com/b/craigfr/archive/2009/02/25/optimizing-i-o-performance-by-sorting-part-1.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi