We are using partitioned views, 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 Books Online recommendations. Thanks Wasp [query plan] [view and constraint def] : /storage/temp/264-sqlplan.txt : /storage/temp/265-view_constraint.txt
Thanks for the Execution plan, here are my quick thoughts - You said that you have a partitioned view, but the query is not referring the view, its getting the records from **HEAP**, consider creating a clustered index on the table MKOnlineSeries_Value. Doing this will improve performance of other SELECT queries that access this table. - Consider creating non-clustered index on MKOnlineSeries_Value(Catalog) column with included columns as columns from your SELECT list. - If you are in Enterprise edition SQL Server is clever enough to refer the indexed views if it thinks its easy to get the data from the indexed view rather than the defined tables in the query, note this can happen even if the indexed views are not used in your querey.