Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Multiple parallelism (exchange) operators in a row with nonclustered columnstore and DISALLOW_BATCH_MODE

In SQL Server 2019 CTP 2.0 the following repro produces a plan with three Repartition Streams operators in a row:

DROP TABLE IF EXISTS #P;

CREATE TABLE #P
(
Id integer IDENTITY NOT NULL PRIMARY KEY,
UserId integer NOT NULL
);

INSERT #P
(UserId)
SELECT TOP (5 * 1000 * 1000)
ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master.dbo.spt_values AS V1
CROSS JOIN master.dbo.spt_values AS V2;

CREATE NONCLUSTERED COLUMNSTORE INDEX n ON #P (Id, UserId);

SELECT TOP (10)
P.UserId,
COUNT_BIG(*)
FROM #P AS P
GROUP BY P.UserId
OPTION (USE HINT ('DISALLOW_BATCH_MODE'));

Plan attached.

4 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Paul White shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base