About This Blog

A copy of my content originally hosted on SQLBlog.com
Showing posts with label Row Goal. Show all posts
Showing posts with label Row Goal. Show all posts

Friday, 27 August 2010

Sorting, Row Goals, and the TOP 100 Problem

Sorting, Row Goals, and the TOP 100 Problem

When you write a query to return the first few rows from a potential result set, you’ll often use the TOP clause.

To give a precise meaning to the TOP operation, it will normally be accompanied by an ORDER BY clause. Together, the TOP…ORDER BY construction can be used to precisely identify which top ‘n’ rows should be returned.

Sunday, 22 August 2010

Row Goals and Grouping

Row Goals and Grouping

You might recall from Inside the Optimizer: Row Goals In Depth that query plans containing a row goal tend to favour nested loops or sort-free merge join over hashing.

This is because a hash join has to fully process its build input (to populate its hash table) before it can start probing for matches on its other input. Hash join therefore has a high start-up cost, balanced by a lower per-row cost once probing begins.

In this post, we will take a look at how row goals affect grouping operations.

Wednesday, 18 August 2010

Inside the Optimizer: Row Goals In Depth

Inside the Optimizer: Row Goals In Depth

Background

One of the core assumptions made by the SQL Server query optimizer cost model is that clients will eventually consume all the rows produced by a query.

This results in plans that favour the overall execution cost, though it may take longer to begin producing rows.