Post reply

SSIS performance improvements

  • October 21, 2011 at 10:23 am

    #390672

    I did a standard, full install of SQL Server 2008 R2 Enterprise on a new server, and am running an SSIS project that reads a large text field from the DB as input, parses one field, then writes to the same DB in a different table.

    It is processing approx 75k records per hour.

    I have AWE enabled, 256 GB of RAM, Four 8 Core processors, and fast 6 gbps HD's running RAID1.

    Any suggestions?

    Thanks

  • October 21, 2011 at 10:37 am

    #1398118

    how many discs in storage?

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • October 21, 2011 at 11:03 am

    #1398131

    Two for the o/s

    Two for the data

    I am waiting for more carriers to add:

    Two for the TempDB

    Two for the Log Files

    I thought the performance would be better, based on the HW!

  • October 21, 2011 at 11:41 am

    #1398154

    My initial feeling is that you have a bottleneck on the IO side. your processors and RAM seem fine but the config for your disks is probably the issue. Is this hardware RAID1?

    What is your avg disk queue length on the database drives? Keep in mind that you are paying for the read and the write onto the same drives.

    CEWII

  • October 21, 2011 at 12:06 pm

    #1398167

    yes RAID1, i have more drives on order, and will move the data to a RAID10.

    I think the regex is what is killing me here!

  • October 21, 2011 at 2:20 pm

    #1398234

    I changed the Regex and now it processes 100k records in 3 minutes 🙂

  • October 21, 2011 at 2:51 pm

    #1398248

    isuckatsql (10/21/2011)

    yes RAID1, i have more drives on order, and will move the data to a RAID10.

    Really my question was hardware vs. software RAID.

    CEWII

  • October 21, 2011 at 2:58 pm

    #1398249

    isuckatsql (10/21/2011)

    I changed the Regex and now it processes 100k records in 3 minutes 🙂

    Even that seems slow, I moved 11M records from one table to another on my laptop (no raid, single disk) in under 2m using SSIS. What kind of regex are you doing? Is it in the package or in SQL?

    CEWII

  • October 22, 2011 at 11:07 am

    #1398348

    isuckatsql (10/21/2011)

    I did a standard, full install of SQL Server 2008 R2 Enterprise on a new server, and am running an SSIS project that [font="Arial Black"]reads a large text field from the DB as input, parses one field, then writes to the same DB in a different table[/font].

    It is processing approx 75k records per hour.

    I have AWE enabled, 256 GB of RAM, Four 8 Core processors, and fast 6 gbps HD's running RAID1.

    Any suggestions?

    Thanks

    Chances are, it has nothing to do with the hardware. Chances are, it's the method of parsing you're using which, according to your description, is using RBAR methods.

    My recommendation is to post a sample of the data you're parsing in a readily consumable format as a text attachment and the code for however you're trying to parse the "field". See the first link in my signature line for what I mean by "readily consumbable".

    In the meantime, SQL Server sucks for doing such things as "parsing" or "splitting". Please see the following article for how to make it suck less. 😛

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    --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)

Viewing 9 posts - 1 through 8 (of 8 total)

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