Skip to main content
added 92 characters in body
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum thresholdminimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not usedonly when bulk loading optimizations are not used.

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not usedonly when bulk loading optimizations are not used.

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answerGeoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

Why is it that the second query does not qualify for minimal logging?

Minimal logging is available for the second query, but the engine chooses not to use it at runtime.

There is a minimum threshold for INSERT...SELECT below which it chooses not to use the bulk load optimizations. There is a cost involved in setting up a bulk rowset operation, and bulk-inserting only a few rows would not result in efficient space utilization.

What can be done to improve the situation?

Use one of the many other methods (e.g. SELECT INTO) that does not have this threshold. Alternatively, you might be able to rewrite the source query in some way to boost the estimated number of rows/pages over the threshold for INSERT...SELECT.

See also Geoff's self-answer for more useful information.


Possibly interesting trivia: SET STATISTICS IO reports logical reads for the target table only when bulk loading optimizations are not used.

Removed EDIT see http://meta.stackexchange.com/a/127655
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Loading
added 125 characters in body
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Loading
added 215 characters in body
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Loading
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Loading