We have a table, which is partitioned through a date field into separate years.
There is a view over all of these tables (Call)
Schema is as follows:
The update to the table is as follows:
The problem then comes with the execution plan. There are huge IO costs to the operation, and I've nailed it down to how SQL is dealing with the update.
Currently we have 20 of these tables partitioned per year. Each update is causing an update of every single table's indexes, regardless of whether the table is actually touched by the update operation or not.
Below this section it goes on to update, in the exact same manner, every table in the view.
I cannot see why this is, as I have specified the Table_Year (which the table is partitioned on) within the query text. Shouldn't SQL only update the necessary table?
By GeorgeP 16 asked Aug 04, 2014 at 09:45 AM
These aren't actually partitioned tables, and even if they were, partition elimination wouldn't really work for updating indexes unless all indexes were also partition-aligned.
Since you are using Express Edition and can't actually use partitioning, I have a different approach to recommend: dynamic UPDATEs that only affect the table(s) represented in @StartDate / @EndDate. You'll have to populate the list of parameters twice; once with their data types - this should be easy as I presume these are declared somewhere already.
By Aaron Bertrand ♦ 1.7k answered Aug 04, 2014 at 02:08 PM
The view meets all the partitioning requirements for both
In fact, rows could not move between partitions in this case because there is a 1:1 relationship between
The new value for
All these consideration lead to a plan that does not static feature partition elimination. It does, however, feature dynamic partition elimination:
On the reading side, the string of Filter operators immediately below the Concatenation are all startup filters. They evaluate their predicate before the subtree is executed. If the predicate evaluates to false, the subtree under the Filter is not executed.
The overall effect is that only tables under the view that could hold qualifying rows (depending on the runtime variable values) are accessed. Notice the execution plan only shows rows being read from one of the base tables, and the Actual Executions property for all other base tables is zero; these operators were not executed at all, at runtime. In the fragment below, the startup Filters mean only the green operators execute; the red ones never start at all:
On the writing side, The normal (not startup) Filter just to the right of each Clustered Index Update operator ensures that only changes for the current table are passed on. In the example plan, only one Clustered Index Update (and its associated nonclustered index maintenance operators) receives any rows:
By SQLkiwi ♦ 6.6k answered Aug 05, 2014 at 06:33 PM