STAD results show DB req time greater than the response time

[Former Member]

I am measuring the performance of RFC calls using STAD. After executing the RFC's (have been exposed as web services) I go to STAD and to my surprise the data base request time is greater than the response time (see below). How is this possible? Any help will be greatly appreciated.

Response time 4,177

Time in WPs 4,177

Wait time 0

CPU Time 2,609

DB req. Time 142,597

VMC elapsed time 0

Edited by: WMaldonado on Apr 22, 2009 10:47 PM

[Former Member · Accepted solution]

Hello Wilfredo,

sounds curious, but what hardware this system is running on? AMD CPUs? [Note 1036194|http://service.sap.com/sap/support/notes/1036194] describes a problem with CPU timers which could lead indeed to a similar situation as you described (parts of response time greater than the response time itself).

Regards, Michael

[Former Member · Accepted solution]

Hi Wilfredo,

1. what type of DB action is going on, my guess is sequential read of some table?

You are right that repose time has to be sum of all parts of response times, can you do the follwoing

1. Run Database statistics from DB13

2. check for failed index in DB02 (this might not be our issue but just to make sure)

3. run the web service which is causing issue for one user and pull the STAD and see if you have same issue. I had this kind issue earlier and was resolved after creating the index for affected table.

4. if you still have issues, please attch the STAD reports.

2. Check if DB stats are up to date

Database statistics - go to DB13 and execute update statistics. If you are not sure how to execute from DB13, please let me know your R3 version and Database being used.

Hope this helps.

Thanks,

Praveen

[Former Member · Accepted solution]

Hello Michael,

I believe it is a HP Proliant DL385 (AMD processors).

Regards,

Wilfredo

[Former Member · Accepted solution]

Hello Praveen,

Any assistance on how to run update statistics from DB13 will be greatly appreciated. The SAP system component version is SAP ECC 6.0 and the data base is MSSQL release 9.00.3077#.

Thanks,

Wilfredo

[Former Member · Accepted solution]

Hi Wilfredo,

Go to - TCODE - DB13

click on 'ADD'

you will get popup window select 'Check and update optimizer statistics' in action

In tab Action parameters select 'All tablespaces'

In tab recurence

for "Every" put 1

select 'Days' and select timeperiod with low user activity

and click on 'ADD'

You can also use 'Execute immediately' for first time and see the logs to make sure the DB stats were sucessful. then you can follow above procedure for periodic scheduling, it is useful to have DB stats run frequently. Depending on your system and DB once a day or weekly.

If you need screenshots let me, I am not sure if I can attch it here.

Thank You,

Praveen

[Former Member · Accepted solution]

Hello Wilfredo,

in this case checking the CPU timers should not take too much time. Simply go to st06, Detailed analysis, HW info. If the output in section "System Information" returns problems with the CPU timers you encounter the problem described in the mentioned note. If this is the case the DB request time in ST03/STAD will be worthless until the CPU drivers are installed.

Regards, Michael

[Former Member · Accepted solution]

Hello Michael,

We modified the boot.ini file as the alternate suggestion in the note you sent me. After invoking one of the web services we could see that the db req time is now less than the response time. Still, I executed ST06 and it says that the Fast CPU timers are not synchronized. That's the only entry regarding CPU timers I see under System Information. This was after we modified the boot.ini and added the /usepmtimer key. The note mentions that the drivers must be installed prior SAP installation. Should we try to install the drivers anyway?

Thanks for your help. We've been stuck with this issue for a few days.

Thanks,

-Will

[Former Member · Accepted solution]

Hello Praveen,

It looks like the main cause for our issue is in the note Michael sent, but we think it will not hurt to update the DB statistics. I went to DB13 but I don't see any action 'Check and update optimizer statistics'. The only options I see in the action pad are "Check Database Consistency", "Differential Database Backup", "Full Database Backup", "Transaction Log Backup", "User Database Backup" and "Central Calendar Log Collector".

Am I missing something?

Thanks,

-Wilfredo

[Former Member · Accepted solution]

Hello Wilfredo,

as the note mentions both options as equal there is no need to install the drivers afterwards. I for my part would nevertheless install the drivers at some time (because it is easier to change the boot.ini by mistake and delete the additional parameter than to uninstall the drivers

Regards, Michael

[Former Member · Accepted solution]

Hi Wilfredo,

Yes something is missing. Please give us the following information SAP version, DB, Kernel level.

Thank You,

Praveen

[Former Member · Accepted solution]

Hello Praveen,

Here is the requested information.

Kernel Release 700

Compilation NT 5.2 3790 Service Pack 1 x86 MS VC++ 14.00

DB releases MSSQL 7.00.699 or higher, MSSQL 8.00.194

DBSL version 700.08

DBSL Patch level 144

-Wilfredo

[Former Member · Accepted solution]

Hello Wilfredo,

Sorry for delayed reply I was busy with Go-Live. The DB stats for MSSQL database should be checked from tcode 'DB02'. If you go to tcode 'DB02', you will under box 'Database', the selected database options and you will have two options

1. AutoCreateStatistics:On

2. AutoUpdateStatistics:On

If you see any of these options as off, please go to MSSQLenterprise manager of MSSQL and change the properties for that database. If you need information how to do this please let me know.

Thank You,

Praveen Gudipudi

[Former Member]

if you are talking about a single STAD record then I would say this must be a bug of the kernel.

because the database time here is measured by SAP kernel, reponse time must be greater than database time.

This is the definition of the database request time:

Starts when a database request is put through to the database interface; ends when the database interface has delivered the result

it has nothing to do with table/index statistics on database

[Former Member]

Hi WMaldonado,

This could be due to many reasons starting point would be to check

1. what type of DB action is going on, my guess is sequential read of some table.

2. Check if DB stats are up to date

3. Check which user and service is calling is making the RFC call.

Thank You,

Praveen

[Former Member]

Hello Praveen,

Thanks for your response. I am not that experienced when dealing with R/3 and STAD. Please see my comments below regarding your suggestions.

1. what type of DB action is going on, my guess is sequential read of some table.

Yes, it is a sequential read. Being a sequential read, shouldn't the process wait until the data is retrieved?

2. Check if DB stats are up to date

What do you mean by DB stats?

3. Check which user and service is calling is making the RFC call.

The posted results belong to one of the executed service calls when having 5 concurrent users accessing the data. All users show pretty much the same results. This service call in particular should return a list containing 50 records. Data is the same for all users.

Thanks,

-Wilfredo