Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: DTS Transfer Table with Text Column slow

    I am using DTS to transfer some tables from one server to another as part of a migration. We want to be down for as little time as possible, but we need the most up-to-date copy of the database tables in question.

    I am currently testing the transfer process in our test environment by migrating the data from one database to another on the same SQL instance.

    There are 7 tables to transfer and the total size of the database is 450 MB (with around 117 MB used). The two largest tables have around 17,000 records each.

    One table (the header) has no text column and it takes just a few seconds to transfer. The other table (the detail) has two columns, one of which is a text column (actually, its not fair to call it the detail table; the relationship is actually one-to-one, but for the sake of this discussion, let's leave it at that).

    The header takes seconds to transfer, but the detail takes up to 18 minutes.

    Physically, our test server is quite robust; 2 processors, a 3 disk RAID-5 for the data files and a separate RAID 1 partition for the logs. Performance counters don't indicate any real issues: during the transfer, the disk utilization on the data partition occasionally spikes to a high level, but comes right back down until the next spike (the spikes being separated by about 1 minute. No issues with memory, paging or CPU.

    I have removed the clustered index on the affected table as well as the PK. No help.

    Are text columns just slow? Is there something that I am missing?

    Regards,

    hmscott

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Are you migraging the entire database, or just the seven tables? If it's the entire database, you should really just detach, move the data and log file, and attach them. Text columns are naturally slower due to how they are stored though.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Just as in other posts, I'd pick BCP...out...-n/BCP...in...-n -a32764 -h"TABLOCK"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    'kay. Thanks for the responses. Detach/Attach may be a realistic alternative. My only worry is that the servers are in different domains (though I think that should be manageable).

    Regards,

    hmscott

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    He's a question though...

    What is the best way to get text out? What about image?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •