CODEWITHSUNDEEP
sql-serverinsertsql-server-2016
phalondon
phalondon

Reputation: 305

INSERT query runs slowly when there is a non-clustered column store index

I have a big test table in my local host database (16 CPU, 124 GB RAM), which has nonclustered column store index. Every day I insert 10 million rows into this table. I found that my system runs very slowly without ending.

I see 2 queries which run parallel without ending and they make system extremely slow:

Query 1:

INSERT INTO TABLE ABC

Query 2:

ALTER NONCLUSTERED COLUMN STORED INDEX TABE ABC.

My questions:

  1. Inserting into nonclustered column store index is very slow because it inserts new records and change the index at the same time. is that correct?

  2. Do I need to disable the INDEX before INSERT and enable INDEX after INSERT to improve the performance?

i use SQL Server 2016 and this Version allows us to INSERT, UPDATE table with nonclustered column stored index.

Thank you

Upvotes: 2

Views: 1422

Answers (1)

S3S
S3S

Reputation: 25132

Those can't be running in parallel since according to the documentation:

Once you create a nonclustered columnstore index on a table, you cannot directly modify the data in that table. A query with INSERT, UPDATE, DELETE, or MERGE will fail and return an error message. To add or modify the data in the table, you can do one of the following:

  • Disable the columnstore index. You can then update the data in the table. If you disable the columnstore index, you can rebuild the columnstore index when you finish updating the data. For example:

  • Drop the columnstore index, update the table, and then re-create the columnstore index with CREATE COLUMNSTORE INDEX. For example:

EXAMPLE

ALTER INDEX mycolumnstoreindex ON mytable DISABLE;  
-- update mytable --  
ALTER INDEX mycolumnstoreindex on mytable REBUILD  


DROP INDEX mycolumnstoreindex ON mytable  
-- update mytable --  
CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable;

So yes, you need to either DISABLE before the INSERT and REBUILD after the INSERT, or DROP then index before the INSERT and CREATE it again after the INSERT. I'm guessing the runs slow and never finishes is a blocking issue seperate from this index.

If the question was more generic, for a regular NONCLUSTERED INDEX, it could be beneficial to drop and recreate the index when you are trying to insert a large number of records, like 10 million in your case, since

  • indexes slow down inserts, updates and deletes (page splits, inserting / updating multiple indexes, etc)
  • inserting that many records will likely cause a lot of of fragmentation

Upvotes: 0

Related Questions