MSSQL 2000 Performance issues

[Former Member]

Hi,

One of our customers is using ECC5 with MSSQL 2000. They are experiencing performance issues in QA server while PRD is working fine. Both servers have same hardware configuration. But ST03N shows:

1. 47% CPU time and 42% DB time on QA with av. response time/dialog step being 1213 ms.

2. 35% CPU time and 20% DB time on PRD with av. response time/dialog step being 542 ms.

Tests:

Trn code PTMW shows 1,338 ms as Av. resp time/dialog step on QAS

Trn code PTMW shows 341 ms as Av. resp time/dialog step on PRD

Trn code PP61 shows 1,154 ms as Av. resp time/dialog step on QAS

Trn code PP61 shows 999 ms as Av. resp time/dialog step on PRD

Trn code PTMW shows 623 ms as Av. DB time /dialog step on QAS

Trn code PTMW shows 63 ms as Av. DB time /dialog step on PRD

Trn code PP61 shows 532 ms as Av. DB time /dialog step on QAS

Trn code PP61 shows 244 ms as Av. DB time /dialog step on PRD

Trn code PTMW uses 58% CPU time and 47% DB time on QAS

Trn code PTMW uses 45% CPU time and 18% DB time on PRD

Trn code PP61 uses 55% CPU time and 46% DB time on QAS

Trn code PP61 uses 63% CPU time and 17% DB time on PRD

Notes:

1. The QA server is at SAP kernel level 155 while PRD is at 101. QA is at higher application support pack levels also.

2. The QA DB server has more memory configured than PRD.

3. ST02 shows high number of swaps on PRD. I have tuned the parameters on QA though. So ST02 on QA is fairly good.

4. ST04 on both servers shows about 99% hit ratio for Data and Proc cache

5. ST04 shows that both servers are using the same index for data selection. QAS has latest index statistics

Despite this, QAS is showing very bad response times. Can anyone give some hints about resolving this issue?

Thanks and regards,

Shehryar

[markus_doehr2 · Accepted solution]

> 1. 47% CPU time and 42% DB time on QA with av. response time/dialog step being 1213 ms.

> 2. 35% CPU time and 20% DB time on PRD with av. response time/dialog step being 542 ms.

Do they use the same storage?

Markus

[Former Member · Accepted solution]

Hi,

Thanks. Both servers have similar configuration for storage. Is there anything specific you are looking for?

Regards

[markus_doehr2 · Accepted solution]

I mean - do they use the SAME storage system or a different one?

Markus

[Former Member · Accepted solution]

Both servers have their own storage. There are two separate clusters running, one for QAS and one for PRD.

I was looking at the disk configuration, the data files for QAS all reside on a single hard disk, while data files for PRD have been mapped to separate hard disks. This might be causing some performance issues on QAS. RZ20 suggests IO performance of 6ms for QAS data files, and 1ms for PRD data files. While a response time of 10ms is considered acceptable, I need to get as close to PRD as possible.

ST04 comparison for both servers doesn't show much difference in performance counters. Any ideas what else I should look at to identify DB bottlenecks like CPU, RAM, IO, etc What are acceptable values for Latch Wait time, Log Write wait time, and IOStall?

Regards,

Shehryar

[markus_doehr2 · Accepted solution]

I was looking at the disk configuration, the data files for QAS all reside on a single hard disk, while data files for PRD have been mapped to separate hard disks. This might be causing some performance issues on QAS. RZ20 suggests IO performance of 6ms for QAS data files, and 1ms for PRD data files. While a response time of 10ms is considered acceptable, I need to get as close to PRD as possible.

Note: that number is per I/O access time.

If a number of I/Os take 10 seconds on PRD it will take 60 seconds in QA.One harddisk can only do a specific number of I/Os in a second so you may be at your max. harddisks throughput and there´s not much you can do about it but spread it across different disks.

Markus

[Former Member · Accepted solution]

Thanks. Is there any other performance measure I can use to confirm that the disk is causing bad IO performance?

SAP also suggests looking at ST04 -> Detail Analysis menu -> IO per file -> "IO wait: ms per read operation". This value is about 7ms for QA and 0 for PRD.

But what is confusing me is that in ST04 -> Current Activity, QAS shows 0 for IOStall Per Request and PRD shows 6,616. Shouldn't IOStall show the same value as shown for ms/read operation when looking at IO per file?

Regards

[markus_doehr2 · Accepted solution]

Thanks. Is there any other performance measure I can use to confirm that the disk is causing bad IO performance?

You could use perfmon and compare the values for one disk for both systems.

SAP also suggests looking at ST04 -> Detail Analysis menu -> IO per file -> "IO wait: ms per read operation". This value is about 7ms for QA and 0 for PRD.

That means that each single I/O needs to wait 7 ms before it gets executed.

But what is confusing me is that in ST04 -> Current Activity, QAS shows 0 for IOStall Per Request and PRD shows 6,616. Shouldn't IOStall show the same value as shown for ms/read operation when looking at IO per file?

Maybe (I can just assume) there´s nobody working on your QA or there´s not done any I/O that would cause the system to stall them. Your production is most likely more loaded than your QA (at the same time), no?

Markus

[Former Member · Accepted solution]

Yes, PRD is running pretty much 24/7. But I am running reports and transactions on QA which are impacting values in IO per file section. But the value for IOStall is not updating.

Any way, I am going through SAP note 521750 now and will try to drill down a bit further. Thanks for all your comments. These have been very helpful. I'll update the thread as soon as I learn anything.

Thanks and regards,

Shehryar

[antonio_voce · Accepted solution]

HI , yes the major indicator for bad performance on the I/O is queue waith lenght and you can check trought transaction st06 ->disk.

This number must be maxymum 2 * phisical number of disk ( it depends on your hardware configuration , Array controller ).

If you see that this number gone over you have sure I/O bottelneck.

I/O bottelneck depend on various factor :

Array controller : cache an maximum Iops

number of the disks : 1 scsi disk can do about 200-250 iops , 10 scsi disks in raid disks perform 2000 2500 iops when the data isn't in cache !!!!

Also more RAM means more buffer space and less i/o operations.

Antonio.