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

If you take a look at my answer over here, I mention a new(ish) query hint called MAX_GRANT_PERCENT that you can use to further control memory grants at the query level.

Documentation is here.

Assuming queries are in the default pool and you haven't altered it, right now they can ask for 25% of your 768 GB of RAM. That's a lot of memory.

You'll have to play with reducing the percentage until you find a balance between concurrency and not spilling to disk-ency.

Hope this helps!

| improve this answer | |

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