Cores Schmores, Why do I care how many processors are running??

It seems like an academic thing to know how many processors are currently running on your SQL Server. Many times it is simply the number of processors on the server, but not necessarily... SQL Server makes it very easy to use the affinity mask options to use only specific processors. It is frequently used on multi-instance servers that don't want to share processors. Besides all that, if you have more than 50 servers, I would venture a guess that you might not know how many cores are running on each one.

I had a particular reason in mind when investigating the number of processors (actually 2 reasons) Firstly, I was writing some automated backup scripts using some add-on backup software that allows you to specify the number of threads the backup will use. Backups are many times faster if you have the appropriate number of worker threads allocated to I/O operations. Even SQL Backups can benefit from striping across files using the appropriate number of worker threads.

The "best" number of threads is based on the number of CPU cores, and more distinctly, how many CPU cores your SQL server is using (calculating affinity masked processors into the count).

My second reason for wanting a script that would tell me the right number of processors was the well-documented principle of having the appropriate number of tempdb data files (one for each processor core). Ultimately I wanted to make a script to run on each instance at startup and make sure tempdb had the right number of files. While I did accomplish that, I've never implemented it because it takes multiple restarts to make that change take affect.

Some other reasons that you might want to keep an eye on how many processors are affinity masked to your server:

• What if some other DBA swipes a core from your instance and uses it in another?
• What if you're hardware is upgraded and new cores are added and your instances are hard-masked?

While the low budget way to find out how many cores you're running is to look at xp_msver and see the number of processors, or check the GUI. I felt there was a more programmatic and elegant way to get this number inside automated scripts when I needed it.

For all these reasons, I decided to take matters into my own hands and write a stored procedure that returns the active number of cores on the instance at the moment it runs. This insures that at the moment I run the query, the correct number of cores is displayed to my backup's @threadcount option, or to my script that creates tempdb files.

Bitwise Operators: Return to Computer Science 101

I was a bit rusty when it came to bitmasking; it had been years since I went through the exercise and needed a refresher. My first steps back into the world of bitmasking was after stumbling upon the SYS.CONFIGURATIONS 'AFFINITY MASK' and 'AFFINITY64 MASK' values and re-reading this MSDN Article which reminded me that each CPU is represented by a bit in a binary value and that bit is either on or off.

Ex. An 8 processor machine:

0000 0001 means that processor #1 is on and the rest are off

0001 0010 means that processor #2 and 5 are on and the rest are off.

OK, that's simple enough, right? I then had to remember how to look at all of the bits in an integer value and parse through them in my program and also apply a bitmask to know whether the bit was on or off.

I chose to use the AND mask which in applies the following bitwise mask

1010
&1100
-------

= 1000 (masked with AND)

Think of Truth tables from back in the days of logic class or "Introduction to Computer Science". Both values need to be True (1) in order for the masked result to be True. A True (1) and a false (0) always become a false. A false and false always yields a false too... the only thing that returns true are two trues.

Think: 1 & 1 = 1

The previous masking example can be represented in SQL and decimal as

SELECT 10 & 12

The & operator performs the "AND" bitmasking mentioned above, 10 is decimal for 1010 and 12 is decimal for 1100. The result is 8 (or 1000). So at the end of the day 10 and 12 is 8... makes perfect sense, right!!?

Bitmasking Processors

In the table SYS.CONFIGURATIONS, the value for affinity mask is stored as an integer value. If the value were 255 or (1111 1111) we would know that 8 processors were turned on, but if any of them were turned off, I would need to do some quick binary addition to figure out which ones (231 in binary is what? OK, that means how many processors are on and off?). This is where bitmasking, or the bitwise & operator comes into play, along with some old school programming.

What I did was start a loop with the value of 1, and after each iteration multiply itself by 2, so the loop counter would go on like this: 1,2,4,8,16,32, 64...(look familiar?). I'm essentially passing a TRUE bit to be AND masked against the decimal value contained in the affinity mask. If the result of the masking operation is TRUE, it means that there is a processor core represented by that bit in the affinity mask.

A simplified version of the code looks like this:

DECLARE @BITS INT
SET @BITS = 1
WHILE @bits < 256
 BEGIN
 SELECT CAST (VALUE AS INT) & @BITS
 FROM sys.configurations
 WHERE NAME = 'AFFINITY MASK'
 SET @BITS = (@BITS * 2)
 END

Any time a non-zero value comes out of the above code it means that a processor is turned on. If you aren't using affinity masking at all on your instance, everything will come back as 0

Zen and Bitmasking

Like a Haiku, the above code is simple, minimalist, and has hidden strength and meaning. Nothing can be taken away or added, and as a total nerd, I am rather impressed with that. It was also like a sojourn into nature to go back to my computer science roots and do some binary operations. While not a cherry blossom or jade mountain, it still provided a soul stirring moment of enlightenment for this weary traveler.

Unfortunately this code had to fulfill its true destiny and so I encased it within a stored procedure and it will soon enough be called in thousands of backups every day.

There are a couple of caveats on the full version of the code:

1. I rely on the values in xp_msver. I fully believe that it knows the number of processors that are assigned to SQL server by the operating system. It's what SSMS uses (I ran a trace to make sure)This may be a weakness in some environment that I've never worked in, but it might be correct 100% of the time. I just don't know.
2. There is such a thing as an "affinity64 mask" for 64 bit servers that have more than 32 cores. I don't have such a beast to test on, but I wrote that part of the code, trusting that my logic was correct. It might not be, so please if anyone can test this on a 33 core machine or better, let me know.

The Code

call it like so:

DECLARE @NUMPROCS INT

EXEC dbo.USP_GET_PROCCOUNT @NUMPROCS OUTPUT

PRINT @NUMPROCS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('dbo.USP_GET_PROCCOUNT') IS NOT NULL
    DROP PROC dbo.USP_GET_PROCCOUNT
GO
CREATE PROC [dbo].[USP_GET_PROCCOUNT]
@NUMPROCS INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @PROCMASKSTABLE TABLE (MASK INT)
DECLARE @BITS BIGINT
DECLARE @X INT
--CREATE A TABLE VAR TO DUMP XP_MSVER INTO
DECLARE @PROCCOUNT table (idx int,
                        name varchar(255),
                        internal_val int,
                        char_val varchar(10))
INSERT @procCount
EXEC master..xp_msver processorCount
SELECT @NUMPROCS = internal_val from @procCount
-- NOW LOOP THROUGH THE NUMBER OF PROCESSORS AND APPLY A BITMASK
-- TO SEE WHAT PROCS ARE TURNED OFF
SET  @X = 1
SET @BITS = 1
--  IF THRE ARE 32 PROCS OR LESS, USE THE 32 PROC MASKING
    BEGIN
        WHILE @X <= @NUMPROCS and @x <=32
        BEGIN
--PUT THE BITMASKED VALUE IN A TABLE
            INSERT INTO @PROCMASKSTABLE
            SELECT CAST (VALUE AS INT) & @BITS
            FROM sys.configurations
            WHERE NAME = 'AFFINITY MASK'
            --BUMP UP THE LOOP CONTROL AND DOUBLE THE BITMASK
            SET  @BITS = (@BITS * 2)
            SET @X = @X + 1
        END
    END
-- ALSO ADD THE 64 PROC AFFINITY MASK if there are 33 or more procs
IF @NUMPROCS > 32 
    BEGIN
        WHILE @X <= @NUMPROCS
          BEGIN
            --PUT THE BITMASKED VALUE IN A TABLE
            INSERT INTO @PROCMASKSTABLE
            SELECT CAST (VALUE AS INT) & @BITS
            FROM sys.configurations
            WHERE NAME = 'AFFINITY64 MASK'
            --BUMP UP THE LOOP CONTROL AND DOUBLE THE BITMASK
            SET  @BITS = (@BITS * 2)
            SET @X = @X + 1
          END
    END
--ALL NON-ZERO VALUES ARE PROCESSORS THAT SQL SERVER IS USING 
SELECT @X = COUNT(MASK) FROM @PROCMASKSTABLE WHERE MASK > 0
IF @X > 0
    SET @NUMPROCS = @X