CODEWITHSUNDEEP
sql-serverrecoverymodel
variable
variable

Reputation: 9736

How does WITH TABLOCK improve performance in full recovery model?

I'm doing some performance testing of a larger insert query (10 million rows).

In simple recovery model, the insert statement WITH (TABLOCK) takes 2 minutes to run.

This is because it will minimally log the transaction.

In full recovery model, if I don't use tablock then query takes 4 minutes. However if I use TABLOCK it takes 2 minutes. How is full recovery model able to achieve this speedup when it doesn't support minimally logged transaction?

The table doesn't have an index. If I do add an index then the load takes 2 minutes and 10 seconds. Still faster than the one without TABLOCK.

What is the cause of speedup in full recovery when performing inserts with tablock?

Upvotes: 0

Views: 103

Answers (0)

Related Questions