Deleting rows in a 120 million columnstore indexed table

Hi Folks, I have an OLTP database Events with a single table Eventlogs having 20 columns with Eventid (int) , Logid (uniqueidentifier) and rest(timestampstart, timestampend etc ) as nvarchar. There is a primary key on Logid (uniqueidentifier). This is SQLl2012

Every night rows more than 3 weeks old are copied by an SSIS process to a reporting server into an archive table and database with the same name as the OLTP database. The rows are then deleted in the OLTP database.

This reporting database is now a 366 GB table with 120 million rows holding data from 2012. There is a nonclustered columnstoreindex on id and 4 other columns and a unique nonclustered index on the logid(uniqueidentifier).

The 1.6TB reporting disk is now almost full and I have been tasked to free up some disk space. There is a requirement to now keep only recent 2 years data in this archive table which means deleting around 70 million rows.

What is the best way to proceed ? The data can be deleted in batches of 10 GB week . The Eventid seems to increment by 1 but am not sure it is the same across 120 million rows. Thank you for your help in advance.

more ▼

asked Dec 11, 2017 at 04:01 PM in Default

avatar image

0 1

@mail_sady Is it at all possible to use a partition switch to delete that many rows? There is an answer to the similar question (pre-columnstore indexes times though) by @Scot Hauder, please have a look at it. Partition switch makes the delete operation much faster because no data is moving anywhere, just meta is changed, so it might be worth it to consider dropping NCCI, deleting rows via partition switch and then creating NCCI once all is done.

Level 6 of the Stairway to Columnstore Indexes by Hugo Kornelis discusses the consequences of deleting data in columnstore indexes in great detail. Of course, it goes without saying that the entire stairway (13 levels), just like anything else written by Hugo is very well worth thorough reading.

Dec 11, 2017 at 05:17 PM Oleg

Hi Oleg,

Thank you for your reply. I will look at the links in your reply and see how far I can go. I need to read up on Partition switch as I haven't come across that before. I will post my progress . Thanks once again.

Dec 12, 2017 at 08:30 AM mail_sady
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi Oleg,

I tried the partition method as described by Scot Hauder. I tried in on a sample of 2 million rows from the same table and it succeeded. I then tried it on the actual table , but during the step to create a clustered index ,the data file grew and filled up the disk and stopped. I had to delete the database and restore it from the backup.

I guess now either I will have to move the data file to another disk to carry out the process or better still , restore the back up on another server and try it out( whether we have another non prod server with so much disk space is another issue).

But thanks for your pointers, I have learnt a neat trick.

more ▼

answered Dec 14, 2017 at 09:15 AM

avatar image

0 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 11, 2017 at 04:01 PM

Seen: 59 times

Last Updated: Dec 14, 2017 at 09:16 AM

Copyright 2018 Redgate Software. Privacy Policy