3

SQL Server 2012. AlwaysOn Cluster with two nodes, each has 4 cores

We are consolidating SQL server instances and moving bunch of databases from various locations to a single cluster which has two instances, 4 cores each. There are "more" and "less" important databases which are going to be mixed at the same location. Is there a way to make queries from a database to use only certain core on the host?

Example:

We have 10 databases named A1-A10. Can we make queries from 6 "less" important databases A1-A6 to use only one core (always the same core 0) , and leave queries from other 4 "more" important databases A7-A10 to use rest of the cores (in this case 1-3).

Thanks.

1

Is there a way to make queries from a database to use only certain core on the host?

One way is to use query hint OPTION (MAXDOP 1).

The problem with above apporach is that you have to hint everything that you don’t want to be limited by the server-wide setting, or use 0 (i.e. unlimited) for the server-wide setting and hint everything that you do want to be limited.

Can we make queries from 6 "less" important databases A1-A6 to use only one core (always the same core 0) , and leave queries from other 4 "more" important databases A7-A10 to use rest of the cores (in this case 1-3).

A better way is to use Resoruce Governor. It is available only in Enterprise Edition (Dev edition as well).

Note: Make sure you do proper testing before implementing directly in PROD as I have seen really bad cases where without understanding, Resource Governor is implemented and things go horribly wrong.

If you are running on Enterprise Edition then you can create

  • CREATE RESOURCE POOL -- here you can specify min and max CPU allowance. Also by using AFFINITY SCHEDULER, you can bind specific schedulers to your Pool.
  • CREATE WORKLOAD GROUP USING RESOURCE POOL. -- Here you can specify max_dop=1. This MAXDOP setting overrides the server-wide setting and any query hints.

Refer to : Resource Governor in SQL Server 2012 whitepaper for more details.

Aaron Bertrand has explained very nicely at : SQL Server 2012 Resource Governor Enhancement

  • You could also create two separate instances, one instance could then be limited to operate on a single CPU, the other instance on the other 3 CPUs, with the databases separated by instance accordingly – Max Vernon Aug 7 '14 at 20:55
  • Good point @MaxVernon .. Agree with you if that is Feasible for OP. Also, it will add licensing cost as well. – Kin Shah Aug 7 '14 at 20:59
1

You can do this with Soft-NUMA. Here's a Perfmon screen shot from a 4-CPU VM I set up to test using your settings, 2 soft NUMA nodes, 1 CPU to Node 0, 3 CPUs to Node 1. I'm running a CPU intensive query for 20 seconds on node 0, and then the same query on node 2. You can see the CPU activity swap over:

enter image description here

I'm running the query via sqlcmd and connecting using the tcp ports I specified, eg

REM Run CPU intensive query with 20 second timeout
sqlcmd -S WIN-xxxx,2001 -ic:\temp\temp.sql -dtempdb -t 20
sqlcmd -S WIN-xxxx,2002 -ic:\temp\temp.sql -dtempdb -t 20

The respective CPU masks would be 0x01 (or 1) for Node 0, and 0x0e (or 14) for Node 1. I followed the registry instructions from here and port mapping from here. I did not need to use ALTER SERVER CONFIGURATION. Jonathan Kehayias' article was also invaluable reading.

A powerful technique in SQL Server consolidation.

HTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.