June 5, 2008 at 7:17 am
i am running one job on my local machine. after some time i can see that sqlservr is taking 100% cpu utilizatin as well as memory utilization is also very high.Due to which performance of the machine is down.
i have sql server 2000 with SP2 is being installed.
-Satya
June 5, 2008 at 7:25 am
You have to give many more specifications about the job: what it does, how long it runs, after what time the CPU starts to increase, etc. before we can give you advice.
But you can start looking at execution plans, proper use of indexes, CPU/Memory/Disk IO by using windows monitoring and possibly start a Profiler-trace to see what is happening.
June 5, 2008 at 7:34 am
All i can say that it is sudden changes came. one day before all working fine.
Basically its a xml job and run lots of SPs in behind. when i start the job, Immediately cpu is up.
June 5, 2008 at 8:01 am
If you can supply some table layouts and code samples, we might be able to help. What are the sprocs doing, selects/updates/deletes? Are your indexes optimized? How many is "and run lots of SPs in behind"? Can this job be scheduled to run during off-hours? You're not giving us much to work with - more information = more help...
And why are you only on sp2?? I know there were security patchs in subsequent service packs that were deemed critical (I believe sp3a but not positive)!
-- You can't be late until you show up.
June 5, 2008 at 1:50 pm
High CPU/memory usage can also be caused by massive full table scans.
June 5, 2008 at 1:55 pm
Since it seems to have recently started, how about looking at index fragmentation?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply