Sql Server : CPU affinity mask or how to limit Sql Server to selected CPU (or processors or NUMA set)

Quick one today:

Recently, during troubleshooting an issue with one of our customers, some patterns emerged that indicated the possibility of resource contention for their repeated timeout issues. Upon further digging in, the CPU usage was abnormal. The database server was on a Virtual Machine with 8 processors allocated. The CPU usage was only on one processor. The rest were idle. 0% usage.

As part of that experience some lessons were gathered out of which today’s post emerges on CPU Affinity mask.

CPU Affinity Mask:

Sql Server uses all CPUs available through the Operating System. It creates schedulers on all the CPUs to make the best utilization of the resources for any given workload. Operating Systems, as part of its multitasking, it needs to switch process threads from processor to another. This is usually not detrimental for normal operations; But resource intensive operations like Sql Server this undercuts performance. Such Context Switching is not always a welcome scenario. To minimize we could configure the processors in a way that all the Sql Server load will be directed to a pre-selected group of processors. This is achieved using CPU Affinity Mask.

As you imagine, this is a HEX value. But we convert the HEX value into integer and configure Sql Server to lean towards (affinitize) a group of CPUs.

Let’s imagine a machine with 16 processors; When you assign all the processors to Sql Server and allow it to optimally utilize all the resources the settings look like this:

CPU Affinity Mask (default)

The options on the top convey that we are enabling Sql Server to fully manage the resource allocation with all the CPUs.

• Automatically set processor affinity mask for all processors
• Automatically set I/O affinity mask for all processors

I/O tasks like Lazywriter, CHECKPOINT, Log Writer, etc are directly affected by the dedicated CPUs allocated for I/O activities.

NUMA Noode0 and NUMA Node 1 are the CPU groups (either Hardware or Software configuration) to enhance hardware resource consumption. Each NUMA node has multiple CPUs with dedicated memory.

The ‘Processor Affinity’ Check box allows you to select individual CPUs, from each NUMA nodes, to SQL Server. Since the check box is not checked, none of the CPUs (from that NUMA node) have their affinity set (not allocated primarily for Sql Server).

Now, if you want to select all the processors from NUMA Node 0 to Sql Server, select that Check box. If you want to accomplish the same with T-SQL scripts like this:

--
-- Since these are advanced options, we first need to set 'Show Advanced Options' & RECONFIGURE
--
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

--
--  Integer value 15 translates to 0x000F (which shows that the last 4 bits are set to 1. Similarly 240 translates to 0x00F0
--
EXEC sp_configure 'affinity mask', 15
EXEC sp_configure 'affinity I/O mask', 240
GO
RECONFIGURE
GO

--
--  Once completed, reset the Advanced Options
--
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO

Affinity Mask for each processor could be set using a 1 or 0 bit flag. 1 signifies the affinity being set. So each processor has an affinity mask bit flag that controls its affinity.

Let’s says you want to allocate all the 8 CPUs in NUMA Node 0 to Sql Server; First 4 with CPU affinity and last 4 for I/O affinity. This are the steps to follow:

Imagine setting 1 (bit) for each allocated CPU; Then convert that value into INTEGER.

In Binary the result looks like this 00001111; It translates to 0X0F in HEX. Which is 15 in integer. This is the value you want to set for ‘affinity mask’.

Now, let’s try the same logic for I/O affinity mask. Let’s set the last 4 bits to 1.

So, 11110000 in binary translates to 0xF0 in HEX and 240 in integer.

Putting it all together it becomes:

EXEC sp_configure 'affinity mask', 15
EXEC sp_configure 'affinity I/O mask', 240
GO

After setting CPU Affinity Mask

Since Following is the table from MSDN that shows other example:

These are affinity mask values for an 8-CPU system.

Decimal value	Binary bit mask	Allow SQL Server threads on processors
1	00000001	0
3	00000011	0 and 1
7	00000111	0, 1, and 2
15	00001111	0, 1, 2, and 3
31	00011111	0, 1, 2, 3, and 4
63	00111111	0, 1, 2, 3, 4, and 5
127	01111111	0, 1, 2, 3, 4, 5, and 6
255	11111111	0, 1, 2, 3, 4, 5, 6, and 7

Some more points:

• One byte (8 bits) covers affinity mask for 8 CPUs. And two bytes for 16 CPUs, 3 bytes for 24; 4 for 32 CPUs.
• For more than 32 processors, you want to use affinity64 for another 32 processors.
• It is recommended not to use the same CPU for both CPU affinity and I/O affinity.
• Changing CPU affinity mask does not require restart.
• Any schedulers assigned to those CPUs will be shutdown once the current workload is completed. Any new workload, is assigned to new schedulers (or the assigned CPUs)

For more information read MSDN BoL article.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Convert from INTEGER (decimal) to HEX and HEX to INTEGER

Quick one today:

Sometimes there is a need to convert data from integer to HEX and from HEX to integer. There are ways to accomplish this and today, this post covers one such ways.

--
--	Conversation samples
--
SELECT	  CONVERT(INT, 0x00F0)		AS [Convert_HEX_to_INT]
        , CONVERT(VARBINARY(8), 15)	AS [Convert_INT_to_HEX]
GO

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : ORDER BY clause beauty and complexity (Tidbit 3)

A few days ago, there was an interesting requirement. We have a query that needs to bring data from a set of tables in a particular order that did not seem straight forward. After some head scratching and banging on the desk, with no avail, my colleague Dylan rescued the quickly dying hope.

Requirement

We have a query that returns necessary data set to be populated on the webpage. But the resultant data set needs to be broken into two logical groups. The first group, should only contain data where a particular column is NULL or empty. Second group has non-NULL data. First group is always displayed at the top of the screen followed by Group 2. But both the groups need to be ORDERed by a different column.

Let’s simplify it further. Entire data set, needs to be ORDERed BY two columns, let’s say, Col8. But all records will NULL value for Col2 needs to be on the top (still ordered by Col8).

So, it is not a simple ORDER BY Col2 ASC, Col8 ASC. As this would result in second group being ordered by Col2 first then Col8. This is incorrect. So we need a way to combine the data set into two groups, with NULL and without NULL; and then order them by Col9.

Let’s take sample data set.

--
-- Sample table to demonstrate
--
CREATE TABLE dbo.test1 (
    ID         INT          NOT NULL IDENTITY(1,1)
  , ExtID      VARCHAR(30)
  , Descrip    VARCHAR(50)
  , Name       VARCHAR(20)
)
GO

INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('143', 'Apple', 'Joe')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('0140', 'Adam', 'Jim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('123', 'Cucumber', 'Tim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('133', 'Banana', 'Kim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('170', NULL, 'Sim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('160', '', 'Rim')
INSERT INTO dbo.test1 (ExtID, Descrip, Name) VALUES('150', NULL, 'Lin')
GO

Correct Order of Data

Consider this query:

--
-- Beauty of ORDER BY clause
--
SELECT  ExtID
      , ISNULL(Descrip,'') AS Descrip
      , Name
FROM dbo.test1
ORDER BY
     CASE WHEN Descrip IS NULL OR Descrip = ''
              THEN NULLIF(Descrip,'')
          ELSE
              CAST(ExtID AS INT)
     END
   , CAST(ExtID AS INT)
GO

Usually, ORDER BY clause is one of the last clauses to be processed (in a SELECT statement). So, once a data set (internal virtual table) is created by FROM clause and WHERE clause; ORDER BY is applied on that internal virtual table to sort the dataset in correct format.

Using complicated ORDER BY statements you could order the data as you need. The above query is similar to having a new column added to SELECT clause with the same CASE statement logic as in ORDER BY clause. See below:

--
-- Beauty of ORDER BY clause
--
SELECT
     CASE WHEN Descrip IS NULL OR Descrip = ''
              THEN NULLIF(Descrip,'')
          ELSE
              CAST(ExtID AS INT)
     END AS [NewDescrip]
   , ExtID
   , ISNULL(Descrip,'') AS Descrip
   , Name
FROM dbo.test1
ORDER BY
   [NewDescrip]
 , CAST(ExtID AS INT)
GO

ORDER BY Clause with SELECT column

So, if you want to order a data set in a particular way, first try to add columns to SELECT clause with that logic (to make it easier to visualize), then use the same logic in ORDER BY clause.

This is very interesting !! Thanks Dylan. Not sure if I never know this feature or completely forgot it after used it before. Either way, good to learn something new again.

Hope this helps,
_Sqltimes

Read Full Post »

Database Servers on Virtual Machines with vCPU

More and more database servers are being migrated into virtual machines. The momentum towards virtual machines is strong, with good reasons, and as a DBA we need to come up with ways to make sure that our database servers are correctly configured to fully utilize the capabilities of virtual environments. Database servers are usually the last ones to be converted into virtual machines for obvious reasons, but in general, not all database servers need to be physical machines.= ir even have a dedicated machine, per se.

A good percentage of the database servers we have are small (to medium) size that it would not make much different if we virtualized them. Though the migration effort has become simpler with better tools availalbe, the task of comeing up with appropriate database configurations to fully utilize the resources of virtual environment while maintainig the same performance (SLA) is a non-trivial task. More on this subject later, but today, the topic is on vCPUs.

What are vCPUs?

Hypervisor software take full control of the hardware on the host machine and allows system administrators to present them to the virtual machines as needed. This capability allows us to enhance hardware resource utilization and bypass some license agreement restrictions. For example, Windows 2003 Standard Edition is limited to 4 CPUs. So if you have a machine with 6 or 8 CPUs, then the Operating System will only see 4 CPUs. With VMWare there is a way to get around this, a.k.a. cpuid.coresPerSocket/sockets option.

In VMware, vCPUs appears to the operating system as a single core single socket CPU (over simplified). vCPUs are scheduled on the logical CPUs which are hardware execution contexts. So, when you assign 4 vCPUs to a virtual machine, the Operating System on the VM sees them as 4 CPU sockets with single cores each. Now with cpuid.coresPerSocket/sockets option, we could configure the 8 CPUs from host machine as 4 CPUs with 2 cores each (4*2=8 vCPUs) or 2 CPU sockets with 4 cores each (2*4=8 vCPUs).

Example 1:

If you want a virtual machine with 1 socket with 8 cores, then you use the following configuration:

• vCPU = 8
• cpuid.coresPerSocket/sockets = 8

Example 2:

If you want a virtual machine with 4 sockets with 2 cores each, then use the following setting:

• vCPU = 8
• cpuid.coresPerSocket/sockets = 2

This is an over simplified summary f the underlying details for easy consumption. For more detailed explanation, please refer to this article on VMWare.

Other considerations:

A physical machine with 4 sockets with 4 cores each is not same as a Virtual machine with 4 Sockets with 4 cores each (using above settings). A vCPU is close to a core, but not exactly a core. It is the logical execution context (thread) on a core. A core could have 1 or more logical CPUs (threads). This difference matters of a database server as they are very resource intensive machines.

When the database server is processing 3000 or more transactions per second, having available CPUs to process the requests is important. A shared CPU (logical CPU a.k.a. CPU thread) could result in CPU bottleneck; Might result in heavy SOS_SCHEDULER_YIELD waits. There are some techniques to over come these situations, but need close coordination with System Administrators. As a rule of thumb, small and medium database server (in terms of traffic, size, payload etc) usually make better candidates for virtual machines than VLDBs.

 

Hope this helps,

_Sqltimes

Read Full Post »