I'm using distributed partitioned views to issue a simple query that sums sales data across two partitions. I've partitioned the data across two tables on two separate remote servers (Server1 and Server2). However, query performance remains the same as if I had all the data on one machine. When I execute a query, I notice that Server1 chugs away while Server2 sits idle. After a few minutes, Server1 goes idle and Server2 starts working. Apparently, the two parts of the union are executing serially rather than in parallel. Why aren't they running at the same time?
Unfortunately, the current release of distributed partitioned views in SQL Server 2000 Enterprise Edition doesn't run distributed queries in parallel. Each piece of the union from the SELECT statement runs serially. Certain behaviors of distributed partitioned views (such as this one) aren't documented well and might degrade overall performance. Microsoft based the current implementation of distributed partitioned views on linked SQL Servers, and when linked servers communicate, a large number of SQL calls travel between the two servers to process requests. I encourage anyone using distributed partitioned views to conduct a thorough review of a SQL Server Profiler trace output file. Review the queries on the distributed servers to be sure you understand how distributed partitioned views are processing your queries.