MS SQL workload at 300%??

At work, we currently having problems with one of our SQL servers.

Our problem is that it can  take anywhere up to 12 hours just to crunch some data overnight.
There are no other loads on the system apart from this one module of code, but for some reason it it using up most of the servers processing power to complete it, and takes several hours to do it.

Based on stats from a program we use to monitor the server, (the program we use is up.time 5)
it shows the server workload spiking  well over 100%.  There are times it goes up to 300%
The pagefile drops from 60% to 40%, this happens about the same time,
Processor usage spikes up to 100% very regularly

If you can help, its much appreciated :)


can post more data if you need it

David Sheehan-DunneIT TechnicianAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ;)

David Sheehan-DunneIT TechnicianAuthor Commented:
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

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Is this a 32 bit system. If yes then see sql server gets more memory using 3gb switch. Also 4GB seems to be low memory for SQL server.

Looks like we do have memory pressure here.. check your indexes, check the slow running queries using DMV

Check the query plan and see if you have lots of table scan or index scans.

Also what data volumes are we talking about.. what are your table sizes.. I think if this is prod machine 4GB seems to be less memory..

I wanted to knw page life expectancy counter. Can u get that.?


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
For testing performance, I suggest running SQL Profiler, looking for queries that have high reads or writes, or have high CPU.  You can set filters when running SQL Profiler so you only see the high cost transactions.  As above,  f most of your processing is done inside procedures, make sure you turn on the additional "SQL:StmtCompleted".

Are you  running optimizations and integrity check jobs?  You should be running these at least once per week.
David Sheehan-DunneIT TechnicianAuthor Commented:

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?


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.
David Sheehan-DunneIT TechnicianAuthor Commented:
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 :)

David Sheehan-DunneIT TechnicianAuthor Commented:
Thanks for all your tips guys, will implement now, and see what results I get back
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.