Clustered Columnstore Indexes – part 26 (“Backup & Restore”)

Continuation from the previous 25 parts, starting from

This time I decided to take a look at how having Columnstore Indexes in a database is affecting database backup & restore procedures. I decided to take a simple backup from the Contoso Retail DW database to just 1 file on a local drive in order to have some kind of a baseline. Naturally in a real production environment we would be able to go for multiple drives as well as multiple backup files which would accelerate the overall performance, but for a simple test I will use my local VM with 2 cores of I7 as well as a SSD drive.

For the first step in this exercise, I would be enabling checksums and compression by default:

To be fair, I have rebuilt Page-compressed tables at our database, by running the following script, which includes some basic update for the database work space:

Now to the Backup command itself, which will be a classic one, generated by the SSMS:

and this is the Restore command that I have been using in order to measure performance:

I have executed both backup and restore commands a number of times to measure the average performance and I have to say that the results were pretty consistent.

Before creating Clustered Columnstore Indexes on those 5 tables, I will execute the following script in order to make sure that I am running a fair test:

Remove all primary and foreign keys, since they are not compatible with Clustered Columnstore Indexes:

Lets create our Clustered Columnstore Indexes:

Now here are the results of my tests and just take a look at those pictures:
Screen Shot 2014-02-09 at 11.31.37

The backup size is absolutely a no-brainer: Columnstore compression simply wipes out any competition. Even though the storage is getting cheaper, it is nice to have backups that are occupying less space – and this way you might simply have more backup history stored at no additional cost. 🙂

Screen Shot 2014-02-09 at 11.30.37
The results of the backup are pretty impressive, with Columnstore showing that it simply wins by a double margin with Page compression being the closest competition. Judging by what I see – backing up a database with Columnstore Indexes might make some good improvement on your backup times.

Screen Shot 2014-02-09 at 11.31.32
Restore – I was actually expecting Columnstore backup performing worse than a Page-based one, since a non-compressed backup restore outperforms greatly a compressed one (9,1 seconds on my setup).
As a matter of a fact PAGE Compression backup restore perform here a little bit worse than Columnstore Indexes backup and I have had to make a good number of tests to be able to reconfirm it.
This might have to do with a speed of the drive (SSD), but if we look at it logically – a slower drive on a server would make even more win for the Columnstore indexes, as long as you have a couple of free CPU cores, which should be the case on a good server.

The slower your backup/restore disks are – the more improvements you will get from using Columnstore Indexes, there is absolutely no doubt about it.

updated on 10th of February 2014:

I have executed some tests backing up data to the USB drive, to be able to compare with the backups to the fast drive. 🙂
Here are the results and as expected the difference gets bigger and bigger according to the performance of the drive.

Screen Shot 2014-02-10 at 12.16.42

Screen Shot 2014-02-10 at 12.16.21

to be continued …

Leave a Reply

Your email address will not be published. Required fields are marked *