Querying Distributed Partitioned Views

Editor's Note: This is the second article in a three-part series about distributed partitioned views in SQL Server 2000.

Distributed partitioned views are the core component of Microsoft's scale-out strategy. (For more information, see "Scalability Wars," page 7.) In "Distributed Partitioned Views," August 2000, we introduced SQL Server 2000's distributed partitioned view technology and demonstrated how to set up and modify the views. In this article, we demonstrate how to query distributed partitioned views. We show you the execution plans for various queries so you can see how SQL Server distributes queries across the nodes. (You can download the code listings from the August article, which shows you how to set up the distributed partitioned views we use in this example, by entering InstantDoc ID 9086 at http://www.sqlmag.com/ and opening the 9086.zip file.)

Let's prepare to query the view that we created in last month's article by using the following script to populate the Orders view with all the rows from the Orders table in the Northwind database:

— Populate the Orders view
USE testdb
INSERT INTO Orders
  SELECT * FROM Northwind.dbo.Orders

Next, let's start the querying demonstration by executing simple select statements that retrieve all the rows from the Customers view.

SELECT * FROM Customers

Now look at the execution plan that Figure 1 shows. Notice that these statements query the local table CustomersAF. SQL Server issues a remote query against each of the other tables: Node2.testdb.dbo .CustomersGP and Node3.testdb.dbo.CustomersQZ. SQL Server concatenates the result of the queries and returns the result to the user.

Next, let's perform a query that requests rows only from the local table.

SELECT *
FROM Customers
WHERE customerid = 'ALFKI'

Now look at the execution plan for this query, which Figure 2 shows. The Startup Filters (the yellow filters in the plan) are the key building blocks that the optimizer uses to determine whether, at runtime, SQL Server needs to go to a particular node to get rows. If you hold your mouse pointer over the local Clustered Index Scan or over either of the two remote queries, you'll notice that the graphical execution plan incorrectly shows that SQL Server executed each of the queries once. But look at the textual output, which Figure 3, page 42, shows, of the statistics profile for the same query. This output (Executes = 0) shows that SQL Server executed neither of the remote queries. This fact significantly affects query performance. SQL Server accessed only the servers that contained rows that the query needed. Note that SQL Server won't always use the startup filter to determine at runtime whether it needs to access a remote node. SQL Server auto-parameterized the query above. In other words, SQL Server placed the query's execution plan in cache with a parameter for the customerid column that the example used in our search criteria. SQL Server reuses auto-parameterized plans when it runs a similar query with a different value in the search criteria, thus avoiding the expense of generating a new plan. For example, if you run the query above but replace the customer ID with another one, SQL Server will reuse the auto-parameterized plan.

SELECT *
FROM Customers
WHERE customerid = 'OLDWO'

The execution plan looks the same as the last one, of course, because it's the same plan. But if you look at the output of the statistics profile, you'll notice that SQL Server executed the remote query against Node3 once, whereas SQL Server didn't execute the queries against the local server (Node1) or the remote server (Node2). SQL Server is conservative in determining which queries to auto-parameterize. If even a slight chance exists that two queries that are similar but have different values in the search criteria will require different plans, SQL Server won't auto-parameterize the plans. For example, consider the following query and its execution plan, which Figure 4 shows:

SELECT *
FROM Customers
WHERE customerid = 'ALFKI'
   OR customerid = 'ANATR'