Improving random insert performance / prereading latch pages(?)

Nov 16, 2016 at 7:39 PM
Edited Nov 17, 2016 at 1:08 AM
On a project I'm working on, for better layout for reads, inserts unfortunately mostly end up being random. With an empty cache, this obviously ends up taking a long time in a large table. From an ETW trace, it seems that the source of the delay responsiveness was on this path:

JetUpdate -> JetUpdateEx -> ErrIsamUpdate -> ErrRECIInsert -> ErrDIRInsert -> ErrBTInsert -> CSR::ErrSwitchPage_ -> CPAGE::ErrGetReadPage -> ErrBFReadLatchPage -> ErrBFILatchPage -> BFISyncRead -> COSFile::ErrIORead -> COSFile::ErrIORead -> COSFile::IOAsync -> esent.dll!GetOverlappedResult_

From the ETW trace, Read IO time ("the time from initiation to completion, including queuing time") was 62 seconds. Summation of "time since last" for the context switches when calling GetOverlappedResult_ was 44.3 seconds ("non-interactive" tasks account for at least 7.3+ seconds, so that accounts for some of the 44.3 vs 62 discrepancy).

Would JetPrereadKeys (specifying a series of keys for the to-be-inserted records) help with this? Or are the "latch pages" separate from the data pages and therefore irrelevant? Is there another option?

Previous posts on this subject by martinc† have suggested shrinking the density or adjusting JET_SPACE_HINTS, the table / index density for this table and its primary index are both 80. However, both are seemingly fixed at table / index creation time, making it challenging to fix prior transgressions.

edit: If the call sequence seems weird, this was from Server 2008 R2.