Solved

best practice for setting cpu on sql 2005 sp4

Posted on 2013-01-18
18
538 Views
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
0
Comment
Question by:dougdog
  • 6
  • 4
  • 3
  • +3
18 Comments
 
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?
0
 

Author Comment

by:dougdog
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
0
 
LVL 9

Expert Comment

by:sognoct
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 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% ...
0
 

Author Comment

by:dougdog
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
0
 
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
0
 
LVL 9

Expert Comment

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

Author Comment

by:dougdog
ID: 38792563
is there nothing i can do in meantime
0
 

Author Comment

by:dougdog
ID: 38792564
what options should be ticked under cpu settings
0
 
LVL 9

Expert Comment

by:sognoct
ID: 38792569
disabling 2 instances did not reduce the cpu usage ?
0
 
LVL 6

Assisted Solution

by:deiaccord
deiaccord earned 167 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)
0
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ earned 166 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..

also
check/try:

SQL Server 2005 Best Practices Analyzer
http://www.microsoft.com/en-us/download/details.aspx?id=23864

Using the Microsoft SQL Server 2005 Best Practices Analyzer
http://www.mssqltips.com/sqlservertip/1972/using-the-microsoft-sql-server-2005-best-practices-analyzer/


SQL Server 2005/2008/2012 Setup Checklist
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/
0
 

Author Comment

by:dougdog
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
0
 
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?
0
 
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.
0
 
LVL 42

Expert Comment

by:EugeneZ
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
0
 

Author Comment

by:dougdog
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
0
 
LVL 42

Expert Comment

by:EugeneZ
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

more:


http://technet.microsoft.com/en-us/library/cc783072(v=ws.10).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx#physical_memory_limits_windows_server_2003
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Copy database from one server to another 3 30
TSQL Challenge... 7 35
Location of Dynamics AX Service accounts in SQL 3 15
Query for timesheet application 3 17
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

839 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