I'm attempting to migrate several very large tables in my data warehouse to new partition scheme (the older partition scheme was based on a RANGE LEFT function, that wasn't regularly split, and the most recent data is top-heavy in the last filegroup). The tables are sufficiently large (5 billion rows, 400 GB data space), that I'm leery to try a clustered index rebuild for fear of insufficient logging space.
I'm attempting to load the new table by running several INSERT...(WITH TABLOCK) SELECT...WHERE <check constraint predicate> (OPTION MAXDOP 4) statements into identical staging tables, one per filegroup in the scheme which I'll later switch into the replacement table. I coordinate execution of this faux bulk load by individual SSA jobs to take advantage of read-ahead/merry-go-round.
All works pretty well on the relatively small tables will all jobs running simultaneously. However, on the first large table, most of the SSA jobs have RESOURCE_SEMAPHORE waits while the job with the first partition is chugging along.
If my server has 768 GB RAM, 24 cores (hyperthreaded) and it's working on a 400 GB table, should I adjust my MAXDOP downward to free up more grants or enable resource governor to reduce the maximum grant to get more SSA jobs to kick in and work in parallel?
I initially tried the BULK_LOGGED recovery model, but went to SIMPLE. Transaction size is still huge; I generated over 1 TB of log copying 15 GB (must be due to the page splits if the data set wasn't sorted, I'm using page compression, and I was running other than MAXDOP 1).
I'm working on a FTDW rig (local SSD array running about 5 GB/s throughput, PureStorage array peaking at 11 GB/s) so hopefully disk latency won't be an issue. I'm writing the SSA jobs with SMO.
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64)
Oct 28 2016 18:17:30
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit)
on Windows Server 2012 R2 Standard 6.3 (Build 9600: )
