DB Best blog

SQL Server Migration Assistant (SSMA) can be used for database migration, for example, from Oracle to SQL Server. When transferring data from large tables, you can face performance problems  during the data migration process. This article describes how to make the SSMA data migration faster and proposes actions that can improve the performance.

Allocating Target and tempdb Database Size

Each time when more data space is needed (and Auto Grow option is on) SQL Server expands the data file according to growth increment, specified for corresponding data or log file. If initial size of data or log file is too small, the auto grows occur frequently, hurting performance. Also, frequent auto grows may occur if growth increment is inadequately small. Initial allocation of all required target space for the SQL Server database and log files before the data migration can decrease resources consuming. Set initial size of the data file roughly equal to actual size of data in the source database. Also, set initial size of tempdb database to reasonable value. The size and growth increment of the file can be specified by means of ALTER DATABASE statement, for example:

ALTER DATABASE [database_name] 
MODIFY FILE (NAME = [db_file_name], SIZE = 2000MB, FILEGROWTH = 20%);

For more information about setting of database size please refer to ALTER DATABASE topic in SQL Server Books Online.

Setting Recovery Model

As a database becomes larger, full backups take more time to finish and require more storage space. Under the simple recovery model, the transaction log is automatically truncated. This saves backup time and the cost of increased complexity.

Set recovery model of target database to Simple before data migration and revert recovery model back after migrating data. These settings can be specified by means of ALTER DATABASE statement:

ALTER DATABASE [database_name] SET RECOVERY SIMPLE

To revert the recovery model back after migration is over, one of the following statements can be used depending on your initial recovery model:

ALTER DATABASE [database_name] SET RECOVERY FULL
ALTER DATABASE [database_name] SET RECOVERY BULK_LOGGED
Setting the Batch Size

Increasing Batch Size parameter may improve data migration performance.  If the row size of tables is not very large, try to change the value to 100000. You can set this parameter in SSMA project settings window: choose Tools/ Project Settings and in the opened window choose General tab and then Migration item. In the Misc group you can specify the Batch size.

Disabling Indexes

To improve performance you may disable unneeded indexes before data migration. Typically all indexes but heap, clustered, unique and primary key may be disabled. Disable all non-unique, non-clustered indexes before the migration and rebuild the indexes after it.

You can use the following operator for disabling indexes:

ALTER INDEX [index_name] on [user_name].[table_name] DISABLE

And this operator can be used for rebuilding indexes:

ALTER INDEX [index_name] on [user_name].[table_name] REBUILD
Setting Table Lock

Choosing Table lock = True in SSMA project settings also can increase the performance. This option can give performance gain up to 20%, but you will not have any access to the target tables before the migration is over.  It is happened due to bulk update lock on the target tables for the duration of the migration process.

To set this parameter, choose Tools/ Project Settings and in the opened window choose General tab and then Migration item. In the Misc group you can specify the Table lock.

Disk defragmentation

Frequent auto grow of database files with small growth increment may lead to hard file system fragmentation. Set initial size of target 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”.

 

 Written by Galina Shevchenko

Tagged as data copy, data migration, data move, data transfer, fast, Migration, optimize, Oracle, performance, quick, reduce, slow, SQL Server, Sybase + Categorized as Newsletters, Newsletter #7, February 27, 2009