Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

best practice for setting cpu on sql 2005 sp4

Posted on 2013-01-18
18
Medium Priority
?
547 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
[X]
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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Accepted Solution

by:
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% ...
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 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)
0
 
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..

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 70

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

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 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