1

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?]

3

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

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)
  • My apologies, the query above was the query that works (with or without the variable for ETL_VERS_START_DTM). I've updated it. When ETL_VERS_END_DTM is not included in the query it fails. If it is included in either the update or the where clause the update will succeed. But since the update doesn't need to touch the column it seems strange that it does fail. – toosuto Mar 16 '16 at 17:57
  • 1
    @toosuto So it fails when the update query can't eliminate the read-only partition because the partitioning column isn't in the where clause? Sounds reasonable, doesn't it? It's not about whether it has to write to that column it's about whether the update might potentially touch some of those rows. Maybe add a dummy where clause like annoying_name >= @start_of_read_write_range... – Aaron Bertrand Mar 16 '16 at 17:58
  • But wouldn't SQL locate the record first? The most horrible part is that the consultants would rather reset the value for partitioning column rather than get the benefit of partition elimination because they'd rather map the column value than have to create custom statements for any tables partitioned on that column. – toosuto Mar 16 '16 at 18:01
  • even better the where the records will be updated lives in a single partition. – toosuto Mar 16 '16 at 18:03
  • No, when SQL Server is building an execution plan for a query, it doesn't go find the rows first. – Aaron Bertrand Mar 16 '16 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.