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 » Top

  • Parallel Execution Plans Suck

    This article has permanently moved to
  • 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