THE SQL Server Blog Spot on the Web

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

Paul White: Page Free Space

A technical SQL Server blog from New Zealand. See also my articles on SQLperformance.com

July 2010 - Posts

  • Inside the Optimizer: Constructing a Plan – Part 4

    Summary: More undocumented ways to explore how the query optimiser works. It might surprise you to learn that most T-SQL users have written queries that disable one or more of the optimiser rules already. Join hints { LOOP | HASH | MERGE | REMOTE } and the query hints shown below are all implemented by disabling one or more optimiser rules: Read More...
  • Inside the Optimizer: Constructing a Plan – Part 3

    Summary: This post presents an undocumented Dynamic Management View we can use to identify the optimisation rules involved in producing an executable plan. In order to fully explore the way the query optimiser uses rules to construct plan alternatives, we will need a way to identify the rules used to optimise a particular query. SQL Server 2005 (later builds only) and SQL Server 2008 include an undocumented Dynamic Management View (DMV) that shows information about the internal rules used by the optimiser. By taking a snapshot of that information before running a test query, and comparing it with the post-query DMV data, we can deduce the rules invoked by the optimiser for that query. Before we get to the DMV itself, we need to nail down a few more things about the internals of the SQL Server query optimiser. The next section builds on the ‘trees and rules’ information given in part 1 of this series. Read More...
  • Inside the Optimizer: Constructing a Plan - Part 2

    Summary: Continuing the series of posts looking at how the optimiser matches and applies internal rules to refine a query plan... Read More...
  • Inside the Optimizer: Constructing a Plan - Part 1

    For today’s entry, I thought we might take a look at how the optimizer builds an executable plan using rules. To illustrate the process performed by the optimiser, we’ll configure it to produce incrementally better plans by progressively applying the necessary rules. Here’s a simple query (using the AdventureWorks sample database) that shows the total number of items in the warehouse for each of a small number of products: 1: SELECT P.ProductNumber, 2: P.ProductID, 3: total_qty = SUM(I.Quantity) 4: FROM Production.Product P 5: JOIN Production.ProductInventory I 6: ON I.ProductID = P.ProductID 7: WHERE P.ProductNumber LIKE N'T%' 8: GROUP BY 9: P.ProductID, 10: P.ProductNumber Heaps of work is done by SQL Server to parse and bind the query before it hits the query optimiser, but when it does, it arrives as a tree of logical relational operators, like this: The optimiser needs to translate this into plan that can be executed by the Query Processor. If the query optimiser did nothing more than translate the logical relational operators to the first valid form it found, we would get a plan like this: Read More...
  • Ranking Function Optimizer Transformations

    In my last post I showed how SQL Server 2005 and later can use a Segment Spool to efficiently implement aggregate window functions and the NTILE ranking function. The query optimizer is also smart enough to recognise that some queries are logically equivalent to a window function, even if they are written using different syntax. As a reminder, here's the sample data defining what the AdventureWorks report output should look like: Here's a query written to meet the same requirements, but without the SUM...OVER construction used last time: Read More...
  • Partitioning and the Common Subexpression Spool

    SQL Server 2005 introduced the OVER clause to enable partitioning of rowsets before applying a window function. This post looks at how this feature may require a query plan containing a 'common subexpression spool'. This query plan construction is required whenever an aggregate window function or the NTILE ranking window function is used. Example To illustrate, here is a simple query based on the AdventureWorks sample database. The AdventureWorks product warehouse is organised into shelves, with multiple bins per shelf. Each bin can hold several different products. We have been asked to produce a report with the following (partial) output: Notice that the total_in_bin column contains the sum of product quantities, partitioned by shelf and bin. We can meet the requirements using a query featuring the OVER clause: Read More...
  • The Segment and Sequence Project Iterators

    In my last post I promised to cover the Segment iterator in more detail, so here we go. Segment The Segment iterator partitions rows into groups as they flow through a query plan, checking whether the current row belongs in the same group as the previous row. For this to work, the incoming rows must be presented in an order which guarantees that all members of a group are received sequentially. Segment has a “Group By” argument to specify how it should partition its input. It adds an additional column as rows flow through it, which is used to communicate with its parent iterator. This extra column is named something like [Segment1003] and is visible in the graphical query plan's properties window, or by hovering your mouse cursor over the iterator (the attribute name in both cases is 'Segment Column'). Segment Top Like almost all iterators, Segment processes rows one at a time, and signals the start of a new group in the Segment Column. Let's look again at the query plan from my last post showing Segment followed by Top: Read More...
  • The “Segment Top” Query Optimisation

    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. As a simple example, based on the AdventureWorks sample database, say we need to find the minimum product quantity stored in each bin in the warehouse. Using the Production.ProductInventory table, we can write a simple aggregate query: 1: SELECT INV.Shelf, 2: INV.Bin, 3: min_qty = MIN(INV.Quantity) 4: FROM Production.ProductInventory INV 5: GROUP BY 6: INV.Shelf, INV.Bin 7: ORDER BY 8: INV.Shelf, INV.Bin; Let’s create a covering index too: 1: CREATE NONCLUSTERED INDEX nc1 2: ON Production.ProductInventory 3: (Shelf, Bin, Quantity); As you might expect, that produces a nice simple query plan: Read More...
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement