Resolving Distributed Partitioned Views

  • Article

The SQL Server 2005 query processor optimizes the performance of distributed partitioned views. The most important aspect of distributed partitioned view performance is minimizing the amount of data transferred between member servers.

SQL Server 2005 builds intelligent, dynamic plans that make efficient use of distributed queries to access data from remote member tables:

  • The query processor first uses OLE DB to retrieve the CHECK constraint definitions from each member table. This allows the query processor to map the distribution of key values across the member tables.
  • The query processor compares the key ranges specified in an SQL statement WHERE clause to the map that shows how the rows are distributed in the member tables. The query processor then builds a query execution plan that uses distributed queries to retrieve only those remote rows that are required to complete the SQL statement. The execution plan is also built in such a way that any access to remote member tables, for either data or metadata, are delayed until the information is required.

For example, consider a system where a customers table is partitioned across Server1 (CustomerID from 1 through 3299999), Server2 (CustomerID from 3300000 through 6599999), and Server3 (CustomerID from 6600000 through 9999999).

Consider the execution plan built for this query executed on Server1:

SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID BETWEEN 3200000 AND 3400000

The execution plan for this query extracts the rows with CustomerID key values from 3200000 through 3299999 from the local member table, and issues a distributed query to retrieve the rows with key values from 3300000 through 3400000 from Server2.

The SQL Server 2005 query processor can also build dynamic logic into query execution plans for SQL statements in which the key values are not known when the plan must be built. For example, consider this stored procedure:

CREATE PROCEDURE GetCustomer @CustomerIDParameter INT
AS
SELECT *
FROM CompanyData.dbo.Customers
WHERE CustomerID = @CustomerIDParameter

SQL Server 2005 cannot predict what key value will be supplied by the @CustomerIDParameter parameter every time the procedure is executed. Because the key value cannot be predicted, the query processor also cannot predict which member table will have to be accessed. To handle this case, SQL Server builds an execution plan that has conditional logic, referred to as dynamic filters, to control which member table is accessed, based on the input parameter value. Assuming the GetCustomer stored procedure was executed on Server1, the execution plan logic can be represented as shown in the following:

IF @CustomerIDParameter BETWEEN 1 and 3299999
   Retrieve row from local table CustomerData.dbo.Customer_33
ELSEIF @CustomerIDParameter BETWEEN 3300000 and 6599999
   Retrieve row from linked table Server2.CustomerData.dbo.Customer_66
ELSEIF @CustomerIDParameter BETWEEN 6600000 and 9999999
   Retrieve row from linked table Server3.CustomerData.dbo.Customer_99

SQL Server 2005 sometimes builds these types of dynamic execution plans even for queries that are not parameterized. The optimizer may parameterize a query so that the execution plan can be reused. If the optimizer parameterizes a query referencing a partitioned view, the optimizer can no longer assume the required rows will come from a specified base table. It will then have to use dynamic filters in the execution plan. For more information, see Simple Parameterization.