Solved

best practice for setting cpu on sql 2005 sp4

Posted on 2013-01-18
18
540 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Eugene Z
Eugene Z 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: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
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: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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

713 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