SQL Server – Number of CPUs Available & Usable

Background

Trying to document a set of SQL Server Instances that we are provisioning.

We have four servers, a server for each environment, Dev/Test/QA/Production.

 

Lineage

We have addressed this issue a couple of times in the past.

  1. SQL Server – Determine Number of CPU Cores
    Link

 

Measurement

OS

Task Manager

Here is see what the numbers look like via Task Manager / Performance Tab.

Image

cpu_taskmanager_20170211_1053am

 

Explanation

  1. Max Speed is 2.4 GHz
  2. Number of Sockets & Number of Processors is 8
  3. Virtual Machine is Yes
    • It is a virtualized environment running VMWare

 

SysInternals

coreinfo

Here us what the numbers look like via SysInternal’s coreinfo.

Syntax


coreinfo -c

Image

coreinfo-c_20170211_1100am

 

Explanation

  1. We are showing 8 Physical processors

 

SQL Server

Let us review the numbers via SQL Server

Dynamic Management Views ( DMV )

sys.dm_os_schedulers – VISIBLE ONLINE

Documentation

We will look in the sys.dm_os_schedulers for entries that have their STATUS equal to VISIBLE ONLINE.

Code

select
          tblOS.scheduler_id
        , tblOS.cpu_id
        , tblOS.[status]
        , tblOS.[is_online]
		, tblOS.current_workers_count
 
from   sys.dm_os_schedulers tblOS
 
where  tblOS.[status] = 'VISIBLE ONLINE'

Output

sys_dm_os_schedulers_visible_20170211_1111am

Explanation
  1. We see that we are only seeing 4 CPUs.

 

sys.dm_os_schedulers – ALL

Documentation

We will look in the sys.dm_os_schedulers for entries that have their STATUS equal to VISIBLE ONLINE.

Code

select
          tblOS.scheduler_id
        , tblOS.cpu_id
        , tblOS.[status]
        , tblOS.[is_online]
		, tblOS.current_workers_count
 
from   sys.dm_os_schedulers tblOS
 

 

Output

sys_dm_os_schedulers_all_20170211_1115am

 

Explanation
  1. Column = STATUS
    • VISIBLE ONLINE
      • Four entries have their STATUS marked VISIBLE ONLINE
      • VISIBLE schedulers are used to process user requests (MSFT )
    • VISIBLE OFFLINE
      • The next four entries have their STATUS marked VISIBLE OFFLINE
    • VISIBLE ONLINE ( DAC )
      • We have a lone bearing VISIBLE ONLINE (DAC)
      • DAC means Dedicated Administrator Connection
    • HIDDEN ONLINE
      • HIDDEN schedulers are used to process requests that are internal to the Database Engine. ( MSFT )
      • Nine entries marked as HIDDEN OFFLINE

 

SQL Engine Info

What edition of SQL Server are we using?

 

@@VERSION

Code


select @@version 

Output

version

 

Explanation

  1. Version :- SQL Server 2014 (SP2)
  2. Edition :- Standard Edition
  3. Bitness :- 64-bit

 

 

@@VERSION

Code


select [edition] = serverproperty('edition') 

Output

serverpropertyedition

Explanation

  1. Edition :- Standard Edition (64-bit)

Documentation

Features Supported by the Editions of SQL Server 2014

Link

crossboxscalelimits

ErrorLog

Reviewed the error log and made changes to the Local Security Policy.

2017-02-22 8 AM

  1. Microsoft SQL Server 2014 (SP2) (KB3171021) – 12.0.5000.0 (X64)
    Jun 17 2016 19:14:09
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
  2. UTC adjustment: -8:00
  3. System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
  4. SQL Server detected 8 sockets with 1 cores per socket and 1 logical processors per socket, 8 total logical processors; using 4 logical processors based on SQL Server licensing.
  5. Detected 65535 MB of RAM.
  6. Using conventional memory in the memory manager.
  7. Database Instant File Initialization: disabled. For security and performance considerations see the topic ‘Database Instant File Initialization’ in SQL Server Books Online.
  8. CLR version v4.0.30319 loaded.
  9. Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.

2017-02-22 8:30 AM

  1. Microsoft SQL Server 2014 (SP2) (KB3171021) – 12.0.5000.0 (X64)
    Jun 17 2016 19:14:09
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
  2. UTC adjustment: -8:00
  3. System Manufacturer: ‘VMware, Inc.’, System Model: ‘VMware Virtual Platform’.
  4. SQL Server detected 8 sockets with 1 cores per socket and 1 logical processors per socket, 8 total logical processors; using 4 logical processors based on SQL Server licensing.
  5. Detected 65535 MB of RAM.
  6. Using locked pages in the memory manager.
  7. Database Instant File Initialization: enabled. For security and performance considerations see the topic ‘Database Instant File Initialization’ in SQL Server Books Online.
  8. CLR version v4.0.30319 loaded.
  9. Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.

 

Interpretation

The OS is reporting 8 processors, but SQL is only availing 4.

The reason is that we are running Standard Edition, and that edition maxes out at 4 Processors.

BTW, the max memory is 128 GB.

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s