I have a Server that hosts 2 instances of SQL Server 2005 SP4 on Windows 2003 R2 SP2.
It is a 32 bit physical machine with 16GB of RAM and 16 processors.
The first instance (the default instance) is for a ERP solution. It should be considered the "primary" instance that is given the most resources. It is mostly OLTP with mainly short transactions. There's some batch processing but not as much as the OLTP component.
The second instance is a named instance an supports another application that presents an alternate front end to the ERP solution. It pulls all sorts of data from the ERP database daily, and there seems to be all sorts of read activity on this database the rest of the day (this comes from another vendor and we don't know too much about the internals of how this app works).
The secondary app is CPU intensive as well.
In order to separate out the resources on the box and try to partition out the secondary database as much as possible, affinity masking was set up for the named instance (this was setup 3 or 4 years ago, but recent calls about performance have me researching this more now). This instance is limited to 4 processors. It also runs in SQL Server 2000 compatibility mode (the version of the app on this one is a real dinosaur).
The default instance is currently set up to automatically set CPU and I/O Affinity masking for all processors. SQL Server priority boosting is turned on (and based upon research today will be turned off during our maintenance window this weekend.
The secondary instance has both CPU and I/O affinity masks set for CPU's 12-15 (leaving 0-11 for everything else).
For a start, from what I can see, Affinity masking hard binds schedulers to specific CPU's. However there's a startup trace flag 8002 that seems like it might address part of this by letting the schedulers reassign load among the processors defined in a affinity mask.
The other thing that seems to be bad is that we have both I/O and CPU checked for the 4 CPU's (which seems to be a bad thing because you then have I/O and CPU contention in the affinity group).
What I'm looking for here, then, is advice on how to configure this secondary instance so that it can't hog all the CPU's on the server (because it is a CPU pig) without changing the code (it's a third party product). Traceflag 8002 seems like it might be helpful, but with the 4 processors I am allowed to spread out over this instance, how should I assign the CPU's ? 3 for CPU and 1 for I/O ? 2 and 2 ?
The docs say that if you're using CPU affinity you also need to use I/O affinity and just make sure that for each CPU, they are both off, or one on and one off. I was thinking of trying to set up individual CPU's for processing and auto assigning I/O for the instance but that seems to fly in the face of the docs.
Or am I reading that wrong and I can set up just CPU masking for CPU load and leave I/O affinity as system defined for both ? The presence of two check boxes (auto allocate CPU and auto allocate I/O) seems to indicate that this may be OK.
Also, is affinity masking a "hot" change (taking affect as soon as the setting is made) or does it require a restart ? The docs (link below) seem to reference that a restart is required.
References:
Affinity Mask option:
http://technet.microsoft.com/en-us/library/ms187104%28v=sql.90%29.aspx
Affinity I/O Mask option:
http://technet.microsoft.com/en-us/library/ms189629%28v=sql.90%29.aspx
Trace Flag 8002:
http://support.microsoft.com/kb/818769
My gut feel here is to do the following (but I lack experience in this area to confirm that it is the right solution and I only get one maintenance window a month):
Default instance: Turn off Priority Boost, leave both CPU and IO set to auto affinity
Named instance: Set I/O to auto affinity, leave CPU set for CPU's 12-15, set startup flag 8002
(Suggestions to tune code, modify indexes etc aren't the answer here. Both applications are pre packaged from third party vendors and changing code or database structures is just not an option).
Any assistance here is greatly appreciated.
if you have no option to get more CPU\RAM\ new box with more resources..
you can try any combination.
consider to do all changes off hours..
<In Microsoft SQL Server 2005, the affinity mask option can be configured without requiring a restart of the SQL Server instance>
----
but before of doing these "not regular in DBA world" changes: normally default "auto"
is good- but depends of situation there can be steps to adjust..
please clarify what is your sql server 2005 edition
how often do you run db maints?
what is the exact issue ?
check the max\min memory settings
and adjust as needed to give OS at least 2GB RAM
check max degree of parallelism
you may need to switch it of on both servers
max degree of parallelism Option
http://technet.microsoft.com/en-us/library/ms181007(v=sql.90).aspx