none
SSIS performance and memory usage

    Question

  • Hi -

     

    I am facing 2 problems :

    PROBLEM 1 :

    We have a few packages that run pretty fast on a desktop server with 2 Gig RAM, Dual processor (approx 4-5 hours). But the same packages run very very slow on the another server containing 8 CPU and 12 Gig RAM (ran for 24 hours without completing). 

     

    PROBLEM 2  :

    The CPU% ranges from 40-80% and the PF usage is stagnant at 2GB on desktop server for the same package. But in the 8CPU server, the CPU % ranges from 0-10% but the PF Usage raises from 750 MB to 8 GB.

     

    This has become critical to our application.

     

    TIA,

     

    Shabs

    Monday, September 24, 2007 2:55 PM

All replies

  • few questions in order to understand the problem better ...

    a) what is the package doing?

    b) what are the control flow tasks and data flow components used?

    c) is source, SSIS packagae execution and destination located on the same machine?

    d) is the destination table empty with no indexes at the start of the execution?

    e) what is the MaxInsertCommitSize?

     

     

    Monday, September 24, 2007 8:43 PM
  •  shabs wrote:

    Hi -

     

    I am facing 2 problems :

    PROBLEM 1 :

    We have a few packages that run pretty fast on a desktop server with 2 Gig RAM, Dual processor (approx 4-5 hours). But the same packages run very very slow on the another server containing 8 CPU and 12 Gig RAM (ran for 24 hours without completing). 

     

    PROBLEM 2  :

    The CPU% ranges from 40-80% and the PF usage is stagnant at 2GB on desktop server for the same package. But in the 8CPU server, the CPU % ranges from 0-10% but the PF Usage raises from 750 MB to 8 GB.

     

    This has become critical to our application.

     

    TIA,

     

    Shabs

     

    Are you referencing the same source data on both servers?

    Monday, September 24, 2007 10:05 PM
  •  

    1. The package is taking data from a db with a column as XML, parsing it and putting the values in columns in a table in another db. Both the db are on the same server.
    2. Control Flow has - Exec SQL and DFTs, DFT has - OLEDB source, OLEDB Target with Normal Load with error handling. The OLEDB source parses the XML.
    3. At the start of the load, the table is empty but has indexes.
    4. The source data is replicated on both the servers. Hence the number of rows in both the servers are the same. The ETLs are the same. Only the machine config is different.

     

    Tuesday, September 25, 2007 5:06 AM
  • a) if it is an option, drop indexes, load data and create indexes.

    b) if a is not an option and if the destination table has a clustered index and it is not on identity column, then add a Sort data flow component that orders the data rows by the columns used by the clustered index.

    c) set the RowsPerBatch and MaxInsertCommitSize to around 10000

     

    Tuesday, September 25, 2007 5:58 PM