Solved

Limiting SQL Server 2008 R2 CPU useage

Posted on 2014-03-13
4
782 Views
Last Modified: 2014-03-15
Hello Experts. I am a SQL Server novice in need of assistance. We have an HP DL585 G7 server with 4 AMD Opteron 6276 processors, 16 cores a piece. The server has 384GB of memory. Windows Server 2008 R2 Enterprise SP1 and MS SQL Server 2008 R2 Enterprise are installed. Right now SQL Server is using all 64 cores. As many of you know, paying for 64 cores is outrageous, almost $700,000! Is there away to tell SQL Server to only use 16 cores? I've glanced through the server configuration. In the Processor section, I see 8 NumaNodes (0-7) with 8 CPUs listed under each. Do I simply select 2 NumaNodes and check all 8 CPUs underneath them to get 16 cores? Does it matter which 2 I choose? How do I determine which affinity to use? I'm new at this so please be gentle. I appreciate your time!
0
Comment
Question by:CMWinters
  • 2
4 Comments
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 39928034
Within the configuration of the database server, you can set affinity which means you can specify how many processors can be used.

Which licensing model are you going for? If you are buying individual user/device cals, you should not care about how many processors are being used.
If you are buying a per processor license, then the affinity is the way to restrict.
note per processor means per processor no matter the number of cores:
download.microsoft.com/download/2/7/0/270B6380-8B38-4268-8AD0-F480A139AB19/SQL2008R2_LicensingQuickReference-updated.pdf
 
You would select the processor/ and the cores within.
0
 

Author Comment

by:CMWinters
ID: 39930433
Thanks for your information. I didn't know we could still buy box product. We had a MS select agreement quote for 2012 that forced core pricing. I've found a couple of places I can still buy the per processor version.
0
 
LVL 77

Expert Comment

by:arnold
ID: 39930447
Double check with your select vendor, or go through the MS Licensing site.  I believe you can buy a per processor from them as well.   not sure what per core pricing you went through. You may have instead of 4 processors, counted the individual cores instead.
The select license is better as the cumulative license offers additional discounts within the select that an external purchase will not be counted towards.  

You might want to consider buying the 2012 SQL R2 version check with MS/vendor whether it would provide licensing for the older versions (2008).
0
 
LVL 6

Expert Comment

by:chlebi
ID: 39930978
Just wanted to add: be careful as with certain licenses it is calculated per physical core - that means even if you limit via CPU affinity it is calculated on all cores.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question