THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

Content now hosted at
More of my SQL Server technical articles are at

Browse by Tags

All Tags » Row Goal

  • 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. The ‘Top N’ Sort Thinking about how this requirement might be implemented in an executable query plan, we might expect to see a Sort iterator followed by a Top. In reality, the query optimizer can often collapse these two related operations into a single iterator: a Sort iterator running in Top N Sort mode: That’s an idea you might find familiar if you read my previous post on Row Goals and Grouping. In that entry, we saw how a Sort followed by a Stream Aggregate can sometimes be collapsed into a Sort iterator running in Sort Distinct mode. The General Sorting Algorithm SQL Server’s normal sorting algorithms are suited to a very wide range of ordering requirements. They work extremely well regardless of the data types involved, the size of data to be sorted, or the number of sort keys specified. They also make good use of available memory resources, and can spill to tempdb if required. It is a common misconception that SQL Server will try to perform a sort entirely in memory if it can. In fact the algorithms used are much more complex: they aim to achieve a balance between memory usage, average response time, while maintaining high levels of resource concurrency. Memory is a precious resource in the server, so SQL Server may spill a sort to tempdb, even if sufficient main memory is available. Read More...
  • Row Goals and Grouping

    You might recall (from my last post) that query plans containing a row goal tend to favour nested loops or 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 from its second input. Hash join therefore has a high start-up cost, which is balanced by a lower per-row cost once probing begins. In this post, I’ll take a look at how row goals affect grouping operations. Grouping Strategies While the start-up cost of hash join often makes it unsuitable for plans with a row goal, there are times when hashing operations may feature in such plans, since the Hash Match iterator also supports a streaming mode. As an example, say we are asked to list one hundred unique first names from the AdventureWorks Contacts table: Read More...
  • Inside the Optimizer: Row Goals In Depth

    Background One of the core assumptions made by the SQL Server query optimiser’s model is that clients will consume all of 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. Let’s look at an example: The optimiser chooses to perform the logical join using a Hash Match physical iterator, resulting in a plan with a total estimated cost of around 1.4 units. By forcing alternative physical joins using a query hint, we see that a plan based on Sort-Merge would have an estimated cost of just under 10, and using Nested Loops would cost over 18 units. All these cost estimates are based on the assumption that all rows are required. Hash Match As detailed in a previous post, the Hash Match iterator starts by consuming all rows produced by its build input (the Product table) in order to build a hash table. This makes Hash Match a semi-blocking iterator: it can only start producing output rows once the build phase is complete. If we need the first few rows from the query quickly, this join type may not be optimal. Read More...
Privacy Statement