Page Free Space

In my last post, Enforcing Uniqueness for Performance, I showed how using a unique index could speed up equality seeks by around 40%.

A little while back, I posted a short series on seeks and scans:

• When is a Seek not a Seek?
• So…is it a Seek or a Scan?
• Seeking Without Indexes

One of the things I highlighted in the middle post was the difference between a singleton seek and a range scan:

• A singleton equality seek always retrieves exactly one row, and is guaranteed to do so because a unique index exists to enforce it.

• A range scan seeks down the B-tree to a starting (or ending) point, and scans forward (or backward) from that point using the next or previous page pointers.

Today’s short post shows how much faster a singleton seek is, compared with a range scan, even when both return exactly the same number of records.

Question

Can a parallel query use less CPU than the same serial query, while executing faster?

The answer is yes! To demonstrate, I’ll use the following two (heap) tables, each containing a single column typed as integer:

A question that often comes up on the forums is how to get the first or last row from each group of records in a table. This post describes a clever query plan optimisation that SQL Server can use for these types of query.