I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db. I've been trying to convert it to a columnstore table using the following commands:

insert Logs(Message) values ('Creating SRR table...');
select top 0 * into temp.SRR from dbo.SRR (nolock);
create clustered columnstore index ix_SRR on temp.SRR with (online = off);
create index ix_SRR_JobId on temp.SRR (JobId);
insert Logs(Message) values('Populating SRR table...');
insert into temp.SRR with (tablock) select * from dbo.SRR (nolock);
insert Logs(Message) values ('Switching out SRR table...');
alter schema old transfer dbo.SRR;
alter schema dbo transfer temp.SRR;
insert Logs(Message) values ('SRR table converted.');
if (select count_big(*) from old.SRR (nolock)) = (select count_big(*) from dbo.SRR (nolock)) begin
    drop table old.SRR;
    insert Logs(Message) values('Deleted old SRR table.');
end else begin
    insert Logs(Message) values('Row counts between old.SRR and dbo.SRR do not match; retaining old.SRR.');

This has worked for all of our other large reporting tables, but this one (after a good 30 hours of DTU time) consistently fails with the message:

Msg 40544, Level 17, State 2, Line 195
The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

What can I do to make this work?

  • 1
    Have you considered batching inserts instead of doing one big insert to migrate data ( similar to this method )? – AMtwo Jul 15 at 12:18
  • @AMtwo I haven't tried that, but it might be difficult for me... this table has no primary key and the clustered index is nonunique, so ordered records for batches might not be deterministic. Worth a shot though, a billion records at a time maybe... I was hoping there was some mechanism that was built in that I could leverage though... seems like this really shouldn't detonate my tempdb. – Jeremy Holovacs Jul 15 at 12:38
  • Surely there must be some combination of columns that is unique, in which case you could simulate an identity column with row_number() over (partition by that_combo), then use that to batch (I assume the source table is static). – Aaron Bertrand Jul 15 at 13:27
  • @AaronBertrand In theory each row is unique, but in design there is nothing enforcing such a concept... it's a fairly large technical debt that I've inherited. I'd give 50/50 odds that a row_number() over all 23 columns for 6B rows would finish by the end of the year... :( – Jeremy Holovacs Jul 15 at 13:59
  • 2
    You don't even need a unique key to batch--just something to partition sufficiently small batches. Ex) Using a date column to batch by month, or ranges of CustomerIDs where each batch is a different size, but can be incremented in order. – AMtwo Jul 15 at 15:11

Bulk inserting into a CLUSTERED COLUMNSTORE index by itself shouldn't be blowing up tempdb. Rows are read and put into compressed rowgroups in chunks of 1 million, there is no reason for long term tempdb usage.

I believe the problem is the non-clustered index ix_SRR_JobId on temp.SRR (JobId);

First try moving the index creation to after the bulk insert:

insert Logs(Message) values('Populating SRR table...');
insert into temp.SRR with (tablock) select * from dbo.SRR (nolock);
create index ix_SRR_JobId on temp.SRR (JobId) WITH (SORT_IN_TEMPDB = OFF);

This will substantially reduce tempdb pressure.

If tempdb is still blowing up, then try loading the data in smaller chunks. How you partition the data doesn't really matter, but, it should be based on the leading column(s) of an existing clustered index (unique or not). The chunks should be small enough to load but large enough to avoid excessive "delta rowgroups" (Columnstore Indexes: Data Loading Guidance).

As a starting point, aim for 10-20 chunks of equal size. If the base table is 2 TB, then these would be 100-200 GB. Adjust up or down until you get it to work.

For example, if column1 is a datetime, then try loading by year or quarter or month.

| improve this answer | |
  • hmm I'll try that and let you know, thanks – Jeremy Holovacs Jul 16 at 12:07
  • That worked. I learned something good. Took about 24 hours to complete, but I'm ok with that. – Jeremy Holovacs Jul 22 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.