Fun with (columnstore) compression on a very large table – part 3

[ Part 1 | Part 2 | Part 3 ]

In part 1 of this series, I tried out a few ways to compress a 1TB table. While I got decent results in my first attempt, I wanted to see if I could improve the performance in part 2. There I outlined a few of the things I thought might be performance issues, and laid out how I would better partition the destination table for optimal columnstore compression. I've already:

• partitioned the table into 8 partitions (one per core);
• put each partition's data file on its own filegroup; and,
• set archive compression on all but the "active" partition.

I still need to make it so that each scheduler writes exclusively to its own partition.

First, I need to make changes to the batch table I created. I need a column to store the number of rows added per batch (kind of a self-auditing sanity check), and start/end times to measure progress.

ALTER TABLE dbo.BatchQueue ADD 
  RowsAdded int,
  StartTime datetime2, 
  EndTime   datetime2;

Next, I need to create a table to provide affinity – we don't ever want more than one process running on any scheduler, even if it means losing some time to retry logic. So we need a table that will keep track of any session on a specific scheduler and prevent stacking:

CREATE TABLE dbo.OpAffinity
(
  SchedulerID int NOT NULL,
  SessionID   int NULL,
  CONSTRAINT  PK_OpAffinity PRIMARY KEY CLUSTERED (SchedulerID)
);

The idea is I would have eight instances of an application (SQLQueryStress) that would each run on a dedicated scheduler, handling only the data destined for a specific partition / filegroup / data file, ~100 million rows at a time (click to enlarge):

App 1 gets scheduler 0 and writes to partition 1 on filegroup 1, and so on…

Next we need a stored procedure that will enable each instance of the application to reserve time on a single scheduler. As I mentioned in a previous post, this is not my original idea (and I would never have found it in that guide if not for Joe Obbish). Here is the procedure I created in Utility:

CREATE PROCEDURE dbo.DoMyBatch
  @PartitionID   int,    -- pass in 1 through 8
  @BatchID       int     -- pass in 1 through 4
AS
BEGIN
  DECLARE @BatchSize       bigint, 
          @MinID           bigint, 
          @MaxID           bigint, 
          @rc              bigint,
          @ThisSchedulerID int = 
          (
            SELECT scheduler_id 
	      FROM sys.dm_exec_requests 
    	      WHERE session_id = @@SPID
          );

  -- try to get the requested scheduler, 0-based
  IF @ThisSchedulerID <> @PartitionID - 1 
  BEGIN
    -- surface the scheduler we got to the application, but force a delay
    RAISERROR('Got wrong scheduler %d.', 11, 1, @ThisSchedulerID);
    WAITFOR DELAY '00:00:05';
    RETURN -3;
  END
  ELSE
  BEGIN
    -- we are on our scheduler, now serializibly make sure we're exclusive
    INSERT Utility.dbo.OpAffinity(SchedulerID, SessionID)
      SELECT @ThisSchedulerID, @@SPID
        WHERE NOT EXISTS 
        (
          SELECT 1 FROM Utility.dbo.OpAffinity WITH (TABLOCKX) 
            WHERE SchedulerID = @ThisSchedulerID
        );

    -- if someone is already using this scheduler, raise roar:
    IF @@ROWCOUNT <> 1
    BEGIN
      RAISERROR('Wrong scheduler %d, try again.',11,1,@ThisSchedulerID) WITH NOWAIT;
      RETURN @ThisSchedulerID;
    END

    -- checkpoint twice to clear log
    EXEC OCopy.sys.sp_executesql N'CHECKPOINT; CHECKPOINT;';

    -- get our range of rows for the current batch
    SELECT @MinID = MinID, @MaxID = MaxID
      FROM Utility.dbo.BatchQueue 
      WHERE PartitionID = @PartitionID
        AND BatchID = @BatchID
        AND StartTime IS NULL;

    -- if we couldn't get a row here, must already be done:
    IF @@ROWCOUNT <> 1
    BEGIN
      RAISERROR('Already done.', 11, 1) WITH NOWAIT;
      RETURN -1;
    END

    -- update the BatchQueue table to indicate we've started:
    UPDATE msdb.dbo.BatchQueue 
      SET StartTime = sysdatetime(), EndTime = NULL
      WHERE PartitionID = @PartitionID
        AND BatchID = @BatchID;

    -- do the work - copy from Original to Partitioned
    INSERT OCopy.dbo.tblPartitionedCCI 
      SELECT * FROM OCopy.dbo.tblOriginal AS o
        WHERE o.CostID >= @MinID AND o.CostID <= @MaxID
        OPTION (MAXDOP 1); -- don't want parallelism here!

    /*
        You might think, don't I want a TABLOCK hint on the insert, 
        to benefit from minimal logging? I thought so too, but while 
        this leads to a BULK UPDATE lock on rowstore tables, it is a 
        TABLOCKX with columnstore. This isn't going to work well if 
        we want to have multiple processes inserting into separate 
        partitions simultaneously. We need a PARTITIONLOCK hint!
    */

    SET @rc = @@ROWCOUNT;

    -- update BatchQueue that we've finished and how many rows:
    UPDATE Utility.dbo.BatchQueue 
      SET EndTime = sysdatetime(), RowsAdded = @rc
      WHERE PartitionID = @PartitionID
        AND BatchID = @BatchID;

    -- remove our lock to this scheduler:
    DELETE Utility.dbo.OpAffinity 
      WHERE SchedulerID = @ThisSchedulerID 
        AND SessionID = @@SPID;
  END
END

Simple, right? Fire up 8 instances of SQLQueryStress, and put this batch into each:

EXEC dbo.DoMyBatch @PartitionID = /* PartitionID - 1 through 8 */, @BatchID = 1;
EXEC dbo.DoMyBatch @PartitionID = /* PartitionID - 1 through 8 */, @BatchID = 2;
EXEC dbo.DoMyBatch @PartitionID = /* PartitionID - 1 through 8 */, @BatchID = 3;
EXEC dbo.DoMyBatch @PartitionID = /* PartitionID - 1 through 8 */, @BatchID = 4;

Poor man's parallelism

Except it's not that simple, since scheduler assignment is kind of like a box of chocolates. It took many tries to get each instance of the app on the expected scheduler; I would inspect the exceptions on any given instance of the app, and change the PartitionID to match. This is why I used more than one iteration (but I still only wanted one thread per instance). As an example, this instance of the app was expecting to be on scheduler 3, but it got scheduler 4:

If at first you don't succeed...

I changed the 3s in the query window to 4s, and tried again. If I was quick, the scheduler assignment was "sticky" enough that it would pick it right up and start chugging away. But I wasn't always quick enough, so it was kind of like whack-a-mole to get going. I probably could have devised a better retry/loop routine to make the work less manual here, and shortened up the delay so I knew immediately whether it worked or not, but this was good enough for my needs. It also made for an unintentional staggering of start times for each process, another piece of advice from Mr. Obbish.

Monitoring

While the affinitized copy is running, I can get a hint about current status with the following two queries: