Error Updating Record on Table with Partition on a Read-Only Filegroup

Question

We are working to partition our data warehouse (SQL Server 2012 Enterprise): moving old records we don't expect to change to a read-only filegroup on a monthly basis. We're getting errors that records cannot be updated even when the record is in the read-write filegroup and would not move to the read-only partition (we are not updating the partitioning column) due to the update.

The partitioning column is ETL_VERS_END_DTM. The following update statement fails as if the row needed to be moved to partition 1 (values less than 1899-12-31 23:59:59 which is expected to be an empty partition).

UPDATE  DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT
SET     ETL_JOB_SEQ_NUM = 1027140 ,
        ETL_IUD_CD = 'D' ,
        ETL_UPD_DTM = CONVERT(DATETIME, '03/16/2016 01:01:17')
WHERE   ETL_VERS_START_DTM = CONVERT(DATETIME, '12/27/2014 00:26:24.137')
        AND ACCT_NO = '5001194157'
        AND ACCT_TYPE = 'ILN';

Error:

Msg 652, Level 16, State 1, Line 1 The index "PK_STG_PHX_LN_ACCT_INT_OPT" for table "dbo.STG_PHX_LN_ACCT_INT_OPT" (RowsetId 72057594415022080) resides on a read-only filegroup ("ARCHIVE"), which cannot be modified.

Explicitly setting the partitioned column to its own existing value or putting anything in the where clause that gets us the correct partition elimination removes the condition that causes this error. Neither solution is considered a 'fix' for the issue by the vendor or our data warehouse admin. What is causing SQL Server to attempt to move the record if the column value is not changing?

UPDATE: So my problem is me misunderstanding that if the execution plan thinks it might touch the read-only partition it will error out (even though no records have been touched). The problem ultimately is that the database was not built originally (or loaded) with a partitioning as a consideration as well as lack of desire to customize update processes by our DW team. Partitioning is being built in to manage maintenance times as the database continues to grow due to a business requirement to never delete anything ever. [Did that sound a little bitter?]

Answer 1

Ordinarily, SQL Server only checks for a read-only filegroup just before attempting to write to the partition. No error is thrown when reading from a read-only filegroup, as you would expect.

SQL Server can therefore touch any rows it likes, so long as only rows located on read-write filegroups qualify for the update. To put it another way: SQL Server can fully scan all partitions (e.g. using a Clustered Index Scan) looking for rows to update, so long as the only rows that arrive at the Clustered Index Update are on read-write partitions.

In this scenario, there are two separate storage engine rowsets involved: one for the read, and one for the update. The reading rowset will not throw an error if it encounters a read-only partition because it is only reading. The writing rowset will throw an error, because it is configured to perform changes.

The catch

In execution plan shapes where there is no blocking operator between reads and writes to the base table, SQL Server may apply an optimization ("Rowset Sharing"), whereby the two operators share a single storage engine rowset.

In this situation, reading from a read-only partition will throw the error reported in the question. The single rowset is configured for both read and write - touching a read-only partition results in the error.

Adding a key from the unique clustered index to the set clause means a split, sort, collapse combination is required to avoid transient key violations. As a side-effect, the sort (a blocking operator) prevents the rowset sharing optimization from being applied.

For more details, see my article Changes to a Writable Partition May Fail Unexpectedly

Answer 2

Maybe SQL Server is making a terrible guess because the convert is happening inline. What if you used a variable or parameter (and stopped using ambiguous formats that require regional and other settings to be taken into account)?

DECLARE @d DATETIME = CONVERT(DATETIME, '20141227 00:26:24.137', 112);

UPDATE  DW_STAGE_PHOENIX.dbo.STG_PHX_LN_ACCT_INT_OPT
SET     ETL_JOB_SEQ_NUM = 1027140 ,
        ETL_IUD_CD = 'D' ,
        ETL_UPD_DTM = CONVERT(DATETIME, '20160316 01:01:17', 112)
WHERE   ETL_VERS_START_DTM = @d
        AND ACCT_NO = '5001194157'
        AND ACCT_TYPE = 'ILN';

It might even be better to always include a dummy clause to explicitly eliminate the archived partition:

AND ETL_VERS_START_DTM >= CONVERT(DATETIME, '19000101', 112)

• Bad habits to kick : mis-handling date / range queries