Solved

best practice for setting cpu on sql 2005 sp4

Posted on 2013-01-18
18
534 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
Comment Utility
>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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
follow angelIII perfect analysis
from sql management studio right click on instance then properties and then memory
0
 

Author Comment

by:dougdog
Comment Utility
is there nothing i can do in meantime
0
 

Author Comment

by:dougdog
Comment Utility
what options should be ticked under cpu settings
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

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

Assisted Solution

by:deiaccord
deiaccord earned 167 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

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

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Comment Utility
<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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
Viewers will learn how the fundamental information of how to create a table.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now