database performance: avg. DB time is high

[Former Member]

My system is ECC6, MSSQL 2005 64 bit.

I got a ERA report from my solution manager and it showed that we had a performance problem.

Our performance overview is red because of the database performance.

The avg. DB time in ms is 2768890,3. It's very high.

Could you please help us on this issue?

Leo

[Former Member · Accepted solution]

HI

A high DB time suggests that the queries are executing expensive SQL statements. This may be due to the following reasons:

The queries may be running multiple nested loops

Indexing of the tables may not be updated.

The table structures may not be reorganised.

Please check when was the last Reorganisation done and are you getting some specific dumps frequently as well?

regards

Chen

[Former Member]

Hi,

As already mentioned bad DB access times depends in a LOT of points that should be analized like Hardware I/O bottlenecks and why are they happening, wrong DB parameters, wrong buffer configuration at the DB and SAP side, expensive sql statements, missing indexes, an some more.

I had twice some problems with DB access times and one of them was tracked (by me and the HW vendor) to the SCSI card that did not have write cache so reading was fast enough but writing was terrible so the card was changed and performance improved, then we spend a lot of time tunning DB parameters, buffers and SQL expensive statements and performance improved more to the point that users were happy again.

Take your time and create a path to track down the problem/s from HW to SW and then you will be able to solve it or improve performance up to the point there will be no more alerts about this.

Good luck

[Former Member]

Hi,

You mentioned

"The avg. DB time in ms is 2768890,3"

What is the total response time? What is the CPU/Memory/IO utilization on the database server?

Check if there are any reports/programs that are using a lot of DB resources. Take a look at Note 155413.

Cheers

[Former Member]

Hai,

There might number of reasons for high DB time.

Check your I/O statistics, check your table structure, create Indexes if needed for efficient table access.

You can schedule Optimize statistics in DB13 to get the statistics upto date which helps DB to get the best possible way to access data from your DB.

Analyse the DB buffers and adjust them to get optimal performance. (Should be done by experienced DB admins).

Check the below links....

http://help.sap.com/saphelp_nw70/helpdata/EN/f2/31add7810c11d288ec0000e8200722/frameset.htm

http://help.sap.com/saphelp_nw70/helpdata/EN/f2/31add7810c11d288ec0000e8200722/frameset.htm

You can also take help from SAP to analyze your case.

Regards,

Yoganand.V

[Former Member]

Yoganand,

Could you please tell me how to check I/O statistics and check my table structure?

Thanks

Leo

[Former Member]

Hi,

Check the I/O information using ST06/ST04. Refer to note 618868 (9). This note gives a good overview in analyzing potential performance issues.

If you need further help, let me know the information that I have requested earlier.

Cheers.

[Former Member]

Avg response time: 432,3ms

CPU utilization: 30%

Memory utilization: 13 GB/16 GB

IO utilization:

latch wait time per request: 5.789 ms

wait time per Log write: 0.549ms

IOStall per request: 0.000

PAGEIOLATCH_SH: 5.842ms/request

WRITELOG: 0.551 ms/request