on 07-24-2013 4:11 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello, Could you share the final performance.
Did you recomend SQL or continue with DB2?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Have you tried to update the statistics?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.