Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Script to get CPU and Cores for SQL Server 2012 Licensing


By:   |   Read Comments (31)   |   Related Tips: More > Licensing

Attend a SQL Server Conference for FREE >> click to learn more


Problem

With SQL Server 2012, Microsoft introduced a new licensing model; licensing per core replaced the licensing per processor. We need to adjust budget to reflect licensing changes for our next Enterprise Agreement renewal, but we do not have processor core information from any of our server inventory tools. How can we quickly gather information about each server's processor cores without logging in to each server?

Solution

In this tip we will provide a PowerShell script that you can use to query your servers for the number of processors (CPUs) and cores. The entire licensing process is more complicated than just the number of CPUs or cores (you have to take into consideration SQL Server edition and virtualization), but this script is a good start.

If you are not familiar with the SQL Server 2012 licensing model changes you can read this tip. This tip describes the licensing changes and also it has several useful links to Microsoft documentation.

PowerShell Function to Find Number of SQL Server Processors and Cores

We will create a PowerShell function that utilizes Win32_Processor and Win32_ComputerSystem WMI classes. The function accepts ComputerName as a parameter that can be piped from a text file.

param([string]$SQLServerList=$(Throw `
"Paramater missing: -SQLServerList ConfigGroup"))


Function Get-CPUInfo{
    [CmdletBinding()]
    Param(
    [parameter(Mandatory = $TRUE,ValueFromPipeline = $TRUE)]   [String] $ComputerName

    )

    Process{
    
            # Get Default SQL Server instance's Edition
            $sqlconn = new-object System.Data.SqlClient.SqlConnection(`
                        "server=$ComputerName;Trusted_Connection=true");
            $query = "SELECT SERVERPROPERTY('Edition') AS Edition;"

            $sqlconn.Open()
            $sqlcmd = new-object System.Data.SqlClient.SqlCommand ($query, $sqlconn);
            $sqlcmd.CommandTimeout = 0;
            $dr = $sqlcmd.ExecuteReader();

            while ($dr.Read()) { 
             $SQLEdition = $dr.GetValue(0);}

            $dr.Close()
            $sqlconn.Close()

   
            #Get processors information            
            $CPU=Get-WmiObject -ComputerName $ComputerName -class Win32_Processor
            #Get Computer model information
            $OS_Info=Get-WmiObject -ComputerName $ComputerName -class Win32_ComputerSystem
            
     
           #Reset number of cores and use count for the CPUs counting
           $CPUs = 0
           $Cores = 0
           
           foreach($Processor in $CPU){

           $CPUs = $CPUs+1   
           
           #count the total number of cores         
           $Cores = $Cores+$Processor.NumberOfCores
        
          } 
           
           $InfoRecord = New-Object -TypeName PSObject -Property @{
                    Server = $ComputerName;
                    Model = $OS_Info.Model;
                    CPUNumber = $CPUs;
                    TotalCores = $Cores;
                    Edition = $SQLEdition;
                    'Cores to CPUs Ratio' = $Cores/$CPUs;
                    Resume = if ($SQLEdition -like "Developer*") {"N/A"} `
                        elseif ($Cores -eq $CPUs) {"No licensing changes"} `
                        else {"licensing costs increase in " + $Cores/$CPUs +" times"};
    }
   Write-Output $InfoRecord
          }
}

#loop through the server list and get information about CPUs, Cores and Default instance edition
Get-Content $SQLServerList | Foreach-Object {Get-CPUInfo $_ }|Format-Table -AutoSize Server, Model, 
   Edition, CPUNumber, TotalCores, 'Cores to CPUs Ratio', Resume

Save the script as "Cores_to_CPU_Ratio.ps1" PowerShell file.

Running the PowerShell script to Find Number of Cores and Processors

The script requires a list of SQL Servers as the input parameter. This could just be a simple text file with one column (without a column header) containing host names only. You do not use the SQL Server named instance, because we want to look at the physical box.

Use the "SL" command to change to the directory where you saved the script and run the script as shown below:

SL "M:\DBA\Scripts\powerShell"
.\Cores_to_CPU_Ratio.ps1 -SQLServerList "C:\SQLSrvList1.txt" 

Here are the results for the demo environment:

The script requires SQL Servers list as input parameter.

Interpreting the results for the Number of Processors and Cores

The information about potential licensing is in the "Resume" column. If we have the same number of CPUs as number of Cores then we do not have to worry about licensing more cores.

Another set of useful information could be obtained analyzing "Model", "Edition" and "Ratio" columns. In our example we have more than 50% of our SQL Servers on VMware. Most of them are Enterprise Edition. We also have DEMOSRV5 which is a physical server where we will see the highest increase in licensing costs. Based on this information we can see that it would be beneficial to migrate DEMOSRV5 to a virtual server and to take advantage of the Maximum Virtualization licensing. Note, you can only take advantage of this if you have Software Assurance (SA) coverage. Read more about Microsoft SQL Server 2012 Virtualization Licensing here.

Next Steps
  • Read more PowerShell tips here.
  • Read Microsoft's "Determining SQL Server 2012 Core Licensing Requirements at SA Renewal" document.
  • Read Microsoft's "SQL Server 2012 Licensing Guide" document.
  • Use the script to assess your environment if you have not transitioned yet to the new licensing model or use it just to review your current environment. You will need to take into consideration the highest Edition installed on your server. For example, if you have Standard and Enterprise Edition installed on the same server as different SQL Server instances you will need to apply Enterprise licensing for that server.
  • Use this script to find out if you can take advantage of the Maximum Virtualization licensing mode.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, February 23, 2015 - 9:27:14 PM - Svetlana Golovko Back To Top

Hi David,

Make sure that SQLSrvList1.txt has only server names. For example for 2 servers there will be just these 2 lines:

SERVER1

SERVER2

Then, "SL" is pretty much the same as "CD" command in CMD (change directory), so in your case it will be:

 SL"C:\"

 

 


Monday, February 23, 2015 - 1:56:17 PM - David Back To Top

I am running this script and having problems at the beginning.

I have this as my first line and the text file is named SqlSrvList1.txt. I have change nothing else in the script except where I needed to update the SqlSrvList1 referencing the text file.

param([string]$SQLSrvList1=$(Throw` "c:c-SQLSRVList1 ConfigGroup")) First Line of script.

 

Kickoff Script I have the following: (This is a separate script I am running to kikoff your other script. I'm assuming that's what you intended here. Both the text file and the script are at the root of C:\.

 

SL"C:SQLSrvList1.txt\"

 

 

 

.\Cores_to_CPU_Ratio.ps1

 

-SQLSrverlist1"C:\SQLSrvList1.txt"

 

 

 

Here is the errors I am getting:

 

SL : Cannot find path 'C:\SQLSrvList1.txt\' because it does not exist.

 

 

 

 

 

 

 

 

 

At C:\Users\dcook\Documents\Cores_Script_Kickoff.ps1:1 char:1

 

 

 

+ SL "C:SQLSrvList1.txt\"

 

 

 

+ ~~~~~~~~~~~~~~~~~~~~~~~

 

 

 

+ CategoryInfo : ObjectNotFound: (C:\SQLSrvList1.txt\:String) [Set-Location], ItemNotFoundException

 

 

 

+ FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

 

 

 

 

 

 

 

Throw c:c-SQLSRVList1 ConfigGroup : The term 'Throw c:c-SQLSRVList1 ConfigGroup' is not recognized as the name of a cmdlet, function, script file,

 

 

 

or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

 

 

 

At C:\Cores_to_CPU_Ratio.ps1:1 char:30

 

 

 

+ param([string]$SQLSrvList1=$(Throw` "c:c-SQLSRVList1 ConfigGroup"))

 

 

 

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

 

+ CategoryInfo : ObjectNotFound: (Throw c:c-SQLSRVList1 ConfigGroup:String) [], CommandNotFoundException

 

 

 

+ FullyQualifiedErrorId : CommandNotFoundException

 

 

 

 

 

 

 

Get-Content : Cannot bind argument to parameter 'Path' because it is an empty string.

 

 

 

At C:\Cores_to_CPU_Ratio.ps1:65 char:13

 

 

 

+ Get-Content $SQLSrvList1 | Foreach-Object {Get-CPUInfo $_ }|Format-Ta ...

 

 

 

+ ~~~~~~~~~~~~

 

 

 

+ CategoryInfo : InvalidData: (:) [Get-Content], ParameterBindingValidationException

 

 

 

+ FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Microsoft.PowerShell.Commands.GetContentCommand

 

 

 

 

 

Any thoughts?

 

 

 

Thanks,

 

David

 

 

 

 

 

 

 

 

 

 

 


Friday, January 23, 2015 - 8:10:36 PM - Svetlana Golovko Back To Top

Hi Ranger,

 

Sorry, I missed your comment in December. Did you figure this out?

You have to be local administrator on SQL Server and have login on SQL Server itself.


Thursday, December 04, 2014 - 9:45:08 AM - Ranger_El Back To Top

Svetlana

Great post thanks for the help on this.

I have an odd issue. I can connect to my servers that are in my .txt list.  However the edition remains blank for all of them , the rest of the information is populated.

Also I get this error for each server listed.

Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

Is there anything you can recommend I check out.

What is the minimum AD permissions I need on each box being looked at ?

Many thanks

Ranger


Friday, August 08, 2014 - 2:57:29 PM - SQLpuppy Back To Top

Great work! Thank you for the script. I enhanced this script to get other information of the server.


Thursday, May 22, 2014 - 7:39:00 PM - Svetlana Golovko Back To Top

Hi Craig,

The licensing is pretty complicated and to get 100% correct answer you probably need to contact Microsoft. But from what I remember it doesn't matter if the server uses processor or not. You have to license all CPUs on the host.


Thursday, May 22, 2014 - 10:34:41 AM - Craig Back To Top

Hi, 

 

I'm trying to understand whether you are required to license all the SQL Server processors on a server even if SQL Server is not using all of them?  Reason I ask is that we've got some SQL servers that are idling and it would be great if I can just disable a few processors from enterprise manager and thereby reduce the number of licenses required.

 

thanks.


Friday, November 01, 2013 - 4:03:04 PM - Svetlana Golovko Back To Top

Hi Adrian,

 

You need create C:\SQLSrvList1.txt file first and populate it with your SQL Servers (just host names). For example:

 

SQLSRV1

SQLSRV2

SQLSRV3


Friday, November 01, 2013 - 3:02:18 PM - Adrian Back To Top

Hi Ms. Svetlana can you help me with this error? Thank you.

PS C:\Users\user\Desktop> .\Cores_to_CPU_Ratio.ps1 -SQLServerList "C:\SQLSrvList1.txt"
Get-Content : Cannot find path 'C:\SQLSrvList1.txt' because it does not exist.
At C:\Users\user\Desktop\Cores_to_CPU_Ratio.ps1:66 char:1
+ Get-Content $SQLServerList | Foreach-Object {Get-CPUInfo $_ }|Format-Table -Auto ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (C:\SQLSrvList1.txt:String) [Get-Content], ItemNotF
   oundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand


Friday, October 04, 2013 - 5:38:43 PM - Svetlana Golovko Back To Top

DBAdmin,

Make sure you use Server (host) name, not the SQL Server instance name.

Svetlana


Friday, October 04, 2013 - 5:34:15 PM - Svetlana Golovko Back To Top

DBAdmin,

I am not sure why it does not work for you. I tested the script on the servers with hyperthreading as well and it still produced correct results.

Svetlana


Friday, October 04, 2013 - 5:30:26 PM - Svetlana Golovko Back To Top

Aaron,

Yes, this is a great tool and I used it to validate the results of my script. But MAP toolkit requires installation which in some environments will require approvals etc. This script is just a quick solution that perform specific task.

Svetlana


Friday, October 04, 2013 - 2:09:10 PM - Aaron Frizzell Back To Top

Microsoft has a wonderful tool available which will perform all these steps and more, automtaically, and store the results in LocalDB.

It's called the Microsoft Assessment and Planning Toolkit.

http://technet.microsoft.com/en-us/library/bb977556.aspx 

Great reporting capabilities, output to Excel. Many details, many sheets.


Friday, October 04, 2013 - 8:04:58 AM - Svetlana Golovko Back To Top

Andy,

 

The script works for the SQL Servers on VMware as well. We are actually moving now all of our SQL Server to the VMware to take advantage of the unlimited virtualization licensing.

 

Svetlana


Friday, October 04, 2013 - 3:18:58 AM - Andy Back To Top

Useful, thank you!  I prefer to check Task Manager, Device Manager, the old CMD systeminfo, and "System", as well as looking at the official vendor side (primarily Intel for us) to get the CPU specs.  I then compare the number of CPUs each method reports.... and still the answer is not always obvious. But this PowerShell script is one more tool for me to use.

With many of our servers in a VMware environment, I've even more confused. Any advice for a virtualised platform?

Andy


Thursday, October 03, 2013 - 2:42:27 PM - DBAdmin Back To Top

This routine returns 24 CPUs and 0 (zero) cores for one system. The system has two CPUs with six cores each and HTT. How can we use this routine for SQL Server 2012 licensing? The right information for the system, 12 cores, is nowhere in sight.


Friday, September 27, 2013 - 7:15:42 PM - Svetlana Golovko Back To Top

Ahmed,

Instances are not licensed separately, so you need to use physical server name or VM name (host name) only as input.

 

Svetlana


Friday, September 27, 2013 - 12:06:16 PM - Ahmed Back To Top

Hi

How about named instances, in my case majority of SQL Server have just named instances. Thanks


Thursday, September 26, 2013 - 2:19:31 PM - sreekanth Bandarla Back To Top

Or...if you are not comfortable poking around BIOS settings, you can use CPU-Z tool. It's one of my fav tools in my Free toolbelt :) Many companies use this tool on Database Servers as well to explore CPU and Memory on their Servers.


Monday, September 23, 2013 - 9:47:55 PM - Svetlana Golovko Back To Top

Hi Yogi,

 

The system configuration will show 4 cores (logical processors) if your laptop has hyper threading enabled. You can check if you have it enabled as  described here: http://www.pcmag.com/article2/0,2817,2422874,00.asp

 

Svetlana


Monday, September 23, 2013 - 10:16:13 AM - yogi Back To Top

Hi Svetlana

 

i tried on my laptop ( core i5 ) , where i got output as 1 cpu and 2 cores.

but when i check on system configuration its showing 1 cpu and 4 cores.

which one i need to consider ? please clarify.


Sunday, September 22, 2013 - 9:49:59 PM - Mike Back To Top

Cool - thanks for the clarification, Svetlana.  Awesome script nonetheless - I'm learning a LOT!

Mike

 


Sunday, September 22, 2013 - 6:25:35 PM - Svetlana Golovko Back To Top

Hi Mike,

 

The script has not been tested or designed to work with the named instances. Instances are not licensed separately, so you need to use physical server name or VM name (host name) only as input.

 

Thanks,

Svetlana


Friday, September 20, 2013 - 4:52:08 PM - Mike Back To Top

Ryan, thanks for the feedback.

This is what I'm seeing when I run the script with the named instance in the input file:

Server                  Model Edition                   CPUNumber TotalCores Cores to CPUs Ratio Resume                             

------                  ----- -------                   --------- ---------- ------------------- ------                             

myserver\sql2012       Standard Edition (64-bit)         1          0                   0 licensing costs increase in 0 times

(Note the missing value for 'Model')
 
 
and this is what I'm seeing when I only pass the server name:
 
Server          Model                         Edition CPUNumber TotalCores Cores to CPUs Ratio Resume                             
------          -----                         ------- --------- ---------- ------------------- ------                             
myserver Precision WorkStation T3500                   1          4                   4 licensing costs increase in 4 times
 
(Note the missing 'Edition' and the differences in 'Total Cores' and 'Cores to CPUs')
 
 
Sorry if I'm missing something obvious somewhere - like I said I'm completely new to Powershell.
 
 
Mike
 

Thursday, September 19, 2013 - 11:33:48 PM - Ryan Back To Top

Mike as per Svetlana's post - "You do not use the SQL Server named instance, because we want to look at the physical box."

Remeber you can have as multipile instances on a single box and only need to licence the box once.

 

Ryan


Thursday, September 19, 2013 - 7:06:10 PM - Mike Back To Top

Svetlana,

I'm also new to Powershell scripting, but I don't think your code takes named instances into account, does it?

 

Mike

 


Thursday, September 19, 2013 - 11:21:46 AM - Svetlana Golovko Back To Top

Hi Sreekanth,

No, the Hyper threading information is not included in this script as it does not affect licensing. But you can add this using NumberOfLogicalProcessors property (use it the same way as the NumberOfCores in the script).

 

Thanks,

Svetlana


Thursday, September 19, 2013 - 11:06:04 AM - Svetlana Golovko Back To Top

Hi Shivram, Yogi

The main script has been updated now. Let me know if you still have any issues.

Svetlana


Thursday, September 19, 2013 - 10:56:43 AM - yogi Back To Top

Hi

 

while executing the powershell script getting below error: pls help me on this , if any corrections required let me know

PS D:\ps> SL "D:\ps"
.\Cores_to_CPU_Ratio.ps1 -SQLServerList "D:\ps\sqlsvrlist.txt"
Missing '=' operator after key in hash literal.
At D:\ps\Cores_to_CPU_Ratio.ps1:52 char:15
+   Write-Output <<<<  $ResultRecord
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingEqualsInHashLiteral
 e copying the code below to remove the SSMS formatting.


Thursday, September 19, 2013 - 10:29:08 AM - sreekanth Bandarla Back To Top

Hi Svetlana - Does this function take Hyper threading into consideration?


Thursday, September 19, 2013 - 6:32:15 AM - Shivram Back To Top

Hi,

I am trying to use the script, but powershell gives me the following error:

Missing '=' operator after key in hash literal.

At C:\Cores_to_CPU_Ratio.ps1:52 char:13

+   Write-Host <<<<  $ResultRecord

    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException

    + FullyQualifiedErrorId : MissingEqualsInHashLiteral

 

I am a little new to powershell so please help me.

Thank you.


Learn more about SQL Server tools