SQL server performance issues while imporing export dump

[Former Member]

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

OS - Windows 2003 x64

DB - MSSQL server 2005 SP4

SAP - 4.6 D kernel

CPU - 4 processors (2.67ghz each)

RAM - 16 GB

source system DB size - around 1 TB 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 10 parallel processes and after import is started, CPU is getting 95% to 99% utilized and when we check SQL server studio-> Activity monitor we found below items:

-There were around 20 processes are in status sleeping

-Only one or two system processes are running with commands - INSERT,SELECT INTO commands ( column)

-At any point of time only two processes are running and CPU utilization is hitting high.

-Import is very slow and its taking 35 hours to complete

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

-Minimum server memory - 5 GB

- Maximum server memory - 5 GB

- index creation memory - 0

- Maximum memory per query - 1024 kb

- Maximum degree of parellelism - 1

- Parallellism locks - 0

- Cost threshold of parellism - 5

- Enabled trace flags - 610,620,1117,1211,3917

- Windows environment variable BCP_BATCH_SIZE = 10000

- SQL log file size - 100 GB

- tempdb size - 20 GB

2) When we tried another test import after tuning parameters (Maximum degree of parellelism to 3) and increasing r3load parallel processes to 20, we found of inserts are overflowing in process list and blocking other waiting processes and putting them in suspended mode.CPU utilization is at 80%

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?

Thanks..

[nicholas_chang · Accepted solution]

Hi,

1) please ensure tempDB & LOG file are allocate in different drive to distribute IO load.

2) assign 500MB to tempdb log

rule of thumb, 1 CPU core x 2 parallel processes, if you have 4 CPU, you should assign 8 parallel processes. However you should always monitor the CPU usage to futher improve or decrease the number of parallel processes, depends on the CPU utilization during import.

You can download migtime from SMP to analyze and evaluate your import time. Based on migtime result, you can enhance import time by adopting sort order or table splitting since you have 1TB of data.

Thanks,

Nicholas Chang.

[Former Member]

I think generally it takes 2 days for import with your kind of h/w.

You should try other methods if downtime is critical t you.

Try table split method or simultaneous export and import ?

This will help you to reduce downtime during the production run