High db workload time on application server

[Former Member]

Good day

We are running Windows Server 2003 and SQL Server 2005.

We have a serious problem that one of our application servers are reporting 98% DB time of total response time in ST03N. All other application servers are reporting DB times of around 30 - 40 % which is normal.

How can we analyse what is wrong with this application server? It is running at around 3 % CPU and only utilises about 10% of available memory. ST06 does not show any memory swapping or disk problems.

The database server is running fine. No noticable issues - and processes are running with good response times to the db on all the other application server.

Please assist!

Thanks

Wilhelm

[Former Member · Accepted solution]

Good day

The server is in the same logon groups as the other application servers (SMLG)

The server has the exact same profile parameters as the other application servers (also the same CPU's and memory)

ST02 has no buffer swaps - same as the other application servers.

I have checked network connectivity to database server using NIPING. It reports the same responses as from the other application servers.

I have checked running different reports/programs on the server. If I run for example program RPCIPE00, which is payroll postings, it takes hours on this server, while only running for minutes on any of the other application servers. So, yes, this server is behaving differently to other app servers when running the same kind of reports/programs.

It is very strange behavior. Is there a way to analyse the 99% time in DB further to determine what it is made up of, i.e. what part is network, what part is connecting to db, what part is in db, etc etc?

Thanks

Wilhelm

[Former Member · Accepted solution]

Is there a way to analyse the 99% time in DB further 

Yes of course, you can do an SQL trace in ST05 and check which SQL takes the longes time (again compare it to the other servers).

Also have a look at this message, especially at the little benchmark mentioned there:

Cheers Michael

[Former Member]

How did you distribute the available application servers in SMLG and RZ12? Does all servers have the same load, number of DIAG/TOTAL steps per day?

When the mentioned server is dedicated for database intensive reports, then the 98% DB time are explainable.

REgards BErnd

[Former Member]

Double check if the same transactions/reports are executed on that application server. Compare one transaction, look for one with a lot of total executions.

Second check the buffering of the application server, especially the table buffers. Transaction ST02 Generic Key and

Single record, compare with other application servers.

Third check the network connection (but only if the above two look ok).

Cheers Michael