Performance of ColumnStore query degrades when there is no GROUP BY clause - by SteveH_UK

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 761469 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 9/6/2012 9:13:24 AM
Access Restriction Public

Description

See attachment for full details.

We have a Kimball-style star schema data mart.  We have tens of millions of rows with a variety of integer and decimal data types.  We are performing aggregating SELECT queries either over the entire data in the table or over a significant slice of data. Only a single result row is expected as there is no GROUP BY clause.  We are not using SQL Server Analysis Services, only the relational engine.

Performance is disproportionately slow (observed at 15x slower than after applying workaround).   Performance appears to degrade as more columns are added.
Sign in to post a comment.
Posted by Lonny Niederstadt on 2/19/2014 at 12:51 PM
At Steve H's blog I see that his system had trace flag 834 enabled for large pages.
http://stevehorsfield.wordpress.com/2012/09/06/performance-in-sql-server-2012-degrades-when-using-columnstore-and-no-group-by-clause/
Microsoft KBa 920093 has this warning:
"If you are using the Column Store Index feature of SQL Server 2012, we do not recommend turning on trace flag 834."
http://support.microsoft.com/kb/920093

Is that recommendation related to this connect item? Does the fix mentioned above improve columnstore performance under these conditions, and is the recommendation to NOT use trace flag 834 w/columnstore rescinded in SQL Server 2014?
Posted by Gus [MSFT] on 4/26/2013 at 10:18 AM
Hello,

Thank you for submitting this feedback. This bug was fixed for next SQL Server release. The fix was implemented through our scalar batch aggregates improvement.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by SteveH_UK on 9/6/2012 at 9:28 AM
For clarification. By "more columns are added", I mean that more columns are included in the query, without changing the shape of the underlying table. We have seen the behaviour on both wide tables and relatively narrow tables, such as the one described in the attachment.