Disabling Non-Clustered Columnstore Index doesn’t appear to execute in Stored Procedure in SQL 2012

| 0 comments

Consider the following scenario, you have a large fact table with non-clustered columnstore index. In SQL 2012, Columnstore index is not updatable hence at night when you load or purge the table, the first step would be to disable non-clustered columnstore index followed by insert/update/delete to the table and the final step would be to rebuild the non-clustered columnstore index to ensure the table is available for querying & reporting on the following day to give you the desired reporting performance.

The Stored Procedure which is used to load or purge the table would be of the form as shown

CREATE PROCEDURE dbo.usp_Update_Fact_table

AS

BEGIN

AlterIndex [csindex_wk_fact] ON [dbo].[Wk_Fact] disable;– Disable Columnstore Index before Update

UPDATE [dbo].[Wk_fact] – Update Fact Table

Alterindex csindex_wk_fact  ON dbo.Wk_Fact rebuild;– Rebuild Index After Index

END

When you execute the above stored procedure, it fails with following error

UPDATE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the UPDATE statement, then rebuilding the columnstore index after UPDATE is complete.

The above error message might appear that ALTER INDEX… DISABLE statement is skipped during the execution of stored procedure and the stored procedure throws the above error while executing the UPDATE query. But the error message doesn’t occur during execution, it actually occurs during compilation of the stored procedure.

When the stored procedure is being compiled, it checks that the Fact table has non-clustered columnstore index which cannot be modified and hence throws the above error at the compile time. This can be further verified by trying to get the estimated showplan of the stored procedure which fails with the same error as above.

SET SHOWPLAN_XML ON
EXEC dbo.usp_Update_Fact_table

The Query Optimizer detects the Non-Clustered Columnstore index during the Query Optimization phase of the Query Plan generation and is an optimization to prevent plan generation to save some resources required to compile, execute and cache the query plan which might ultimately error out in the execution phase.

To overcome the above error, you have following options

  1. Run ALTER INDEX….disable outside stored procedure prior to the insert/update/del stored procedure as a first step of the Batch Load/purge job.
  2. If you need to run the disable index as part of same stored procedure, you can execute the DML to Fact table as dynamicSQL which will avoid the compile time check as shown below

    CREATE PROCEDURE dbo.usp_Update_Fact_table
    AS
    BEGIN

    Alter Index [csindex_wk_fact] ON [dbo].[Wk_Fact] disable;– Disable Columnstore Index before UpdateEXEC sp_executesql  ‘UPDATE [dbo].[Wk_fact]‘;– Update Fact Table as Dynamic SQL

    Alter index csindex_wk_fact  ON dbo.Wk_Fact rebuild;– Rebuild Index After Index

    END

  3. Upgrade to SQL 2014 or above as the columnstore Index is updatable in SQL 2014 and above version where these compile checks do not occur.

Hope this helps !!!

Parikshit Savjani
Senior Premier Field Engineer

Leave a Reply

Required fields are marked *.