Performance Problem

Question

Performance Problem

raj-247939

SSCertifiable

Points: 5999
More actions
November 18, 2005 at 11:40 pm

#188456

Hi all,

One of our client had purchased a new RAID 5 HP Server with 32 GB of RAM + 4CPU
The problem is that every thing had been properly installed. But the users are complaining that ERP Application is still slow.
I looked in every area and it seems that SQL server 2000 SP3 is not using RAM properly.
I need help that what would be wrong with the server or sql server EE, and in which area i should search for the slow performance .
from
Killer

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

dougjjj SSCrazy Points: 2081 More actions · Answer 1

Have you enabled AWE? Enterprise Edition won't use more than 2GB of RAM by default, you need to alter the Boot.ini for it to utilise more memory.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 2

Hi,

AWE is enabled and stopped and restarted the sql services.

from

Killer

dougjjj SSCrazy Points: 2081 More actions · Answer 3

Have you just enabled AWE in the SQL config or have you added AWE to the Boot.ini file and restarted the machine?

BobAtDBS SSCarpal Tunnel Points: 4588 More actions · Answer 4

You say that the App is "still slow". Reading your sentence in a purely logical way, are you saying that it was slow before, you did a massive hardware upgrade, and it's "still slow"? If so, the problem might not be the hardware/memory/configuration, it might be the application itself.

I'd be looking at traditional things like indexes on joined columns, indexes on columns in where clauses. Try the index tuning wizard. Try performance monitors. "Interogate" users and find out specifically what is slow, and then check those Stored Procedures for poor performing queries.

In my experience, it's not usually the hardware. Not until you can see performance slowly go from good to acceptable to poor as more users come on line.

Student of SQL and Golf, Master of Neither

ramesh-248220 SSC Enthusiast Points: 185 More actions · Answer 5

Hi ,

Check that SQL is using the RAM properly after enabling the AWE option.

--Total amount of dynamic memory the server is currently consuming

select object_name,counter_name,'Amount of memory used in(GB)'=convert(float,(cntr_value))/(1024*1024),cntr_value from master.dbo.sysperfinfo

where counter_name='total Server memory (KB)'

--Total amount of dynamic memory the server is willing to consume

select object_name,counter_name,'Amount of memory SQL will use in (GB)'=convert(float,(cntr_value))/(1024*1024),cntr_value from master.dbo.sysperfinfo

where counter_name='Target Server memory(KB)'

Run SQL profiler and see any queries/SP taking more then 2 seconds to run . From there work on the queires/SP which are taking more time and fine tune them if possible.

But if it is ERP package then I don't think it is possible to tune the queries as they may be in hundereds.

First thing I will suggest is rebuilt all the indexes (on a weekend) may be the stats are out of date.

Then I would follow SQL server best practices on the server like

Check the SQL Server Settings:

Database Auto Shrink should be off

Min Memory/Max memory setting

Priority boost off (to start with)

Max degree of Parallelism

Lastly check the DISK I/O to make sure the Disk are not the bottle neck using performance counters.

BTW what ERP you are using?

Cheers

Ramesh

raj-247939 SSCertifiable Points: 5999 More actions · Answer 6

Hi,

Thanks for all ur help .

The application was slow before the client purchased the new server but the old server have only 6 GB of RAM.

Now the server have 32 GB of RAM.But still slow.

I have conigured the AWE in boot.ini.

ERP and CRM Application is so big that it is impossible for us to tune all the queries.

We have scheduled the reindexing once in 2 weeks.The database size is +600 GB.

But i think there is a CPU bottle neck.but i dont know how to check that there is a CPU Bottle neck.

The client is using Microsoft Navision Axapta 3.0 SP4

from

Killer

Vik Nat Hall of Fame Points: 3930 More actions · Answer 7

if you have set up AWE correctly - then its unlikely a memory issue.

You said you have 4CPU - Do they support hyper threading? You might consider turning hyper threading off in the bios and see if that helps. Studies have shown With SQL Server installations, HT-enabled motherboards show markedly degraded performance under heavy load.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 8

Hi Vik,

I will check for HT and let u know .

from

Killer

raj-247939 SSCertifiable Points: 5999 More actions · Answer 9

Hi ,

HTT is set to OFF.

Pls help in this regard.

from

Killer

Jonathan Hoare Newbie Points: 1 More actions · Answer 10

Hi Killer,

Were running Navision 3.60

I would look at locking and see if your getting deadlocking.

If your database is very transactional SIFT indexing could be your problem.

Try to identify particular areas that are causing a problem Finance reports and also investigate timings of inbound/outbound connections and how long they take.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 11

Hi,

We have few deadlocks but they are normal per navision documentation.

i am trying to looking in other areas and let u all know.

from

Killer