xVelocity Memory Optimized Columnstore indexes: A Gem of SQL Server 2012

April 18, 2012 at 5:06 AMSteven Wang

I was presenting at Auckland SQL User Group for the titled session.


The columnstar indexes, formerly known as project "Apollo", are new future of SQL server 2012. Columnstore indexes deliver the blazing-fast query performance, particularly for the star-joining queries. It is not surprised to notice a query performance increase up to 100 times faster. In this session, Steven will take you through the nitty-gritty and internals of columnstore indexes; he will also talk though the columnstore index do's and don'ts, limitations, tips and tricks, and the possible impact on the future BI solution considerations.

Thanks for all attendants!

I have attached the slides deck and SQL code in this blog.


ColumnstoreIndex_SQLUserGroup.pptx (1.46 mb)

SQL_Scripts.zip (15.09 kb)

Using Trace Flag 3042 for Backup Compression

October 19, 2011 at 8:56 PMSteven Wang

While we were using the SQL Server 2008 R2 native backup compression to replace the Redgate SQL Backup, we noticed a huge backup file size increase at the beginning of the backup which basically nearlly full up of our backup disk volume.

The database we were backing up close to 2 Terabyte and by using the SQL backup with compression, the final backup size was roughly 376GB as below:

When we used the SQL server native backup with compression


Backup database MyDB to disk = N'I:\Backups\CompressionBackup_Without_TF.bak' With init, compression


the initial file size has gone up to 638GB, which is roughly one third of the original database size, as below:

This is a huge 262GB backup file size increase, which was totally thrown away our Backup disk capacity plan based on the Redgate compression ratio.

Surprisingly, once the backup completed the file size shrank back to around 380GB which is similar size as the SQL backup compressed file.

This is indeed a default behaviour of the SQL Server Native backup compression. for a compressed backup, it is hard for SQL server to determine what is the final size of the backup. simply, it creates a initial backup files size with 1/3 of the database size (pre-allocation algorithm). In case during the course of the backup more disk space is needed, it will dynamycally extend the file as needed. In case the final backup size is smaller thant the initail 1/3 of the database size, it will truncate the file to the actual.

This is not good enough for us as I mentioned above that we have already planned the capacity based on the Redgate SQL Backup. In some point that we may not have enough disk space to hold the initial backup files. We really want to have the actual size required for the compressed backup.

This issue was solved by introduce a trace flag 3042. The trace flag 3042 bypasses the “pre-allocation algorithm” and grows the file as needed with small performance penalty.

This trace flag was not offically documented but a few month ago it was announced in micorosoft support see the articale Space requirements for backup devices in SQL Server

By using the trace flag, you can notice that the initil backup file size is 0 and will grow up to the final size.

Backup compression with trace falg 3042 on:


dbcc traceon(3042)

Backup database BiW to disk = N'I:\Backups\CompressionBackup_With_TF.bak' With init, compression

dbcc traceoff(3042)


The initial file size was 0 as below:

You can turn on this trace flag globally by adding the -T3042 to the startup parameters.

Posted in: Backup | Trace Flag

Tags: ,