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_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 (http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/04/what-is-allocation-bottleneck.aspx).
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.
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 bstat = 0x10b blog = 0x7a7a7a7a bnext = 0x0000000000000000 PAGE HEADER: Page @0x0000004064D20000 m_pageId = (1:476) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0 m_objId (AllocUnitId.idObj) = 7 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 458752 Metadata: PartitionId = 458752 Metadata: IndexId = 1 Metadata: ObjectId = 7 m_prevPage = (1:475) m_nextPage = (0:0) pminlen = 69 m_slotCnt = 34 m_freeCnt = 5546 m_freeData = 3089 m_reservedCnt = 0 m_lsn = (18391:146685:11) m_xactReserved = 0 m_xdesId = (0:588003648) m_ghostRecCnt = 0 m_tornBits = 208117691 DB Frag ID = 1 Allocation Status GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
Edit 2: a bit more detail on the actual insert process. Basically:
- There are about 200 tables I'm ultimately trying to insert into (the tables are called things like dbo.LastTradePrice, dbo.Volume, etc). These tables all have clustered indexes of on a
(SecurityID, Date, FromSourceID)triple. The tables can have 100s of millions of rows.
- I have 200 simultaneous connections to the SQL server, each of which bulk insert into a different staging table (stage.LastTradePrice, stage.Volume etc). These tables all have clustered indexes on an autoincrementing primary key
ID. Each staging table can hold up to 300,000 rows.
- Then I have up to 32 connections which attempt to concurrently merge each staging table into the corresponding normal table. For each table, this merge process involves sorting the staging table (which I guess will spill to tempdb) and storing a subset of the sorted result into a table variable (another tempdb hit?). The data in the table variable is then used to update the main tables, a process which involves both INSERT and UPDATE operations.