Post reply

Optimize BCP

  • February 17, 2009 at 3:26 pm

    #126483

    Hi Guru,

    I'm doing BCP testing with 131 millions records with two different recovery options. Below is test result:

    Bulk-Logged Simple

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

    Duration 38 minutes 48 minutes

    According to Sunil Agarwal (Program Manager at Storage Engine) minimal logging can also be enabled by Simple recovery mode as well. In another word, in Simple mode, SQL server only requires to log page and extent allocations structure instead of logging individual rows. If this holds true, why Bulk-Logged is always faster then Simple mode?

    FYI... I preallocate data and log files exactly the same between these two recovery modes (Data with 65GB and Log with 10GB).

    Thanks in advance,

    KongDBA,

  • February 17, 2009 at 3:31 pm

    #945556

    simple recovery mode will still write to log file. but in bulklogged recovery model, these type of operations are not logged at all. so saveing time on IO, CPU etc.

    I would recommend you to create one SSIS package for this task, and allocate multiple threads to this process and see how much time it takes...

  • February 17, 2009 at 9:18 pm

    #945644

    4DJ**** (2/17/2009)

    simple recovery mode will still write to log file. but in bulklogged recovery model, these type of operations are not logged at all. so saveing time on IO, CPU etc.

    I would recommend you to create one SSIS package for this task, and allocate multiple threads to this process and see how much time it takes...

    Absolutely not true for operations that meet the qualifications for "minimal logging". Both the Simple and Bulklogged recovery models allow for "minimal logging" of bulk operations provided that they qualify as such. And, yes, both write to the log even for bulk logged operations even if they meet the requirements for "minimal logging" although it is greatly minimized compared to the Full recovery mode.

    Something else is going on here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 19, 2009 at 12:49 pm

    #947056

    A way to see if the logging is the issue might be to check the size of the log after each test - making sure to truncate the log prior to each test of course.

    dbcc sqlperf( logspace) will show you %log used. If they are both minimally logging the results should be very similar.

    jg

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

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