 Change the number of running CPU's -
01-10-2010
, 03:46 AM
You have to change the number of running CPU's within your SQL configuration under SQL Server -> Properties -> Processors or with a code like below (Depending of your configuration)
EXEC sys.sp_configure N'affinity mask', N'7'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'7'
GO
RECONFIGURE WITH OVERRIDE
GO
Noel wrote:
Soft NUMA configuration problem
09-Aug-07
Hello,
I'm working on a SQL Server 2005 database (under Win 2003) which is used
for a web application (24/24) and on the other hand for asynchronous
works (20h -> 06h).
Sometime the asynchronous works consume all system resources and have
for consequence to slow the web application even unavailable.
So I want to install a soft-NUMA configuration in order to limit the CPU
access for the asynchronous works.
The test platform is based on two hardware NUMA nodes, each having 2
hyperthreading CPU (4 CPUs per node for Windows). The repartition of the
CPU on the two hardware NUMA node is :
- Node 0 : CPU 0, 1, 4 et 5
- Node 1 : CPU 2, 3, 6 et 7
Actually, I'm testing the following configutration on the test platform
that is discribed higher :
Configuration soft-NUMA (in the registry)
CPU 7 6 5 4 3 2 1 0
node 0 0 0 1 1 0 0 1 1
node 1 0 0 0 0 1 1 0 0
node 2 1 1 0 0 0 0 0 0
I respected the fact that this is not possible to create soft NUMA nodes
with CPU which are on differents hardware NUMA nodes.
In parallel, any CPU affnity is defined because I am on a single
database instance.
Then, on SQL Server, I defined an endpoint on the TCP port 2000 by
taking care well to give connction right on this endpoint.
At the end, I added in the SQL Server Configuration Manager the TCP port
2000 with a soft-NUMA mapping in order to create grouping. I obtained
the following configuration : 1433,2000[5].
With this configuration, normally, when I'm connected to the database by
the TCP port 1433, I have all the CPU. In the other hand, when I'm
connected by the TCP port 2000, I have just the CPUs of the NUMA nodes 0
and 2 (CPU 0,1,4,5,6 and 7)
However, and this is my problem, when I'm connected by the TCP port 2000
and that I execute a query which I know it consumes all CPU resources,
all CPU are used !
In order to verify, I changed the mapping in the TPC port like this :
1433[2],2000[5].
Normaly, with a connection by the TCP port 1433, I have just the CPU of
the NUMA node 1. However, all CPU are used by my query.
When I'm looking at the configuration which is in the error log, I don't
see anything strange :
Multinode configuration: node 0: CPU mask: 0x0000000c Active CPU mask:
0x0000000c. This message provides a description of the NUMA
configuration for this computer. This is an informational message only.
No user action is required.
Multinode configuration: node 1: CPU mask: 0x00000033 Active CPU mask:
0x00000033. This message provides a description of the NUMA
configuration for this computer. This is an informational message only.
No user action is required.
Multinode configuration: node 2: CPU mask: 0x000000c0 Active CPU mask:
0x000000c0. This message provides a description of the NUMA
configuration for this computer. This is an informational message only.
No user action is required.
Server is listening on [ x.x.x.208 <ipv4> 2000].
Server is listening on [ x.x.x.245 <ipv4> 2000].
Server is listening on [ x.x.x.132 <ipv4> 2000].
SQL Network Interfaces initialized listeners on node 2 of a multi-node
(NUMA) server configuration with node affinity mask 0x00000005. This is
an informational message only. No user action is required.
Server is listening on [ x.x.x.208 <ipv4> 1433].
Server is listening on [ x.x.x.245 <ipv4> 1433].
Server is listening on [ x.x.x.132 <ipv4> 1433].
SQL Network Interfaces initialized listeners on node 1 of a multi-node
(NUMA) server configuration with node affinity mask 0x00000002. This is
an informational message only. No user action is required.
Is someone know why, in spite of the NUMA configuration, all CPU are
used ?
Is there a configuration error ?
Is it necessary to define an affinity CPU in parallel of NUMA
configuration ? But how to make, because I work on only one database ?
Thanks in advance for your reply.
Previous Posts In This Thread:
On Thursday, August 09, 2007 9:02 AM
Noel wrote:
Soft NUMA configuration problem
Hello,
I'm working on a SQL Server 2005 database (under Win 2003) which is used
for a web application (24/24) and on the other hand for asynchronous
works (20h -> 06h).
Sometime the asynchronous works consume all system resources and have
for consequence to slow the web application even unavailable.
So I want to install a soft-NUMA configuration in order to limit the CPU
access for the asynchronous works.
The test platform is based on two hardware NUMA nodes, each having 2
hyperthreading CPU (4 CPUs per node for Windows). The repartition of the
CPU on the two hardware NUMA node is :
- Node 0 : CPU 0, 1, 4 et 5
- Node 1 : CPU 2, 3, 6 et 7
Actually, I'm testing the following configutration on the test platform
that is discribed higher :
Configuration soft-NUMA (in the registry)
CPU 7 6 5 4 3 2 1 0
node 0 0 0 1 1 0 0 1 1
node 1 0 0 0 0 1 1 0 0
node 2 1 1 0 0 0 0 0 0
I respected the fact that this is not possible to create soft NUMA nodes
with CPU which are on differents hardware NUMA nodes.
In parallel, any CPU affnity is defined because I am on a single
database instance.
Then, on SQL Server, I defined an endpoint on the TCP port 2000 by
taking care well to give connction right on this endpoint.
At the end, I added in the SQL Server Configuration Manager the TCP port
2000 with a soft-NUMA mapping in order to create grouping. I obtained
the following configuration : 1433,2000[5].
With this configuration, normally, when I'm connected to the database by
the TCP port 1433, I have all the CPU. In the other hand, when I'm
connected by the TCP port 2000, I have just the CPUs of the NUMA nodes 0
and 2 (CPU 0,1,4,5,6 and 7)
However, and this is my problem, when I'm connected by the TCP port 2000
and that I execute a query which I know it consumes all CPU resources,
all CPU are used !
In order to verify, I changed the mapping in the TPC port like this :
1433[2],2000[5].
Normaly, with a connection by the TCP port 1433, I have just the CPU of
the NUMA node 1. However, all CPU are used by my query.
When I'm looking at the configuration which is in the error log, I don't
see anything strange :
Multinode configuration: node 0: CPU mask: 0x0000000c Active CPU mask:
0x0000000c. This message provides a description of the NUMA
configuration for this computer. This is an informational message only.
No user action is required.
Multinode configuration: node 1: CPU mask: 0x00000033 Active CPU mask:
0x00000033. This message provides a description of the NUMA
configuration for this computer. This is an informational message only.
No user action is required.
Multinode configuration: node 2: CPU mask: 0x000000c0 Active CPU mask:
0x000000c0. This message provides a description of the NUMA
configuration for this computer. This is an informational message only.
No user action is required.
Server is listening on [ x.x.x.208 <ipv4> 2000].
Server is listening on [ x.x.x.245 <ipv4> 2000].
Server is listening on [ x.x.x.132 <ipv4> 2000].
SQL Network Interfaces initialized listeners on node 2 of a multi-node
(NUMA) server configuration with node affinity mask 0x00000005. This is
an informational message only. No user action is required.
Server is listening on [ x.x.x.208 <ipv4> 1433].
Server is listening on [ x.x.x.245 <ipv4> 1433].
Server is listening on [ x.x.x.132 <ipv4> 1433].
SQL Network Interfaces initialized listeners on node 1 of a multi-node
(NUMA) server configuration with node affinity mask 0x00000002. This is
an informational message only. No user action is required.
Is someone know why, in spite of the NUMA configuration, all CPU are
used ?
Is there a configuration error ?
Is it necessary to define an affinity CPU in parallel of NUMA
configuration ? But how to make, because I work on only one database ?
Thanks in advance for your reply.
On Sunday, January 10, 2010 4:38 AM
R G wrote:
Change your number of running CPU's
You also have to change the number of running CPU's within your SQL configuration. Under Server properties -> Processors or with a code like below.
EXEC sys.sp_configure N'affinity mask', N'7'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'7'
GO
RECONFIGURE WITH OVERRIDE
GO
On Sunday, January 10, 2010 4:41 AM
R G wrote:
Change your number of running CPU's
Yoou have to change the number of running CPU's within your SQL configuration under SQL Server -> Properties -> Processors or with a code like below (Depending of your configuration)
EXEC sys.sp_configure N'affinity mask', N'7'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'7'
GO
RECONFIGURE WITH OVERRIDE
GO
On Sunday, January 10, 2010 4:43 AM
R G wrote:
Change your number of running CPU's
You have to change the number of running CPU's within your SQL configuration under SQL Server -> Properties -> Processors or with a code like below (Depending of your configuration)
EXEC sys.sp_configure N'affinity mask', N'7'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'7'
GO
RECONFIGURE WITH OVERRIDE
GO
On Sunday, January 10, 2010 4:43 AM
R G wrote:
Change the number of running CPU's
You have to change the number of running CPU's within your SQL configuration under SQL Server -> Properties -> Processors or with a code like below (Depending of your configuration)
EXEC sys.sp_configure N'affinity mask', N'7'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'7'
GO
RECONFIGURE WITH OVERRIDE
GO
Submitted via EggHeadCafe - Software Developer Portal of Choice
A Low-Overhead SysLog Message Sender Class
http://www.eggheadcafe.com/tutorials...yslog-mes.aspx
 |
Linear Mode
