SQL Server Performance

Data Import from one server to another server is taking lot of time

Discussion in 'SQL Server 2005 Performance Tuning for Hardware' started by sampathmartha21, Apr 23, 2008.

  1. sampathmartha21 New Member

    Hi,
    I am using SQL Server 2005 Import wizard/DTS Package to Import one table data from X Server to Y Server. To Import 20 million records its taking almost >3 hours, when i am importing i observerd in Y Server -> Task Manager -> Network Utilization is 0.25% of 1 Gbps. If i am copying any fdata file (using copy and paste command) then i observed the n/w utilization is increased to 30% of 1 Gbps.
    I also tried to import the same data from X Server to Z Server then it completed with in 5 minutes and the n/w utilization at the time of import is 10% of 1 Gbps on Z Server.
    All the X,Y,Z servers are 8 proc machines and having 32GB RAM and all are in the same domain.
    If anybody has solutions. Please post it.
    Thanks & Regards,
    -Sam
  2. MohammedU New Member

    Check the database recovery model and allocated free space in the database. If it is growing while copying the data it will take time.
    And also make sure you have the right NIC configuration.
  3. sampathmartha21 New Member

    Hi Mohammed,
    i checked the data base recovery model and its Full Recovery on both Y Server and Z Servers ( i mean destination sql servers) i mentioned previously.
    the dabase free size available is almost 5 GB and file growth for both ndf and log files are 5GB with unrestricted growth.
    When i used xp_cmdshell 'copy \XServer*.* \YServer' its copying fastly and the n/w utilization is almost 30% of 1Gbps. I mean the file copy is very fast so, i think its looking like there is no NIC problem because for the copy command and SQL import might use the same network.
    We are suspecting on SQL configurations for network transmission or any SQL s/w patches.
    We are using SQL 2005 with SP2.
    Regards,
    Sam.
  4. jagblue New Member

    Hi,
    Instead of using Import wizard create new SSIS package
    In Import/Export wizard default batch size is 0 i.e. data want committed until everything is transferred
    If you create package you can use fast load option and change batch size to some thing like committed after 1000 records

    Thanks

Share This Page