Home Dashboard Directory Help
Search

Poor query plan generated for SQL Server 2014 clustered columnstore index by irabufan


Status: 

Active


3
0
Sign in
to vote
Type: Bug
ID: 808566
Opened: 11/14/2013 8:22:34 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

We are testing with clustered columnstore indexes on SQL 2014 CTP2 in our data warehouse to see how its performance stacks up compared to traditional rowstore type tables. However, we ran into a problem with one of the commonly used queries right away as it performed much worse compared to a traditional rowstore table. I was also able to replicate the issue using AdventureWorksDW2012 restored onto SQL 2014 CTP2 and the database set to compatibility mode 120.

the sample query is a star join to dimDate and dimProduct on FactProductInventory and filtering on a single day with the filter applied to dimDate.FullDateAlternateKey

In short, we noticed that when using a traditional row store, a star join can effectively use the seek predicate (or predicate pushdown in a case the dim column is not part of some index) while performing a clustered index seek on the fact table. However, when using clustered columnstore, it first performs a full clustered scan on the columnstore fact table (in row mode) and then performs a date filter in separate steps, which leads to much slower performance in my real-world scenario.

obviously the performance is fine if the date filter is directly applied onto the fact table instead of dimDate, but I don't believe that's a reasonable workaround especially for dimensions other than date
Details
Sign in to post a comment.
Posted by geoff patterson on 7/1/2014 at 8:34 AM
For future readers, I think it is worth clarifying that a computed column only forces a serial plan if and only if the computed column uses a T-SQL UDF in its definition.

I initially read the comment below as indicating that any computed column would force a serial plan in SQL Server 2014, which is not the case and would have been a major regression.

The workaround can be expanded to either (a) create a table without the computed column or (b) update the computed column definition to avoid using a T-SQL UDF (perhaps by inlining the logic).

See this blog post for a more thorough discussion: http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
Posted by Microsoft on 12/3/2013 at 10:23 AM
Hi,

In T-SQL, there are constructs that force sections of the plan to be serial (e.g., inner side of a loop join), and constructs that force the whole plan to be serial. T-SQL UDFs belong in that second category. This is independent of columnstores and batch-mode execution, it just happens to be particularly problematic for batch-mode execution.

In this case, the query refers to a table which simply includes a computed column. Even thought this column is not explicitly referred to, it forces a serial plan.

Work around suggestion is to create a table without the computed column and use that for the query - and you should see a parallel plan.

Regards,
Richard
Posted by Microsoft on 11/20/2013 at 7:00 PM
Hi,

I was able to replicate your original issue. When I quadrupled the size of FactProductInventoryCS, the query columnstore index scan did go into batch mode. The QO does choose the cheapest plan (with an without setting cost threshold for parallelism to 0).

In other words, when the size of the fact table was @700K, QO chose row mode. When the size grew to @3M, it went to batch mode.

I can't take a look at your real-life DW - can you replicate the issue on AW? Do this twice to get your query into batch mode:

insert into dbo.factproductinventorycs select * from dbo.factoproductinventoryCS

Regards,
Richard
Posted by irabufan on 11/14/2013 at 7:33 PM
we attempted the solution below by setting cost threshold for parallelism and tested with both the adventureworks example as well as with my real-world example which was loaded with 69+ million records and filtered for a single date which has roughly 70000 records. it did not change the query plan at all. we also attempted using undocumented trace flag 8649 (http://sqlblog.com/blogs/paul_white/archive/2011/12/22/forcing-a-parallel-query-execution-plan.aspx) to force parallel execution in the query and also did not change the query plan to run parallel either.

I strongly disagree that this is resolved and by design, especially if it leads to query performance up to 6x worse than traditional rowstore.

i'm attaching the query plan for a similar query with 69+ million records. this was executed with cost threshold for parallelism set to 0.

Posted by Microsoft on 11/14/2013 at 5:51 PM
You're not getting a plan with filter directly applied to the fact table scan because the clustered index scan is performed in row mode. We need to choose a parallel plan to make the column mode execution kick in, and the cost of the plan is too small to meet the threshold for parallel plans to be considered. If you lower the threshold for parallelism to 0 using the scripts below and recompile your query, you will see the fact table scanned in column mode and the filter pushed into the scan.

sp_configure 'show advanced options', 1;
GO

reconfigure;
GO

sp_configure 'cost threshold for parallelism', 0;
GO

reconfigure;
GO
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
columnstore2014.xml 11/14/2013 29 KB