SQL CPU utilization issue

Hello, We are currently running a live Axapta implementation running Axapta 3.0sp3 on a HP Proliant Dual Xeon 2.8’s w/2 drives for the OS (raid1) and 3 drives for data (raid5). The box is on win2K w/SQL 2000. We have been experiencing sporadic performance issues. I have looked at the CPU, I/O, Network utilization, etc… Today we noticed that at some point in the morning the CPU (taskman) was up at an average of 25% or so…it would jump to as high as 75% and go down to 0%…but it was averaging about 25%. This is not the norm. My best guess is that some of the code in Axapta has started a process which is continuing to run. We are a 20user shop…and we went around to see if anyone was doing anything special…which wasn’t the case. How do I find out which process is creating CPU utilization from within SQL 2000? ie/where do i go to look and see (from WITHIN axapta or sql) which process is causing the heavy CPU use…and why it isn’t stopping. This have been going on for 4 hours or so…and I’m sure will continue until end of business. Thanks for any help you can provide. Irfan Qureshi TEC irf@shaw.ca

I don’t know anything about Axapta and SQL Server, but for Navision they tell us not to use RAID 5. Also, you want your transaction log to be on a separate raid array, that will speed things up. What’s wrong with 25% CPU? Sounds to me like it’s peachy :).

Hi Irfan, On RAID - Recommendation is to use 0+1. Though RAID 5 may seem cost efficient in the short run, but over the time is more expensive and less reliable and of course slower. But coming to your problem - I would recommend to straight away upgrade your SQL SP3 to SQL SP3a or SP4. BTW, just few queries - a) Is it 2-tier or 3-tier that you are running? b) Also apart from your obsevations on CPU, are the users actually complaining of slow resonse? And if so, could you let us know the details please Regards, Harish Mohanbabu

Hi Harish, thanks for the post…sorry it took so long for me to re-appear. Here is the issue we are facing… Every few days…users on our ‘sales’ screen will complain of the system being VERY slow…so we look at the server…ps/we are running a 2x2.8XEON with 5.5GB of RAM in a Tier-2 config…the CPU utilization is normally under 10%. When users are complaining of the system being slow…there is something running that is pushing sqlserver.exe up to 25-50%. I have used sp_who active to try and see what is running on SQL…but this doesn’t show me much… I need a way to determine WHY sql 2000 (enterprise) is using up 25%+ CPU power and what is causing this to happen. Our coder can’t see any issues from his end… I need to solve this issue as it impacts users heavily every 3 or 4 days … and a mid-day re-boot isn’t a great solution…it does solve the issue…but I want this thing to be able to run day in and day out… I really need help on this one…any input you can give would be greatly appreciated… Thanks, Irfan Qureshi irf@shaw.ca

Hi Irfan, Try to downloads this tools http://www.quest.com/spotlight_on_sql_server/ and Install on your server for analyze what is the root cause of this problem. And I have important information to tell you Almost execute code of Axapta can’t utilize multi CPU (MS SQL Also.!) (May be they not know how to write multithread code by X++ [:)] I’m kidding) you can see on your task manager when you run MP or Forcast or any code that load your CPU axapta use only 1 CPU [:(!] How to utilize remaining CPU [?] You still have remaining logical 3 CPUs(Physical 2 but your CPU is base on HT Technology) you can use it by let your user to run heavy load task on your server by terminal service but not over 2 connection because we should leave 1 CPU for system If root cause of this problem is Disk you can improve it by 1.Change raid system to raid 0 or 0+1 2.Separate Disk to 3 Raid Channel 1. Raid 1 for System 2. Raid 0 or 0+1 for Data 3. Raid 0 or 0+1 for Log and move your data and log to new raid and set your virtual memory to Raid 0 or 0+1 for Log If you have more budget you can add more raid and separate log file to new raid. Hope you will lucky Anucha (From Thailand) *Sorry for my poor english due to I’m alway sleep in the class when english teacher is comming [8D] Is your server model is “ML370 G3”?

quote:

Hi Irfan, Try to downloads this tools http://www.quest.com/spotlight_on_sql_server/ and Install on your server for analyze what is the root cause of this problem. I have loaded this on my backup server and have been looking at the performance indicators. And I have important information to tell you Almost execute code of Axapta can’t utilize multi CPU (MS SQL Also.!) (May be they not know how to write multithread code by X++ [:)] I’m kidding) you can see on your task manager when you run MP or Forcast or any code that load your CPU axapta use only 1 CPU [:(!] How to utilize remaining CPU [?] You still have remaining logical 3 CPUs(Physical 2 but your CPU is base on HT Technology) you can use it by let your user to run heavy load task on your server by terminal service but not over 2 connection because we should leave 1 CPU for system If root cause of this problem is Disk you can improve it by 1.Change raid system to raid 0 or 0+1 2.Separate Disk to 3 Raid Channel 1. Raid 1 for System 2. Raid 0 or 0+1 for Data 3. Raid 0 or 0+1 for Log and move your data and log to new raid and set your virtual memory to Raid 0 or 0+1 for Log If you have more budget you can add more raid and separate log file to new raid. Hope you will lucky Anucha (From Thailand) *Sorry for my poor english due to I’m alway sleep in the class when english teacher is comming [8D] Is your server model is “ML370 G3”? Thanks for the input…and yes…my server is an HPML370 G3… irf
Originally posted by anucha - 2006 Feb 27 : 20:51:19