Post reply

Issue Merge and Trace Flag 610

  • September 20, 2011 at 1:45 pm

    #245236

    I have ETL process that utilizes fast load merge statements by turning on trace flag 610. I do this on a number of tables with no issues, everything works fine, the insert portion of the merge takes place in a minimally logged fashion dramatically reducing the time to insert records. I have two tables in particular that insert batches of 60 to 90 million records. In these two tables the merge operation is not minimally logged. These are the two largest table batches I insert on a single load. If I switch and do and insert with the 610 Trace Flag enabled the operation is minimally logged and the operation once again is dramatically faster. When I insert into these tables with a merge statement there are no records that would trigger updates in the batch. Has anyone else experienced this issue or something similar?

    Thanks ahead of time,

    P.S.

    I forgot to mention if I load smaller batch sizes of 1 million rows or less the insert portion of the merge statement takes place in a minimally logged fashion.

    Steve
    http://stevefibich.net
    http://utilitydb.codeplex.com/

  • September 21, 2011 at 7:30 am

    #1384481

    There are a lot of prerequisites to minimally logged inserts (even with trace flag 610 turned on).

    There's a good explanation here:

    http://msdn.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx

    Are your inserts in clustered index order, or can they be anywhere in the index?

  • September 21, 2011 at 10:04 am

    #1384637

    The table being inserted into is empty, so no pages have been allocated to the table As for the data being order in the order of the clustered index, no, I have not place a specific order by clause on either the select statement for the insert or the sub select source statement for the merge.

    I have seen the statement before in the document you referred to stating

    (1) If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. If using BULK INSERT the order hint must be used.

    I have observed that it is not necessary to add an order by clause for an INSERT…SELECT to be minimally logged. I do not know if this statement should also apply to the source query for a merge statement, I’ll give it a try.

    Thanks,

    Steve
    http://stevefibich.net
    http://utilitydb.codeplex.com/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply