Affinity mask VS Resource Governor

Question

Post reply

Affinity mask VS Resource Governor

FIJITechnologies

Old Hand

Points: 349
More actions
December 7, 2011 at 12:56 pm

#249229

Hi,
I'm working on the integration of a software which has it's own DB, uses a Sharepoint DB and a reporting DB. The plan is to isolate these databases in separate instances to prevent one service eating all of the resources. I planned to use the affinity mask and max memory to achieve this. But since I use SQL 2008 R2, I would also have the option to use the resource governor to achieve the same results. As a note, the Windows 2008R2 production server is running as a VM with dedicated CPUs and memory.
I tried to find some advices on using one option more than the other but found nothing that could convince me. The main arguments would be:
1. Advantage of using the resource governor is to allow sharing of resource when not needed by the other workloads. Simplifies maintenance since we have only one instance.
2. The affinity mask option will be removed in future version of SQL Server !! (reference: http://msdn.microsoft.com/en-us/library/ms187104.aspx)
3. Less configuration with the affinity mask option.
Can you give me your thoughts? Did you try the resource governor in a production environment? Does it perform as expected?
Thanks,
Jean-Michel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

FIJITechnologies Old Hand Points: 349 More actions · Answer 1

Seems that the resource governor doesn't have a lot of adepts yet.

Any thoughts anyone?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If the affinity mask is deprecated, then I wouldn't go anywhere near it. You don't want to have to devise your solution all over again when it's eventually removed.

John

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

I don't know a lot of people using Resource Governor, but they have been successful. It is a little more complicated to setup and understand, but it does do a good job of limiting CPU only when there's contention.

It's not perfect, so long running queries don't "lose" CPU if contention increases while they're running, but as you get busy, it does help throttle down the CPU for the different pools

sqlblogi SSC-Addicted Points: 420 More actions · Answer 4

I use it and works like a champ. sql 2012 is actually improving on it with more options. It only governs when contention is happening on that scheduler, so keep that in mind.