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
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: )