About This Blog

Including my content from SQLBlog.com and some from SQLPerformance.com
Showing posts with label Execution Plan. Show all posts
Showing posts with label Execution Plan. Show all posts

Thursday 4 August 2011

Avoiding Uniqueness for Performance

Avoiding Uniqueness for Performance

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

Friday 29 July 2011

Enforcing Uniqueness for Performance

Enforcing Uniqueness for Performance

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

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.

Thursday 7 July 2011

Bitmap Magic (or… how SQL Server uses bitmap filters)

Bitmap Magic (or… how SQL Server uses bitmap filters)

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:

Tables #BuiltInt and #Probe

Wednesday 28 July 2010

The “Segment Top” Query Optimization

The Segment Top Query Optimization

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.