MS SQL workload at 300%??

More information about the job is probably needed - but a good place to start would be to run the "Display Estimated Execution Plan" to determine where your slow spots are.

Check the code in the functions / modules to see if there are ways you can optimize it.  You should run a trace and look for slow commands / queries - especially look at those with a high CPU cost.  If most of your processing is done inside procedures, make sure you turn on the additional "SQL:StmtCompleted".

Check especially for missing indexes.  You could consider changing some indexes to Clustered to speed access, though this could require you to rebuild your primary keys, which default to clustered when you build them.

This cannot be answered without more details.

Please share the job details..

Also check out following..

1. Reads happening per second
2. write happening per second
3. Sql server memory
4. total memory on server
5. Free memory on server when job is running.
6.  Average page life expentancy.

you can get above using perfmon.

and last but not the least please be generous about points ;)

-Naval

An update:

There are two jobs that happen over night, one of them is the backup, the second one is used to process another program

Our main program, payroll, which is run fortnightly, has further problems, but I don't have access to that data yet, sorry

Server details:

Total server memory: 4GB.
Memory spare during processing of job: 512MB
Pagefile size flicks between 45% - 50% .

when you say read / writes per second is that page read / writes? if so the following information is below
reads per second:
writes per second: is 0 for the entire duration when the software is running

processor is dual core and runs at about 40% but occasionally spikes up to 80%

Also I have only just noticed that the page faults per second is extremely high, could this be down to a poorly written piece of software, or the server not having enough juice to process the software module?

My apologies about the details being a bit vague, I have just recently started working at this company and still learning the ropes,

And this is my first time on this site, so apologies about the points, just increased the points value

Dave

Hi,

My apologies about the delay in getting back, the operating system is 32-bit, but we are considering upgrading the SQL server very soon to Windows 2008 version, with high specification hardware.

The database system was built by an outside company, supposedly to make the job quicker and faster, but on looking at the database structure and there are literally hundreds of tables inside the database,  Most of them seem to be replicated.  Went through a few tables, these seemed to be empty and holding no data what so ever.  

There is also a huge list of functions / SQL queries when we went to view the functions list.  The table sizes I am not sure about as we have so many, it would take a long time to go through them.

We are currently setting up the profiler so that it can catch the high cost SQL queries, but since this program runs every fortnight, the next time it runs wont be until a week from now to get up to date information.

@kenwagers  I will start looking into doing the optimizations and integrity check jobs once i get a bit more information my self on the servers and the jobs they do, what programs would you recommend to help with this, or does SQL server come with these functions built in?

regards

David

I would start with the built in tools.  It's likely the jobs that run are SSIS or DTS (older), and they were built within the tools.  You should be able to review what's included in the jobs (unless they were encrypted).

For a more advanced set of tools that I have found helpful, take a look at RedGate.

http://www.red-gate.com/

ok thanks for your help guys, i will close this down now, and if i do have any more trouble i will post it back up here,

thanks again for both your input to this :)

Dave

Thanks for all your tips guys, will implement now, and see what results I get back