SQLs erver migration performance issue

70 Views

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 information:

-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..