Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Minimal Logging Enhancement in SQL Server 2008 Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 4:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 48, Visits: 311
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.
Post #1435385
Posted Tuesday, March 26, 2013 5:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
What recovery model is the database in?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1435388
Posted Tuesday, March 26, 2013 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 48, Visits: 311
Hi Gail,

As mentioned, the database is in SIMPLE recovery model.

Best,
Yusuf
Post #1435448
Posted Tuesday, March 26, 2013 8:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
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
Post #1435540
Posted Tuesday, March 26, 2013 8:54 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 5,845, Visits: 12,576
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.

---------------------------------------------------------------------

Post #1435550
Posted Wednesday, March 27, 2013 12:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 48, Visits: 311
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.
Post #1436097
Posted Wednesday, March 27, 2013 12:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 48, Visits: 311
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
Post #1436098
Posted Wednesday, March 27, 2013 12:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 48, Visits: 311
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.
Post #1436102
Posted Wednesday, March 27, 2013 12:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:06 AM
Points: 48, Visits: 311
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?
Post #1436116
Posted Wednesday, March 27, 2013 1:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, February 21, 2014 4:34 PM
Points: 369, Visits: 1,197
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
Post #1436145
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse