Troubleshooting Distributed Partitioned Views

Editor’s Note: SQL Server Magazine contributing editor Itzik Ben-Gan submitted this response to a reader who implemented a distributed partitioned view solution with SQL Server 7.0 and used as a guide the article series by Ben-Gan and Kalen Delaney about distributed partitioned views in SQL Server Magazine ("Distributed Partitioned Views in SQL Server 2000," August 2000, "Querying Distributed Partitioned Views," September 2000, and "Modifying Views with INSTEAD OF Triggers," October 2000).

SQL Server 7.0 (as of Service Pack 2) does not produce efficient plans if you add a constraint after the table is already created because the constraint is created as a table-level constraint and not as a column-level constraint. However, Microsoft addressed this problem in SQL Server 2000. Listing 1 creates a partitioned view and shows the plan produced by SQL Server 7.0 and the plan produced in SQL Server 2000. First, Listing 1 creates a partitioned view where constraints are added at table creation time.

The queries in Listing 2 show that both SQL Server 7.0 and SQL Server 2000 handle direct queries that filter data by efficiently using the partitioning column. Next, Listing 3 creates a partitioned view where constraints are added as table-level constraints after table creation time. The queries in Listing 4 show that SQL Server 7.0 doesn’t handle direct queries that filter data using the partitioning column efficiently, whereas SQL Server 2000 does.

Queries that you issue in a stored procedure by using a parameter provided to the stored procedure to filter data produce an inefficient plan in SQL Server 7.0 but produce an efficient plan in SQL Server 2000. To create an example that demonstrates this behavior, first use Listing 1 to recreate the partitioned view. Next, create the stored procedures that Listing 5 shows.

Now execute the first stored procedure, as Listing 6 shows, which issues a direct query against the partitioned view by using the parameter supplied in the WHERE clause. Note the differences in the plans between SQL Server 7.0 and SQL Server 2000:

Dynamic execution using the EXEC statement uses an efficient plan both in SQL Server 7 and in SQL Server 2000, as Listing 7 shows.

In "Querying Distributed Partitioned Views" (September 2000), Figures 2 and 3 show an autoparameterized plan for a query against a distributed partitioned view. You can think of such an autoparameterized plan as a template for all possible execution flows for the same query with different values in the WHERE clause. The value used in the actual query determines, at run time, which of the branches of the plan will be executed and which won’t. We produced these plans with SQL Server 2000 beta 2 when we wrote the article. By the final release of SQL Server 2000, the programmers determined that autoparameterization for queries issued against more than one table didn’t cause a significant performance hit. They detected almost no measurable difference in execution times between the autoparameterized plans that performed partition elimination at run time and the plans that performed partition elimination at compile time, so they decided not to autoparameterize such queries.