The Trivial Plan Problem With Columnstore

Niko Neugebauer shows that trivial columnstore plans can lead to poor performance, but SQL Server 2017 has a fix:

Do you remember one of the major problems in SQL Server 2014 using Columnstore Indexes ? It was the lack of the support for the Batch Execution Mode with just a single core. We would get wonderful, fast execution plans with MAXDOP >= 2, which will go terribly slow if there would not be enough memory to run the query with 2 or more cores, or if the internal query cost would be below the parallel execution threshold (cost threshold for parallelism)
OR if the execution plan would be dimmed as TRIVIAL by the Query Optimiser, thus getting a single core execution and running really slow.
Once we upgraded to SQL Server 2016, the problem of inability of the single core Batch Mode execution would fade away, but still, sometimes some queries would run terribly slow for some reason …
One of the reasons behind this are the trivial execution plans, which are running Columnstore Index Scan in the Row Execution Mode – also known as a VerySlowExecutionMode for the big amounts of data.

Read on to see the change in 2017, as well as a workaround for 2016.

Related Posts

Clustered Columnstore and Azure SQL DB

Arun Sirpal takes us through online clustered columnstore index creation in Azure SQL Database: What tier do you need to create one of these things? Let’s see. CREATE CLUSTERED  COLUMNSTORE INDEX cciSales ON [SalesLT].[ProductModelProductDescription] WITH ( ONLINE = ON ) But I get this message, Msg 40536, Level 16, State 32, Line 1‘COLUMNSTORE’ is not […]

Read More

Creating a Columnstore Index

Monica Rathbun shows a scenario where creating a clustered columnstore index can make data retrieval much faster: Using AdventureworksDW2016CTP3 we will work with the FactResellerSalesXL table which has 11.6 million rows in it. The simple query we will use as a demo just selects the ProductKey and returns some aggregations grouping them by the different […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31