I have a partitionned view with 3 big tables for 1 day each (10,11,12/09/2010) with a date check , (key clustered id, date), When i insert\delete date, the STATISTICS IO show that only the target table for the date is used. When we update the view :
.. the statistics show that all the tables are used ! Same thing if i delimit the date axe with variables
Is it possible to update a partionned view with a join on only the target table of the where clause ?
Are you using date constraints on the three individual tables?
answered Sep 13 '10 at 07:13 AM
srob, first of all, is there any physical or logical io on the partitions that are not supposed to be touched? I can't see that in the information you provided. It seems that only the table vw_20100912tmp has had anything happen to it (scan count 1) whereas the other seem to have no operations spent on them.
If I understand it correctly, you are doing the "old-school" style of partitioning, namely manually partitioning by creating physical tables instead of using "real" partitions as offered by SQL 2005/2008 Enterprise Edition. This means that the partition elimination abilities of "real" paritioning would not be on offer to you.
What you are seeing is SQL Server taking a quick look at the two tables it doesn't need to access along with the third table it does need to access. It cannot know which of your "partitions" is the right one until access time. With "real" partitioning this would be the case, as the partition function would tell the storage engine that it only needs to access the middle partition in your example.
From what I can see from your example, table "vw_20100911tmp" and "vw_20100913tmp" are not seeing any I/O to speak of, other than a single hit to see if the supplied filter can be satisfied by the clustered index. It then skips them and scans the third table, and only costs you significant I/O for that one.What edition & version are you on?
answered Sep 13 '10 at 07:23 AM