photo

Vasanth

shared this question
6 years ago

Statistics

1
Comments
256
Views

Relates to

Share

Tags

1
votes

Data migration speed

Hi,

I execute Migrate Data command in SSMA after conversion, but the data transfer seem to run very slowly. What can I do to improve the performance?

My regards,

Vasanth

Add Comment

Comments (1)

photo
2

Hi Vasanth,

First, you should check the target and tempdb database size.Each time when more data space is needed (and Auto Grow option is on) SQL Server expands data file according to growth increment, specified for corresponding data or log file.If initial size of data or log file is too small auto grow may occur frequently, hurting performance. Also, frequent auto grow may occur if growth increment is inadequate small.Set initial size of data file for database nearly equal to actual size of Oracle’s data.Also, set initial size of tempdb database to reasonable value.For more information about setting of database size please refer to description of ALTER DATABASE .. MODIFY FILE command.Next, check current Recovery model.Set recovery model of target database to Simple before data migration and revert recovery model back after migrating data.Other items to check include:a) Batch sizeIncreasing “Batch size” parameter may improve data migration performanceb) IndexesTo improve performance you may disable unneeded index before data migration.Typically all indexes but heap, clustered, unique and primary key may be disabled.For more information about index disabling please refer to ALTER INDEX .. DISABLEFor more information about enabling indexes please refer to ALTER INDEX .. REBUILDc) Disk fragmentationFrequent auto grow of database files with small growth increment may lead to hard file system fragmentation. Set initial size of database to reasonable value and then run defragmentation for your hard disk.For more information about file system defragmentation please refer to “Using Disk Defragmenter” topic in Window’s “Help and support center”.Best wishes,

= = Yuri Rusakov

Leave Comment

photo

Attach files...

The file must be a jpg, gif, png, bmp, ico, pdf, doc, rtf, txt, zip or rar no more than 2M