How to get the Affinity data for Resouce Pool in SQL Server 2012?

Question

Create 2 resource pool:

create resource pool rscp01
with(
 min_cpu_percent=10,
 max_cpu_percent=20,
 cap_cpu_percent=30,
 affinity scheduler=(0,1),
 max_memory_percent=80,
 min_memory_percent=20);

create resource pool rscp02
with(
 min_cpu_percent=10,
 max_cpu_percent=20,
 cap_cpu_percent=30,
 affinity NUMANode=(0,1),
 max_memory_percent=80,
 min_memory_percent=20);

VM win7, no hardware NUMA, use soft NUMA, error log shows like:

2012-08-02 00:17:46.52 Server      SQL Server detected 2 sockets with 1 cores per socket and 1 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

...

2012-08-02 00:17:48.01 Server      Node configuration: node 0: CPU mask: 0x00000001:0 Active CPU mask: 0x00000001:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-08-02 00:17:48.01 Server      Node configuration: node 1: CPU mask: 0x00000002:0 Active CPU mask: 0x00000002:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

Run query:

select * from sys.resource_governor_resource_pool_affinity;

Result:

pool_id  processor_group  scheduler_mask
258        0                          3
259        0                          3

Questions:

1. Is scheduler_mask is the Affinity value? Is there other view has text Affinity value like "(0,1)"?

2. How can we know which option (scheduler or NUMA) is used when define the Resouce Pool?

Answer 1

Hi Sun Shine1 N,

You can try to script out CAP_CPU_PERCENT and Resource Pool Scheduler affinity once you apply SQL Server 2012 SP1.
Could you verify the edition of SQL Server 2012? The Resource Governor feature supports Enterprise edition. For more information, please refer to
1. Features Supported by the Editions of SQL Server 2012

Regarding to the error message you provided, might be related to the 2012 licensing change issue.
Please follow the steps run msinfo32 from a command prompt and verify what your system does have. You will find the count of Physical Processors, Physical Cores, and Logical cores.
select * from sys.dm_os_schedulers
To verify you have an EE Core edition (This is the Core limiting Edition)
select serverproperty('Edition')
If it returns the following, you have a license enforced core limit on your SQL Server.

If still error you have the same, here is how to fix it once you get the correct license key.
Command Line
Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=MSSQLSERVER /PID=<PID key for new edition>" /IACCEPTSQLSERVERLICENSETERMS

If you would like to know more, Juergen Thomas has a very in depth blog covering all of the ins and outs of this. If you have the time I highly encourage you to read it.

---

Regards, Amber zhang

Answer 2

Hi Amber,

Thanks the information provided.

I am trying to query the system table to know resource pool defination like what cap_cpu_percent values is, what affinity option used-"Scheduler" or "NUMANode" and what the affinity values are.

I can create the resource pool succesfully. But how can I get the "affinity NUMANode" value? Is it the scheduler_mask in sys.resource_governor_resource_pool_affinity? If yes, how can I tell it is the "scheduler" or the "NUMANode"? Or the SP1's system table will have such data?

Thanks,