THE SQL Server Blog Spot on the Web

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

Kevin Kline

More Pages Reported Than Actually Exist in the Table

MVP Hugo Kornelius once reported that he encountered a situation in which it was possible to perform a table scan on more pages than actually existed in the heap table.  Hugo deduced that this was due to a phenomenon called “forwarding pointers”. 

 

Why in the world would this ever happen?  Real Paul Randall’s excellent blog at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/09/19/761437.aspx for a hint: 

 

“Another drawback [of heaps] is that when scanning through the heap, forwarding records have to followed immediately (as opposed to ignoring them and just reading the forwarded records when they're encountered). This is to vastly reduce the possiblity of read anomalies (such as non-repeatable reads or missed rows if a row moves before the scan point during a scan).”

 

Evidently, SQL Server chooses some times when it’s appropriate to ignore forwarding pointers for a speed advantage when scanning the entire table.

 

Interesting details, though minute, to be sure.  Enjoy,

 

-Kev


Published Friday, October 26, 2007 9:38 AM by KKline
Filed under: ,

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

 

Hugo Kornelis said:

Hi Kevin,

First, my last name is Kornelis, not Kornelius :-)

Second, SQL Server will not "choose some times" to ignore forwarding pointers; as far as I know they are always followed.

Best, Hugo

October 26, 2007 3:15 PM
 

KKline said:

Sorry about that Hugo!  I've made the appropriate connections.  Oh, and I also just now saw that you're blogging here too.  (Shows you how sloppy I am at reading other people's blogs.)  So I'll add that in as well.  Thanks for your patience,

-kev

October 26, 2007 5:04 PM
 

Jason Haley said:

October 27, 2007 9:55 AM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About KKline

Kevin Kline is Technical Strategy Manager for the SQL Server business unit at Quest Software. Kevin was the original architect and dev manager for many of Quest's SQL Server tools. Prior to Quest, Kevin worked as an enterprise DBA for a variety of large corporations and government agencies.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement