MSSQL 2012 poor performance versus DB2

[former_member323185]

Hi,

We just performed a OS/DB migration from iOS/DB2 to Wintel/SQL. After the migration, we discovered abap reports run much slower on the Wintel server compared to AS400 server. The Wintel server possess more CPU and RAM than the AS400 server. From SE30 and ST05 results, we suspect database may be the issue. Below are some screen shots of database access between the two servers using the same abap report:

Fetch on table COEP takes 12 seconds on MSSQL. 77k records were retrieved

Same fetch on table COEP takes 3 seconds on DB2

Fetch times on MSSQL is around 1000ms for each fetch of 13 records

Fetch times on DB2 is mostly below 1000ms for each fetch of 63 records

Can anyone explain why the poor performance of MSSQL compared to DB2? Why is MSSQL able to fetch only 13 records per fetch while DB2 is able to fetch 63 records per fetch?

Is there anything we can do to improve the performance of MSSQL?

Rdgs,

Lim

[Sriram2009 · Accepted solution]

Hi Wee

As you said after migration ABAP reports are running slower in MS SQL 2012. For this you can enable the ‘single transaction analysis’ T- code ST12 in both systems, then analysis the trace in both systems check the summary of the MS Sql statements in that you can find the expensive Ms Sql statement and if any index advices are there ? I thing you may require to create some of the indexes in MS Sql 2012 then you performance issue will be resolved

Thanks & Regards

Ram

[former_member323185 · Accepted solution]

Hi Ram,

I have use SE30 and ST05 to analyze individual abap reports. I did find some slow SQL queries but they are slow when compared to the old DB2 system. If we didn't migrate from DB2 to SQL, we wouldn't know that SQL is performing SQL queries slower than DB2. Take the example of reading BSEG table, SQL took 1 min and DB2 took 2 secs for the same query.

I also did make changes to the query statements and build indexes to speed up the report performance in SQL which resulted in closer reponse times to the DB2 system. But the question is, why do I have to do that for SQL? Are there some tuning on SQL Server that I can do to avoid changing abap codes and building indexes?

Rdgs

[Sriram2009 · Accepted solution]

Hi Wee

  Thanks for you information.

1. Sine your are migrated from DB2 to MS SQL i thing at the time of migration it may asked option to to the DB compression? you have enable the compression? it is not pl follow the SAP Note 1488135 - 

2. Could you share your SAP & Physical system configuration details? Since you are using the windows system have you define the memory configuration as per the SAP note 88416 - Zero administration memory management for the ABAP server?

before that could pls paste the ST02 screen shot?

check this and get back

Thanks

Ram

[former_member323185 · Accepted solution]

Hi Ram,

The SQL database is compressed. Below is the screen shot of ST02:

[Sriram2009 · Accepted solution]

  Hi wee

   As per ST02 output some of the parameters are require to change the values as mention below screen shots

1. Extended Memory  current value 32 gb you can reduce it 10 gb

2. Page & Roll are already having the hit ratio 100% you can increase it double the values 

3. Heap memory is not defined you can define some about memory

4 . Kindly execute the DB check in MS Sql 2012 off time during the time keep an eye on transaction log as per the SAP Note 142731 - DBCC checks of SQL server 

Kindly check the finding and let us know the status

Thanks

Ram

[former_member323185 · Accepted solution]

Hi Ram,

We are on Wintel platform, so we are following the Zero Memory Administration note 88416 to set the memory buffer parameters. Below is the screen shot of the buffer parameters set according to this note:

I ran DBCC Check and no warning or error is reported.

Rdgs

[Sriram2009 · Accepted solution]

Hi Wee

Another few finding which we did in my environment just I want share with you

1. You can define the MS Sql Server memory options in MS Sql server management studio. 10 GB (70% applications + 30% database usage)

2. you have define the virtual memory 32GB *2.5 = 80GB (Not in C drive)

After defining this many thing still you ABAP reports are running slow ?

Thanks

Ram

[former_member323185 · Accepted solution]

Hi Ram,

Thanks for your help so far. The memory allocation ratio is already at 70/30. Page file is set to 3 times the physical memory. I don't think it is the memory or buffer settings that are causing the slow performance.

The problem I suspect is the way SQL Server reads data compared to how DB2 reads data. Anyway, I will use table indexing and SQL query optimization to overcome the performance problem.

Rdgs

[clas_hortien]

Hi,

the difference in the execution time is most probably due to a non-optimal plan to retrieve the data. We should look at the access plan SQL Server is using to see if this is the best plan to retrieve the data.

Can you please send the explain of the COEP statement, you can do this on the OPEN of the statement.

The amount of rows that the SQL Server DBSL can retrieve is limited by an internal buffer of the DBSL, which might be larger in DB2. The SQL cache is 48K (if I remember correctly) and based on the rows size of the selection only 13 rows will fit into it. We have never seen any problems with the size of these buffers. It is not configurable.

FYI, the runtimes in ST05 are in microseconds, means 1000 is one millisecond.

If you need a deeper analysis you should open an OSS case on this.

Best regards

   Clas Hortien

[Former Member]

Hello, Could you share the final performance.

Did you recomend SQL or continue with DB2?

[Rob3]

Hi Lim,

Another area of investigation can be the architecture of the underlying system. Wintel systems are much more sensitive to data IO issues. You can validate performance of the underlying database with transaction DB02. You want to verify that the Database File IO for Reads is 10ms or less. Then you can check ST03N and validate that the database response time is averaging 600ms or less for transactions. Both are baseline standards for SAP systems.

Thanks
Rob

[former_member323185]

Hi Robert,

I use RZ20 to check the I/O performance of the data and log files. Both times are 11ms and 2ms respectively. Avg read time in ST03N is 0.3ms for dialog process.

I don't see any I/O problem with the system.

Rdgs

[former_member323185]

Hi All,

Thanks for the tips. I have gone through them all and have implemented those tips. After further analysis, I discovered that the issue is with SQL Server open cursor duration which takes a few hundreds of miniseconds. This may be normal for SQL Server but when compared to DB2 2-digits duration to open a cursor and adding all the SELECT statements in a abap program, the difference in response times become significant.

Is there any way to improve the Open Cursor times for SQL Server? The abap program is just using standard SELECT statements. Opening and closing of cursors commands are issued by the SAP system.

[Former Member]

Check the Note : 1558087 and 1224738

this should help you in solving the problem.

[former_member323185]

Hi Nitin,

Thanks. We are on SQL Server 2012. Event 2371 is already implemented by default.

There is no setup error as described in 1224738 in RZ20.

Rdgs

[former_member188883]

Hi Wee,

Please refer SAP note 1744217 - MSSQL: Improving the database performance

Following the guidelines in this SAP note should provide some assistance in fine tuning.

Hope this helps.

Regards,

Deepak Kori

[former_member212248]

Have you tried to update the statistics?

[Nicolas]

Hi Wee Seng Lim,

Did you read the blog Tune your SQL Server SAP Database written by Beate Grötschnig ? It is a good starting point to improve performance of your SQL Server Database.

Best Regards,

Nicolas Vander Auwera