The newly added page will not necessarily be full (though obviously that is the ideal case) because SQL Server has to be careful not to add rows to the new page that logically belong on an existing index page. Once as many rows as possible are written to the new page, it is directly linked into the existing target index structure. Otherwise, rows from the ordered insert stream are added to a brand new page using an optimized, and minimally-logged code path.

Rows moved between pages by a page split.

All rows added to the first index page, if the index was empty at the start of the operation.

Rows are inserted one at a time by the Index Insert operator and fully logged in the following cases: Mixed loggingĪ single INSERT.SELECT statement using FastLoadContext may fully log some rows while minimally logging others. This event does not fire for RowsetBulk loads. The Debug channel Extended Event fastloadcontext_enabled can be used to monitor FastLoadContext usage per index partition (rowset). As a consequence, a suitable insert with TABLOCK may still qualify for minimal logging via FastLoadContext if it fails the detailed RowsetBulk tests.įastLoadContext can be disabled on SQL Server 2016 using documented trace flag 692.

A TABLOCK hint is not required with FastLoadContext, but it is not required to be absent either. You may have noticed an overlap between RowsetBulk and FastLoadContext for empty clustered tables with no secondary indexes. The FastLoadContext also requires DMLRequestSort=true on the corresponding plan operator in all cases. Empty and non-empty nonclustered b-tree indexes maintained by a dedicated Index Insert plan operator.Empty and non-empty clustered b-tree indexes.The FastLoadContext code path adds support for minimally-logged and concurrent bulk load on: DMLRequestSort=true on the Clustered Index Insert operator.Fast Load ContextĪs a quick reminder, the RowsetBulk facility (covered in parts 1 and 2) enables minimally-logged bulk load for: The minimal logging conditions for unindexed heap tables were covered in part one.įor more background, see the Data Performance Loading Guide and the Tiger Team notes on the behaviour changes for SQL Server 2016. Without FastLoadContext, the only index inserts that can be minimally logged are those into an empty clustered index without secondary indexes, as covered in part two of this series. From SQL Server 2016 onward, FastLoadContext is enabled by default the trace flag is not required. It can be activated from SQL Server 2008 to 2014 inclusive using documented trace flag 610.

The internal facility that enables these cases is called FastLoadContext. This post provides new information about the preconditions for minimally logged bulk load when using INSERT.SELECT into indexed tables.

Will there be flags in cine tracer