Writable Partition Failure

Paul White shows us a scenario in which attempts to update a writable partition could fail:

The where clause is exactly the same as before. The only difference is that we are now (deliberately) setting the partitioning column equal to itself. This will not change the value stored in that column, but it does affect the outcome. The update now succeeds (albeit with a more complex execution plan):

The optimizer has introduced new Split, Sort, and Collapse operators, and added the machinery necessary to maintain each potentially-affected nonclustered index separately (using a wide, or per-index strategy).

Read on for the reason why this happens, as well as a few solutions.

Related Posts

Max And Min Partition Values

Ken Kaufman explains a major performance problem when trying to get maximum (or minimum) values from a partitioned table: Now that I rambled a bit you want to know why when using a partitioned table does grabbing the min and max of the primary key take sooooo long, and how do you fix it.  Theoretically […]

Read More

Discovering Partition Schemes

Kennie Nybo Pontoppidan shows us how to find the partition scheme for a particular table: I needed to query SQL Servers metadata about partitioned tables, especially the column and the partition scheme used partitioning. The former is quite nicely documented in the SQL Server documentation (see link below), but the latter is not (yet). I […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031