SAP Migration - Import very slow - Source Oracle 11g - Target SQL Server 2005 S3

[Former Member]

We are performing migration from oracle 11g to MSSQL server 2005 (Windows 2003/SAP 6.40).Our target system configuration is like below:

• OS - Windows 2003 x64
• DB - MSSQL server 2005 SP3 CU8
• SAP – 6.40 Non Unicode
• CPU - 8 processors (3.47ghz each)
• RAM - 28 GB
• Recovery Mode – Simple.
• Temp DB 40gb DATA, 10gb Log (Since it is simple mode, it’s hardly getting use)
• source system DB size - around 450 GB and package splitting was implemented.

While importing the export dump that came from source system, we are facing load performance issue's.

1) We have started 40 parallel processes and after import is started, CPU is getting 50% to 70% utilized.

• Import Took around 6 days. It gets very slow on table S524 which is largest in source. After import Table S524 size in target was 600GB where in source it was only 100GB. Size of Rest of the table increase slightly, may be by 5%. Overall size of target DB is 950GB.

We have followed few SAP notes(1054852,1241751 e.tc..) and below are the settings for SQL server:

• Minimum server memory - 13 GB
• Maximum server memory - 13 GB
• Index creation memory - 0
• Minimum memory per query - 1024 kb
• Maximum degree of parellelism - 0
• Parallellism locks - 0
• Cost threshold of parellism - 5
• Enabled trace flags - 610,620,1117,1211,3917
• Windows environment variable BCP_BATCH_SIZE = Not set
• SQL log file size - 40 GB
• Tempdb size - 50 GB

Are there any SQL server parameters need to be tuned to fix this import load time issue in both scenarios? Are there any  best practises for migrating to SQL server?

Export finished very quickly as expected in 10 hours.

Thanks..

Mamadi.

[Matt_Fraser]

Hi Mamadi,

Although there is some conflicting advice out there for migration imports, in general you probably want to set Max Degree of Parallelism to 1. This is the setting you'll want for normal, productive operation, in any case. Note 1054852 talks about setting it to 4, and with 8 processors that might work for you. However, the Oracle to SQL Server Migration FAQ ( SAP OS/DB Migration to SQL Server–FAQ v5.2 April 2014 - Running SAP Applications on Microsoft Platf...) recommends keeping it at 1, but says 4 is also probably fine (but may show no benefit). However, they are very specific about not setting it to 0.

Double-check that you aren't getting a lot of autogrowth on your DB files. You may need to manually extend the files to a larger size so that autogrowth doesn't occur. Autogrowth can be very expensive on performance.

Also, did you disable the creation of secondary indexes during the R3LOAD? You will need to create them before starting productive use, but you can do it after the import is done to save time. The FAQ has more information. This is when setting MaxDOP higher than 1 might help, as opposed to during the import.

Even though you are minimizing transaction logging, some logging still occurs, so check that your transaction log is large enough and not autogrowing.

Regards,

Matt

[Former Member]

Will Appreciate your expert advice.

Regards,

Mamadi.