SQL08: affinity_mask, IO_affinity_mask and like riding two on a single server environments without being "walked"

We put ourselves in position
In our environment we may need to have two replicas of a / s data base distinct environments (the classic example would be production and test). In deciding as we do the most common questions we must ask ourselves are:

- Is this new environment will be temporary? Does large data bases in terms of volume and / or load to be borne is high (even test)?
- Is there version SqlServer2008 development? That alone is at hand if you have an MSDN subscription ...
- Is there an extra server?

Based on these questions and all that it can happen one can opt for different solutions:
"The easiest and if the database to bear the burden are small we can use the same server for all databases (created on the same server with different names (_test) and Holy Easter ...). In order not to disturb each other we can use Resource Governor.
"The most" aseptic "if resources permit and where it would be worthwhile to mount on different servers (if we have the development version)
"Another option is a mixture of the above. Fit the two environments on the same server but different instances.
-Etc ...

1 server cpu n (n> 1) + 2 = 2 instances environments
One option that I like about the above is the third, where we mounted two instances to separate the two environments and we set the processor affinity to control the dedication of each processor to each instance. We must also control the memory assigned to each instance (server memory and max server memory).

Example:
In a 6-core dedicated server 2 of the four processors to service the test environment while the remaining 6 were awarded the production environment. To do so we just have to open the SSMS and the Server Properties: XXXX in the part of processors each processor to enable manually (by unchecking the automatic award). View image.

It's good to know also that we can allocate and deallocate the convenience since you can vary dynamically for each instance. If necessary change is good if the carrying capacity is upon us. But all is not gold that glitters, and we know that to be managing two instances we are already consuming more than if gestionáramos only one.

Concepts: affinity mask, affinity io mask

Up
0