SQL Server – Determine Number of CPU Cores

Background

Ran SQL Server Best Practices and one of the flagged items is that TempDB might not be properly configured.

Here is the error.

Textual

Category: Performance

Issue: This instance of SQL Server has only one tempdb data file
Processor Count: 4, TempDB datafiles Configured: 1

Impact: Under a heavy load, the tempdb database can become a single point of contention and affect concurrency and performance

Resolution: To reduce contention in the tempdb database, increase the number of tempdb data files, and configure the required startup trace flags.

Image

sqlservertempdbdatabasenotconfiguredoptimally

Review

Let us ensure that we actually have that # of Processor Cores.

SQL Server

sys.dm_os_schedulers

Code


select 
		  tblOS.scheduler_id
		, tblOS.cpu_id
		, tblOS.[status]
		, tblOS.[is_online]

from   sys.dm_os_schedulers tblOS

where  tblOS.[status] = 'VISIBLE ONLINE'

 

Output

sys-dm_os_schedulers

Explanation

We have four schedulers

 

Windows Internals

Task Manager

Steps

  1. Launch Task Manager
  2. Access the Performance Tab
  3. Click on CPU radio button

 

perfomance-cpu

Explanation

  1. Read the “Virtual processors” and “Virtual Machines

 

WMI

Steps


WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors

Output

wmic-cpu

SysInternals

Process Explorer

If you do not have a more recent edition of SysInternal’s Process Explorer, please download it from here.

BTW, as of this post, that version is 16.12.

Steps

  1. Launch Process Explorer
  2. Click on the menu items – View \ System Information…
  3. Click on CPU Tab

 

systeminformation-cpu

 

Explanation

  1. Read the Cores and Sockets
  2. BTW, on a busy system it might be instructive to make sure that “Show one graph per CPU” checkbox is checked

 

Coreinfo

Coreinfo is available here.

BTW, as of this post, that version is 3.31.

Steps

  1. It is Command Line Tool
  2. And, so launch a Command Shell
  3. Syntax
    • To get Processor\Core Info issue “coreinfo -c
    • To get Virtualization Info issue “coreinfo -v

Command Syntax – Processor/Core Info

Code

coreinfo -c

Output

coreinfo

 

Code

coreinfo -v

Output

hypervisor

 

CPUID

CPU-Z

cpz-processor

Explanation

  1. It is interesting that CPUID/CPU-Z
    • Has Number of Cores as 1
    • And, the Processor Count is 4
  2. We are good for CPU Speed
    • Intel Xeon CPU E7-2870 @ 2.40GHz
    • Core Speed => 2376.25 MHz
      • It is likely that Power Savings is not enabled

Summary

Again, CPU-Z is giving us Number of Cores at 1.

Not sure if it is because it is a virtualized environment.

Nothing new here, but it is always good to base Engineering decisions on collaborative data.

 

References

  1. Tempdb Configuration
    • Recommendations to reduce allocation contention in SQL Server tempdb database
      Link

 

 

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