SQL Server 2008: Hot-Add CPU (and affinity masks)

Quickie today as I’m preparing to speak at the Pacific Northwest SQL Server User Group meeting tonight on the MS Campus here in Redmond (my trademark Detection and Recovery from Database Corruptions talk).

SQL Server 2005 introduced the concept of hot-add memory, to allow for dynamic workload handling. SQL Server 2008 increases these capabilities by adding hot-add CPU as well. ‘Hot-add‘ means being able to plug in a CPU while the machine is running and then reconfigure SQL Server to make use of the CPU ONLINE! (i.e. no application downtime required at all)

There are a few restrictions:

  • You need a 64-bit system that support hot-add CPU (obviously :-))
  • You need Enterprise Edition of SQL Server 2008
  • You need Windows Server Datacenter or Enterprise Edition

When you plug in the new CPU, SQL Server won’t automatically start using it. If you think about it, it can’t – you may not want that CPU to be used by SQL Server – so it has to be told that it can use it. You do that by setting the appropriate affinity masks and then running a RECONFIGURE.

Ok – now we come to the bit that needs the “(and affinity masks)” in the title. What’s an affinity mask? In a nutshell, it’s a bitmap of all the CPUs on the machine that specifies which ones are available for general SQL Server Engine use, and which ones are available only for SQL Server I/O. There are two kinds of masks, a regular affinity mask, and an affinity I/O mask. The regular affinity mask, if all zeroes, says that Windows decides who get’s what CPU when. If the affinity mask is non-zero, then there’s a bit per CPU. If it’s set to 1 then SQL Server can use the CPU. If a bit is set to 1 in the affinity I/O mask, then the CPU can only be used for I/O. A bit cannot be set in both masks. Now – an affinity mask is 32-bits wide, so if you have more than 32 CPUs, you need to use two more affinity masks, called affinity64 and affinity64 I/O. These do the same thing but for CPUs 32-63 on the machine.

So, very cool, especially for those of you that can afford such hardware – I can’t so I don’t have a box to test it on (the 64-bit server we have here at SQL skills doesn’t support it).

6 thoughts on “SQL Server 2008: Hot-Add CPU (and affinity masks)

  1. Hey Paul,

    Didn’t this feature have name changes, etc. related to issues with it? My understanding was that at present, all connections get dropped when it is used. That wouldn’t make it quite so transparent and not really suitable for dynamic alterations.

    Regards,

    Greg

  2. Hi, Paul!

    If your 64-bit box has multiple CPU’s maybe you could run Virtual Server to limit the number of CPU’s (to say, one), configure SQL Server, then after it’s running allocate another CPU to the virtual machine. With the new Hypervisor code that should give you the same scenario, shouldn’t it?

    Allen

  3. Hey Allen – you *could* but I’d be very wary of the perf overhead of going through Virtual Server for IOs – in the last few conference workshops I’ve taught I’ve asked about people’s experiences and the universal answer has been not to put a virtualized SQL Server into production because of the perf issues. Cheers

  4. Hi Greg – no. There was an issue with the Windows RC where hot-add CPU in Windows generated a pnp event that caused the network stack to deinstall/install, thus dropping SQL Server connections. It’s been fixed in Windows RTM so Hot-Add CPU in SQL Server will not be changing. Thanks.

  5. Hi Paul,

    What are the things we need to check after adding more CPU? We have facing with high CPU usage than ever after moving CPU from 4 to 8

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.