Performance issues on BW on Windows/SQL 2005 server

[Former Member]

Hi All,

I have a tricky performance problem on our producton server where on one day I found the average response time/ dialog step being about 10000 secs on a particular day of the month. On other days when we do not use a particular application in BW the av response time per dialog step is around 1200 ms.

When the av response time shoots to 10000 ms per dialog step I find the break down as below:

2200 ms CPU time

6700 ms DB time

1000 ms roll wait time

when the av response times is the normal 1200 ms the break down is as below:

400 ms CPU time

450 ms DB time

190 ms roll wait time

the ST04 on the database shows a very good data and procedure cache hit ratio of 99%

I have 18 GB RAM and 6 CPUs on the server.

I have only 5 disks on the system visible in ST06 the average read times (read IO stall) are areound 25 to 30 ms is this acceptable ? I thought the avg read response times should be around 1 to 5 ms.

In ST04 I see of the 18 GB RAM about 7 GB is assigned to the DB .

Can any of please clarify the cause of the bottlenecks I also see quite a bit of IO waits, queue lengths on the disks and some disks are heavily 100% used. Also, in ST06 I see a constant page out of around 500 MB

Also I see a sizeable amount of buffer swaps in program buffers, nametab buffers and single record buffers there are more than 15 to 20000 buffer swaps in a week.

Appreciate your inputs on the key causes of the performance issues.

Thanks

Karen

[Former Member]

Karen - I have faced similar issue at specific times of the week and month.

This is what you need to do:

You need to find out if Extractors are running on that time. High Dialog processes are telling me that extractors are running at that time.

You also need to make sure your info cubs are being maintained properly. You need to find out which reports are running on that time and make sure they are tunned properly.

Give me your email address, I have a tunning material that I can send you.

Regards

K

Edited by: kourosh Ghouchkhani on May 20, 2010 8:37 PM

Edited by: kourosh Ghouchkhani on May 20, 2010 8:38 PM

[Former Member]

Hi Khouroush

Many thanks for offering the tuning material can you please let me know how I can contact you I do not find your contact address on your business card the forum does not let us post email address in the post.

Thanks

Karen

[markus_doehr2]

> I have a tricky performance problem on our producton server where on one day I found the average response time/ dialog step being about 10000 secs on a particular day of the month. On other days when we do not use a particular application in BW the av response time per dialog step is around 1200 ms.

Is the machine maybe swapping at that time?

Is there a backup running - or an update statistics scheduled?

Markus