Any way to reduce sysallocunits contention under a heavy concurrent INSERT workload?

I have a database which is being inserted into by a large number of independent sessions. Each session writes to a different table, so you would naively expect that they would all proceed in parallel. However, by inspecting sys.dm_os_wait_stats and sys.dm_os_latch_stats, I found that I was actually experiencing a high number of PAGELATCH_* waits on pages that DBCC PAGE reported as type 11, i.e. PFS pages.

So basically the whole process is being slowed down because each thread is contending with the others to allocate new pages.

The standard advice in this situation is to:

• Increase the number of data files to be at least as many as there are cores on your machine (Link).

• Do DBCC TRACEON(1118, -1) to force the server to allocate whole extents rather than single pages.

I did both of these things and it did indeed reduce contention on PFS pages, but I'm now seeing a fair bit of PAGELATCH_EX contention on pages that DBCC PAGE tells me are data pages belonging to object ID 7, i.e. sysallocunits.

There isn't much information available online about the purpose of the sysallocunits system table, and I couldn't find anything about contention on it. It seems like I've just managed to replace my allocation bottleneck with a bottleneck when mutating this table!

I suppose I might be able to reduce contention by putting each table in its own database (which would then not share a sysallocunits table), but this would be a fairly major architectural change that I'd prefer to avoid.

Is there any way that I can reduce sysallocunits contention without creating extra databases?

Edit: as requested, this is the output of DBCC PAGE on one of the pages showing contention:

```
PAGE: (1:476)

BUFFER:

BUF @0x0000006143763A80

bpage = 0x0000004064D20000 bhash = 0x0000006301FCBAC0 bpageno = (1:476)
bdbid = 15 breferences = 2047 bcputicks = 48351
bsampleCount = 90 bUse1 = 32392

With the workload description you have provided, you are putting a large amount of pressure on the allocations unit, especially since you are trying to maintain a sorted load (with the IDENTITY column and cluster). Keeping track of the allocations that happen will put a lot of pressure on the metadata - unless you are in bulk mode of course.

Here are the potential solutions:

• Load into a heap. Rebuild the index (if you need it) after load is done. This also allows concurrent BULK INSERT on a single table. If you are on 2012, prefer SEQUENCER with cache over IDENTITY (IDENTITY columns have bottlenecks)

• Use Trace flag 610 to enable minimally logged load on cluster indexes (if you MUST use clusters)

• Put each table on a separate file group (this spreads out the allocations). Do this only as a last resort

• If you plan to merge the staging tables into the final tables, why not merge directly without going through the intermediate step of table variables?

• If you use a clustered index, could you have that be sorted by the same keys that you need the merge to happen on (removing the later sort in tempdb)

• When loading into a sorted table (as above) use the ORDER hint if the input is already sorted. This reduces contention on the allocation maps (and also allows minimally logged loads)

StackExchange Database Administrators Q#57155, answer score: 5