Post reply

slow bcp performance

  • June 28, 2004 at 5:00 am

    #108534

    We use bcp to unload csv format data as part of our weekly batch process. At any given time, there could be several bcp jobs running in parallel (6 or more). This is to ensure that the batch completes in its given window (approx 18 hours).

    The batch is executed via SQL Agent, using VBS to interrogate a table of bcp commands and execute them.

    When we run SQL Server and SQL Agent under the loacl Administrator account, the batch schedule flies and completes in 18 hours.

    When we run SQL Server and SQL Agent under a domain account, the schedule really slows down and is taking > 6 hours extra.

    We have experienced and fixed http://support.microsoft.com/?id=811891

    Are there any other privs that the domain account may need? I'm thinking some sort of memory issue?

    THanks

    Andy

  • June 28, 2004 at 5:14 am

    #512130

    P.S I tried running a single bcp as both Administrator and the domain account - they completed in a similar time.

    So, it appears to be some resource/parallel running issue??

  • June 29, 2004 at 6:20 am

    #512393

    Hello --

    We use bcp's quite often... and they're usually pretty quick... faster than any other type of export.. and use we domain accounts (from a security standpoint) for our SQLAgent.

    Although we're not pumping out as much data as you, there could be a couple of issues at play here (IMHO) with the amount of data your talking about here...  :

    - Is there some method to how many bcp's you're doing at one time? Depending on the server here, I never run more than 1 bcp per CPU... usually never more than CPU-1 bcp's.  This is because it is taking time from SQL Server, and if you are running more bcp's than you have CPUs, bcps could stall/slowdown as they are competing for CPU to finish.

    - Are you bcp-ing out directly from tables or views? If from views, are the views going after indexed columns? -- This could greatly impact performance depending on how dynamic the data is from table to table...

    - Are you bcp-ing out to local disk on the server or to a network drive?  Here we always bcp out to local disk first, then copy/ftp to where it needs to go. This usually eliminates any networking issues from the picture...

    - If you are already bcp-ing out locally, are the disk/drive/array that you're exporting to the same disk/drive/array used for the databases your exporting from? If so, this could be causing hard drive contention, when could be choking things up too...

    - You may also be able tweak the bcp parameters a tad too... can you post what parameters you're usuing, or a sample bcp command you're using?

     

    -- just my 2 cents..   based on my experiences... I actually do more bcp-ing in than out, but the points I mention here equally apply... -- Mike

     

  • June 29, 2004 at 8:11 am

    #512444

    Hi

    Thanks for the reply. I don't think that this is a bcp issue particularly - more why the differences when run as admin against when run as domain account. The domain account is in the local administrators group and as far as I can see, the domain account has the same rights assigned.

    Andy

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

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