Why partitioning an indexed view with columnstore makes it slower?

Question

I have a table containing about 100M entries, with 3 different tenants, splitting the rows evenly.
I have created an indexed view based on this table, and a nonclustered columnstore. Both indexes are partitioned, based on the Tenant number. Each partition has about 30M rows.

Querying the view with this partitioned columnstore takes 2.6s The same query without partition takes 2.4s (the execution plan is the same in both cases. In the query on the partitioned index, only one partition is queried).

Why do partitions make the query slower, instead of faster ?

---

Here are the details:

--I create a view
CREATE VIEW dbo.myView
WITH SCHEMABINDING
AS
SELECT TenantNumber, Date, ProductId, 
COUNT_BIG(*) rowCount,
SUM(TotalSales) TotalSales
FROM [dbo].[myTable]
Group by TenantNumber, Date, ProductId;

This view has about 30M rows per tenant.

--I create my partition scheme
CREATE PARTITION FUNCTION myRangeTenantNumber (int)  
    AS RANGE FOR VALUES (1,2,3);  


CREATE PARTITION SCHEME mySchemeTenantNumber  
    AS PARTITION myRangeTenantNumber
    ALL TO ('PRIMARY');  

--I index my view
CREATE UNIQUE CLUSTERED INDEX idx_TenantDateProductId on 
myView(TenantNumber, Date, ProductId
ON mySchemeTenantNumber(TenantNumber);

Queries confirm that each partition has about 30M rows.

--I add a columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_columnstore
ON  dbo.myView
(TenantNumber, SkuNumber,  Date, TotalSales)
ON mySchemeTenantNumber(TenantNumber);

For this index as well, I can see that each partition has about 30M rows

--I query my view 
DBCC DROPCLEANBUFFERS;
SET STATISTICS IO ON;
SET STATISTICS time ON;
SELECT Date, SUM(TotalSales) TotalSales
  FROM myView WITH(NOEXPAND)
  WHERE TenantNumber = 1 AND Date >= '2018-01-01' AND Date <'2021-01-01'
  GROUP BY Date
OPTION(RECOMPILE);
SET STATISTICS time OFF;
SET STATISTICS IO OFF;

The execution plan confirms that only one partition is queried (out of the 3 created), but still the execution is about 10% slower than querying the same view without partitions.
More, this is about 30% slower than if I had created manually an indexed view per tenant.

EDIT : Paste The Plan could not parse the exported plans, i stored them in these pastebin : this is what i get with the non partitioned index link and this is what i get with the partitioned index link.

The diagrams look the same :

sql plan

But in the xml I can see 3 differences when relying on a partitioned index:

in the WaitStats section, a WaitType is added

<Wait WaitType="IO_QUEUE_LIMIT" WaitTimeMs="47" WaitCount="4"

in RelOp, a RunTimePartitionSummary specify which partition to query, as expected :

  <RunTimePartitionSummary>
    <PartitionsAccessed PartitionCount="1">
      <PartitionRange Start="1" End="1" />
    </PartitionsAccessed>
  </RunTimePartitionSummary>

The IndexScan is Ordered (it is not when i query the view with the non partitioned index)

<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="true" Storage="ColumnStore">

and it has a SeekPredicate section that is also absent in the query on the view with the non partitioned index :

<SeekPredicates>
  <SeekPredicateNew>
    <SeekKeys>
      <Prefix ScanType="EQ">
        <RangeColumns>
          <ColumnReference Column="PtnId1000" />
        </RangeColumns>
        <RangeExpressions>
          <ScalarOperator ScalarString="(1)">
            <Const ConstValue="(1)" />
          </ScalarOperator>
        </RangeExpressions>
      </Prefix>
    </SeekKeys>
  </SeekPredicateNew>
</SeekPredicates>

Why does partitioning the index cause an additional SeekPredicate and an ordered index scan ? could it be the cause of the slower execution ?

Answer 1

The CPU time is very similar for the two plans; slightly lower for the partitioned case, as expected. There's more IO waits in the partitioned plan, which is likely an accident of the state of the page cache. Run both in a completely cold cache, or a completely warm cache to eliminate this effect.

Non-partitioned

       <WaitStats>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="554" WaitCount="11" />
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="6" WaitCount="8197" />
        </WaitStats>
        <QueryTimeStats CpuTime="350" ElapsedTime="856" />

Partitioned

        <WaitStats>
          <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="755" WaitCount="34" />
          <Wait WaitType="IO_QUEUE_LIMIT" WaitTimeMs="47" WaitCount="4" />
          <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="4" WaitCount="7909" />
        </WaitStats>
        <QueryTimeStats CpuTime="327" ElapsedTime="1099" />