asked
Partitioned views and locking
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][1] [view and constraint def][2] [1]: /storage/temp/264-sqlplan.txt [2]: /storage/temp/265-view_constraint.txt
Can you please post the VIEW definition and the CHECK constraint definition on the discriminator column?
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.
Thanks for your answer. Not sure I follow you when you say that the query is not referring the partitioned view, the query (which is a sample) is "select top 200 * from SeriesValues where Catalog = 'MKON'", and SeriesValues is the partitioned view, which is a union of 4 tables, among them MKonline_SeriesValue... Unluckily, no Enterprise Edition...
I thought your tables are partitioned(
http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx), this is only available in Enterprise edition. I guess you are doing a horizontal partitioning. So its enough to create the recommended index.
10 People are following this question.
