SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Minimal Logging Enhancement in SQL Server 2008


Minimal Logging Enhancement in SQL Server 2008

Author
Message
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 413
Hi folks,

SQL Server 2008 has enhanced INSERT..SELECT statement to allow minimal logging in certain cases, documented here:
http://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx (See section: Best Practices for Bulk Loading Data)

We planned to use this feature in a situation where we wanted to insert millions of rows in an empty heap (or an empty clustered index) and made sure that all pre-requisites are fulfilled.
a) The target table is a heap or an empty B-Tree,
b) TABLOCK is being used on the target table,
c) The database is in SIMPLE recovery model,
d) The target table is not used in replication.


The INSERT is done from a SELECT from a temp table (#temp) having around 300,000 rows (300,000 distinct Ids) joined with another table that has ~500 rows for each of the distinct #temp.Id. Thus, 500 x 300,000 = 150 million rows (around 10 GB)

We are on Microsoft SQL Server 2008 (SP1) - 10.0.2740.0 (X64).
But it seems the minimal logging is not working for us, as I see the transaction log growing to almost 10GB, during the insert if no other processes are running.
We also face transaction log full issue, when other processes are running along with this insert. (Transaction Log size = 20 GB)

There were no backups being run at the time of testing, which may have caused full logging.

Is the minimal logging using INSERT..SELECT available in all versions of SQL Server 2008 or did it get included in one of the Cumulative Updates?

I would really like to make use of this new feature in SQL Server 2008.
Any ideas on how to achieve it, would be appreciated.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215716 Visits: 46270
What recovery model is the database in?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 413
Hi Gail,

As mentioned, the database is in SIMPLE recovery model.

Best,
Yusuf
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1858 Visits: 1266
Heap should have no indexes. Try with TABLOCKX hint.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23742 Visits: 13698
are you doing the insert in batches? after the first batch the table will no longer be empty so you will no longer get minimal logging.

---------------------------------------------------------------------
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 413
Hi Vedran,
I couldn't quite connect your two sentences: "Heaps have no indexes" and "Try using TABLOCKX".
Nevertheless, I did try TABLOCKX to no avail.
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 413
george sibbald (3/26/2013)
are you doing the insert in batches? after the first batch the table will no longer be empty so you will no longer get minimal logging.


Not doing insert in batches. It is a single big insert..select
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 413
Vedran Kesegic (3/26/2013)
Heap should have no indexes. Try with TABLOCKX hint.

Hi Vedran,
I couldn't quite connect your two sentences:
"Heaps should have no indexes" (heaps can have non clustered indexes, can't they?),
and
"Try with TABLOCKX hint".
Nevertheless, I did try TABLOCKX to no avail.
Yusuf Bhiwandiwala
Yusuf Bhiwandiwala
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 413
I figured out the reason why minimal logging was not being done in my case.

All the pre-requisites mentioned in BOL to achieve minimal logging were met.
Though, a few hours later I noticed that my target table has an IDENTITY column, and I thought of removing the identity property or the identity column itself and see if it makes any difference.

And lo, minimal logging was achieved.

Not very sure of the internals behind it, but if I remove the identity column from the target table, the 150 million records insert is minimally logged.

Do you think BOL should mention this exception or add it in the list of pre-requisites for minimal logging during normal INSERT..SELECT operation?
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1858 Visits: 1266
Yusuf Ali Bhiwandiwala (3/27/2013)
Vedran Kesegic (3/26/2013)
Heap should have no indexes. Try with TABLOCKX hint.

Hi Vedran,
I couldn't quite connect your two sentences:
"Heaps should have no indexes" (heaps can have non clustered indexes, can't they?)

My sentence was: "Heap should have no indexes". Singular, not plural, your particular heap you want to achieve minimal logging.
If you have NC indexes on the heap, they will be fully logged on nonempty tables (after the first batch if you bulk import in batches, your table is no longer considered empty) even if table itself is minimally logged.
Maybe identity problem would disappear if you choose that value is imported instead of auto-generated.
Certainly, that should be in the BOL. You can add a comment there for others to know.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search