Infrastructure at your Service

David Barbarin

Stored procedure execution may fail with nonclustered columnstore indexes

In this blog post, let’s talk about a weird situation where I faced a nonclustered columnstore issue with SQL Server 2012. Let’s say we are in the same context than this previous blog post here. So as a reminder, we decided to benefit from the columnstore index feature after installing a new SQL Server 2012 based infrastructure at my customer shop. As showed in the previous blog, implementing columnstore indexes may introduce some challenges and I will show you a next one here.

Adding several nonclustered columnstore indexes to the existing fact tables led to modify some related ETL stored procedures. Indeed, columnstore indexes shipped with the SQL Server 2012 version are read-only and require to disable them before loading data into the underlying tables. Thus, we obviously added the corresponding DDL statements (disabling + enabling indexes) into the concerned stored procedure between the specific load data statements, basically as following:

ALTER INDEX … DISABLE

-- load data statement

ALTER INDEX … REBUILD

 

But the first execution of the stored procedure led to the following error message:

Msg 35330, Level 15, State 1, Procedure XXXX, Line 35
INSERT statement failed because data cannot be updated in a table with a columnstore index.
Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

After double checking the script, I tried to manually execute the code inside the procedure and no issue occurred in this case. In addition, running separately the stored procedure only with the data load routines and the index DDL statements was successfull. So regarding the preceding tests, I understood that the problem lies elsewhere. Indeed after some investigations, we found out that this error is basically a side effect of batch compilation because SQL Server compiles the statements of a batch into a single executable unit. So in our case, the load data statement is compiled before the columnstore index is really disabled. You may also read the following microsoft connect item to get a good explanation of this issue as well as some workarounds provided by Vassilis Papadimos. In my specific context we chosen to add the RECOMPILE hint to each data loading batch inside the procedure.

Happy development with columnstore indexes!

 

Leave a Reply


eight + = 13

David Barbarin
David Barbarin

Senior Consultant & Microsoft Technology Leader