best practice for setting cpu on sql 2005 sp4

Posted on 2013-01-18
Medium Priority
Last Modified: 2013-01-23
my sql server is really slow
i have 5 instances on it
inside sql management studio i right click the properties then cpu settings of the instance that is the slowest and gives the most problems and i see cpu settings that can be configured.
i see processor affinity and io affinity
i only have cpu0 selcted for both affinity and io affinity
should i enable both cpus or
split instances over each cpu
the server is virtual and has 2 x cpus
any help to get this to run fastre would be appreciated
Question by:dougdog
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +3
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38792513
rather than modifying the CPU, you shall modify the max memory setting.
presuambly all those 5 instances are fighting for the ram.
how much RAM does your box have?

Author Comment

ID: 38792528
its got 6 gig
but its the cpu that is maxing out at 100%cpu
in task manger
the process sqlserver.exe is 100% cpu quite a lot
there are 2 cpus in box
and the problem instance is set to use just cpu0 and thats the cpu that seems to max out more often
thinbk its because of some jobs running under the sql agent jobs

Expert Comment

ID: 38792536
my question is why 5 instances ?

if you could reduce istances number to just one (or 2) with different users that can access to specific databases, RAM and CPU will be used better.
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 668 total points
ID: 38792537
>its got 6 gig
then please start to put 1GB (1024MB) as max memory for each instance to start with

after that, please tune your queries. use the query profiler to see which queries are taking most CPU (use a filter with CPU > 100), take the queries, tune them, and quickly see the CPU usage go down.

chaning the CPU affinity will help close to nothing compared to that effort, and I have done this a couple of times on servers.
after my intervention on the servers, after +- 2-4 hours, by adding only a couple of indexes in most cases, CPU went down to <5% ...

Author Comment

ID: 38792550
i have disabled some instances that are not used much so i only have 3 on it now
what should i do
should i tack both processor affinity and io affinity for both cpus?
im not really a sql person so not sure how to tune querries
is this complicated
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38792556
>im not really a sql person so not sure how to tune querries
then you are indeed not the right person to solve this issue.
as I said, playing with the CPU affinity will help close to nothing if CPU usage is 100%.

find someone to get on-side (or remotely) to use the query profile to find the "bad" queries running and add missing indexes

Expert Comment

ID: 38792561
follow angelIII perfect analysis
from sql management studio right click on instance then properties and then memory

Author Comment

ID: 38792563
is there nothing i can do in meantime

Author Comment

ID: 38792564
what options should be ticked under cpu settings

Expert Comment

ID: 38792569
disabling 2 instances did not reduce the cpu usage ?

Assisted Solution

deiaccord earned 668 total points
ID: 38792626
angelIII is right that tuning the SQL queries and creating indexes is the best way to deal with high CPU usage.

By selecting both cpu's on your troubled instance you will allow it to use both, so queries on this instance will have more resources/run faster but this will negatively affect the other instances using the other cpu. If the above tuning is done (it should be) or you have no other choise you may need to do this to try and improve performance

I'd also query why there are 5 instances on a server with only 2 CPU cores? If everything else is done you may still have a situation where there is not enough processing power available currently for the load you are expecting from the server (so you might need to allocate more cores to the virtual server)
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 664 total points
ID: 38792652
on the server: open task manager and check what is taking CPU: it can be your Antivirus or not sql server process.
did it just start or it always was the issue? if "just" - what changed?
also check your server even viewer logs for related errors\warnings
find sysadmin to review the server box health setup; hardware including NIC; disks(speed);firmware, etc.
verify if db maintenance task ran: if not you need to run: check db; reindex; update stats...

BTW: can you post details about your OS/service pack; 5 sql servers/service pack ; how many drives; raid level etc? what kind virtualization was set: on somebody PC or ESX: in any case check host health..


SQL Server 2005 Best Practices Analyzer

Using the Microsoft SQL Server 2005 Best Practices Analyzer

SQL Server 2005/2008/2012 Setup Checklist

Author Comment

ID: 38792972
no its def the sqlserver instance i have narrowed it down to a specific job that runs under sql agent jobs for the problem instance
when i disable the sql agent and the job the server is fine
its when this job is running the problems start
the dev team insist that there is nothing wrong with this job and its the server is just not high enough spec
but i think its a problem with this job or coding but cant prove it
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38792989
what is that "job" doing? you can check the steps and tell us what it does?
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38793858
I think your set up is inadequate: this is likely to perform badly no matter what tuning you do, except for very small loads.

You're using a virtual server (not good for performance) with only 2 cpus (not at all enough here) with only 6 gig RAM divided among 3+ instances.  

It's like trying to haul a full truck load with a volkswagen: it probably ain't gonna happen, and it will be slow as heck while it tries.
LVL 43

Expert Comment

by:Eugene Z
ID: 38793962
<the dev team insist that there is nothing wrong with this job and its the server is just not high enough spec>

you have the answer: disable the job; ask dev team what sql server they need and why and ask them to submit request to buy\build a new server

Author Comment

ID: 38800308
its a virtual server
i can easily up the spec
however the os is 2003 enterprise 32 bit so limited to 4gig on ram
LVL 43

Expert Comment

by:Eugene Z
ID: 38801056
<however the os is 2003 enterprise 32 bit so limited to 4gig on ram >

please check below info and see what can be done for your systems

Physical Memory Limits: Windows Server 2003

The following table specifies the limits on physical memory for Windows Server 2003. Limits over 4 GB for 32-bit Windows assume that PAE is enabled.

Version  Windows Server 2003, Enterprise Edition

Limit on X86  64 GB(16 GB with 4GT)

Limit on IA64  512 GB



Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question