SQL Server 2008 R2 Distributed Partition View Update/Delete issue

I have a big data table for store articles(has more than 500 million record), therefore I use distributed partition view feature of SQL Server 2008 across 3 servers.

Select and Insert operations work fine. But Delete or Update action take long time and never complete.

In Processes tab of Activity Monitor, I see Wait Type field is "PREEMPTIVE_OLEDBOPS" for Update command.

Any idea what's the problem?

Note: I think problem with MSDTC, because开发者_StackOverflow中文版 Update command not shown in SQL Profiler of second server. but when check MSDTC status on the same server, status column is Update(active).

What is most likely happening is that all the data from the other server is pulled over to the machine where the query is running before the filter of your update statement is applied. This can happen when you use 4-part naming. Possible solutions are:

• Make sure each table has a correct "check constraint" which defines the minimum and maximum value of the partition table. Without this partition elimination is not going to work properly.
• Call a stored procedure with 4-part naming on the other server to do the update.
• use OPENQUERY() to connect to the other server

To serve 500 million records your server seems to be adequate. A setup with Table Partitioning with a sliding window is probably a more cost effective way of handling the volume.