SQL Server partitioned views and locking

Question

We are using partitioned views (SQL Server 2008 Standard, partitioned tables are not an option), and they work fine if we consider the partition elimination goal: if we run a query in a partitioned view specifying a clause on the column we choose as the discriminator, we can see from the actual execution plan that only the table related to the specified discriminator value is hit. But we incur in locking problems if there are concurrent INSERT or UPDATE statements, even if those ones are NOT hitting the table selected by the discriminator.

Analyzing the locks I can see that, even if the execution plan shows that only the right one table is read, IS locks are still put on ALL the tables in the partitioned view, and of course if someone else has already put an X locks on one of those the whole query running on the partitioned view gets locked on that one, even if the table with an X upon is not read at all.

Is this a limitation of partitioned view in general, or there is a way to avoid it while sticking with partitioned views? We created the partitioned view and the related things following the SQL Server Books Online recommendations.

Thanks

Wasp

Answer 1

This is by design. Do not lock X entire tables.