cancel
Showing results for 
Search instead for 
Did you mean: 

SQLs erver migration performance issue

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

markus_doehr2
Active Contributor
0 Kudos

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

What processes is using the CPU? The SQL Server itself or others? (see task manager)

> -Minimum server memory - 5 GB

> - Maximum server memory - 5 GB

For the time of the migration itself I'd give the database more memory (10 GB or more) and reconfigure later when the import is done.

What parameters do you use for the R3load? I'd recommend

-loadprocedure fast -merge_bck -c 10000

and also set

BCP_LOB=1 in the environment.

Markus

Former Member
0 Kudos

Thanks Markus. SQLserver process at OS level is occupying most of the utilization (around 80%). Is this is a normal behaviour ? Also we shall try with increased RAM and R3load options as suggested..

Former Member
0 Kudos

Thanks Markus, We have tried with fast load and commit option's. However, for la`rge tables while loading indexes, CPU is hitting 95-98% utilization (SQL server process 50-60% and rest of for R3load processes). R3load process is getting failed while creating indexes for large tables. Error in the failed package logs are like below:

(IMP) DATA: 33301545 rows in table "BSIS" #20110819004208

#Trying to create primary key "BSIS~0"

(IMP) ERROR: CREATE statement failed for object "BSIS"

(ALTER TABLE "BSIS" ADD CONSTRAINT "BSIS~0" PRIMARY KEY

CLUSTERED ( "MANDT", "BUKRS", "HKONT", "AUGDT", "AUGBL",

"ZUONR", "GJAHR", "BELNR", "BUZEI" ))

DbSlExecute: rc = 99

(SQL error 11)

error message returned by DbSl:

[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

#STOP: 20110819050635

We have checked SAP note # 39282 and changed MSSQL server settings. Is the issue could be from network side as mentioned in note or SQL server tuning side?

Thanks,

neetha

markus_doehr2
Active Contributor
0 Kudos

> [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

Are there any local firewalls installed on the system? This looks to me like "something" is tearing down the connection.

Markus

Former Member
0 Kudos

Thanks Markus. Issue is from network side. Now import is completing in around 14 hours..

Answers (0)