4

SQL Server books online say in SET TRANSACTION ISOLATION LEVEL:

"Optimized bulk load operations on heaps block queries that are running under the following isolation levels:

SNAPSHOT

READ UNCOMMITTED

READ COMMITTED using row versioning".

What does optimized bulk load mean here exactly?

6

Optimized bulk load means SQL Server is using a special bulk load code path (fast load) and minimal logging.

These concepts are described in:

From that last link:

Although readers under RCSI are not affected by X locks, there are two bulk load situations that will block RCSI queries (as well as NOLOCK queries) in SQL Server 2008:

  • When populating a heap with TABLOCK using BULK INSERT, INSERT-SELECT, or other bulk load operations. This is because the heap load acquires a BULK OPERATION intent exclusive (IX) lock and the NOLOCK or RCSI readers acquire a BULK OPERATION shared (S) lock. To bulk load a heap and permit concurrent readers even using RCSI or NOLOCK, you must eliminate the TABLOCK hint and thus give up minimal logging.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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