Performance in SQL Server 2012 degrades non-linearly as SELECT clauses are added when using ColumnStore

When adding additional SELECT clauses to an aggregating query over a large fact table and using ColumnStore, the performance degrades in a step-wise linear fashion with large steps.  It may be quicker to execute several less-complex queries rather than a single complex query.

I’ve submitted a Microsoft Connect bug report here:
https://connect.microsoft.com/SQLServer/feedback/details/761895/
.

Continue reading for full details, or download this attachment:
https://www.box.com/s/5hp9f0ditg0fspghu506
[PDF file also available via Microsoft Connect].  Actual test results, steps to reproduce, etc., as well as more pretty graphs :), are included.

Performance in SQL Server 2012 degrades when using ColumnStore and no GROUP BY clause

Essentially, the performance of a non-grouping SQL SELECT query degrades when applied to a ColumnStore index.  This has been tested with SQL Server 2012 RTM CU1.  Performing partial aggregation can result in a 15x performance improvement in some of the cases I observed.

See the full details in my Microsoft Connect submission here:
https://connect.microsoft.com/SQLServer/feedback/details/761469/
. Or download the details here:
https://www.box.com/s/frf7imhyclb2efz2tfvb
.

HTTP and “Package Response”

This is a technical post and is probably worth skipping if you don’t have a background in IT.

The HTTP protocol is moderately efficient at single transmissions that it was originally designed for, such as the HTTP GET requests that are used to retrieve a web pages HTML source.  However, this moderate efficiency is signficiantly reduced by the actual behaviour and semantics of website content and browsers.