sql job runs for 100+ hours, but hardware does not seem to be taxed...

First of all a quick disclosure, I am NOT a DBA, I can barely spell SQL, I am the administrator of the windows box. Our DBA's are not local so I am working with them trying to learn where the bottleneck on this job is to shrink the time it takes to complete. This is a freshly rebuilt and installed server, all drivers, OS and SQL server are fully patched to current.
Hardware is a HP ML580G2 4x2.7 Xeon CPU 10GB RAM Disk 4x300GB u320scsi 10K drives in a single RAID10 array, on a single logical drive.
OS is W2K3 SP2 Enterprise Edition, boot.ini has /3gb and /pae and under system properties/general, it lists 9.5GB and Physical address extension
We have SQL server 2000 sp4 and it looks to be very default other than I used query analyser to toggle AWE from 0 to 1.
The job is running right now and I am RDP to the server and watching perfmon and also Windows Task Manager.
I see a few things that I am questioning.
In windows Task Manager I see one CPU running maxed, the 7 others completely idle. every once in a wile there is a slight momentary drop acompanied by a similar rise on another CPU. Since enterprise manager appears to show that all CPU are available to it, I am assuming that this is because SQL server has determined that spreading the load to more CPU will not help. Does this sound right, or likely? The server was rebooted and the job started about 17.5 hours ago and cputime for sqlserver.exe is 17:35 this seems to indicate that this behaviour (maxing one, ignoring the others) is consistant. Network utililization is near zero since this job runs on local data. Windows task manager shows sqlservr.exe mem usage to be ~90 meg, I assume this is a lie because of our using AWE. Does this sound right?
Suggestions from this site had me use permon and the following counters
Memory-Pages/Sec ==> almost all zeros, average is 3.5
NIC Bytes Total/sec ==> average 2,780
Physical Disk %disk Time ==> average 0.05
Physical Disk current disk queue length ==> average 0.0
System Processor queue length ==> average 0
Processor % processor time ==> average 12.5%
SQLserver:Gen Stats User connections ==> average 7
SQLServer:access methods Page splits/sec ==> average 0.004
SQLServer:Buffer Manager Buffer cache hit ratio ==> average 99.870
SQLServer:Memory Manager Target Server Memory ==> average 4946520
SQLServer:Memory Manager Total Server Memory ==> average 4946520
SQLServer:Buffer Manager AWE lookup maps/sec ==> average 0.000
SQLServer:Buffer Manager AWE write maps/sec ==> average 0.000
Al resources but a single CPU seem barely touched. Network is near zero, all other CPU's ignored, RAM use is under 5GB, so it has not even been all claimed by SQL, the disks are in danger of falling asleep .... what gives? If you need more info on what SQL activity is occuring at this time, I can get it from a DBA
Thanks in advance.

Good you have take pain in posting all details, but can you also elborate what kind of job are your running ?
Cheers
Sat

I would alos recommend to discuss with your DBA the following post, some tuning tips are there mentioned
Shorter versions
1. Does your job have queries utilizing cost based optimizer or parralel plans ?
2. There is configuration discussed in post about boosting SQL priority which had helped in certain cases not sure if that fits your case. It may not help if SQL is only application on the server.
http://sqljunkies.com/Forums/ShowPost.aspx?PostID=1431
Regards,
Sat

I am the fellow that inherited this job that this post is about (also not a DBA), so I'll try to provide some information about the job.
The job is a DTS package (32 steps) that runs 24-36 hours on a production grade server (Database A on Server 1) that creates fact records (about a million per run in fact table, 25K in key table) in our data store (fact table contains 50+ million records, key table 1+ million).
The steps that runs 100+ hours in our UAT environment calls a Stored Procedure that loads 22 facts from four sources (iterates through 2 facts from Database B on linkserver Server 2, 1 fact from Database C on linkserver Server 2, 1 fact from a flat file loaded into Database A on Server 1 in a prior step).
One of the OPENQUERY insert statements on Database B linkserver Server 2 also JOIN's out to Database D on linkserver Server 3)

Typically there in no other activity major activity on any of these Servers while this process in running.
Without a doubt there is likely some SQL changes that could be made to improve the process (and they will be made) but I'm still interested in tuning the servers to get the most out of them.

Please let me know what other information I can provide to clarify.

Using OPENQUERY in itself is often a performance bottleneck, but JOINing two of them is asking for trouble. I would suggest creating temp tables with indexes, inserting into those with the same OPENQUERY method, then JOINing the temp tables.

I agree about the temp tables, given time that is something I am moving toward.