Solved

Where to find logical processor count in a physical and virtual SQL server?

Posted on 2014-03-19
2
706 Views
Last Modified: 2014-04-14
Hello there,

Where to find logical processor count in a physical and virtual SQL server?

Please advise.

Thanks and Regards.
0
Comment
Question by:goprasad
2 Comments
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 250 total points
ID: 39941266
Below give you the count of Processor in your physical and virtual servers

SELECT cpu_count
FROM sys.dm_os_sys_info

Open in new window


And also look into the below query which give you Hyper thread ration, physical CPU Count, Physical Memory in MB, SQL Server Start Time, Affinity Type Desc besides Logical CPU Count.

SELECT
	cpu_count AS [Logical CPU Count],
	hyperthread_ratio AS Hyperthread_Ratio,
	cpu_count / hyperthread_ratio AS Physical_CPU_Count,
	physical_memory_kb / 1048576 AS Physical_Memory_in_MB,
	sqlserver_start_time,
	affinity_type_desc
FROM sys.dm_os_sys_info

Open in new window

0
 
LVL 8

Assisted Solution

by:Andrei Fomitchev
Andrei Fomitchev earned 250 total points
ID: 39941568
In addition to
SELECT cpu_count
FROM sys.dm_os_sys_info

Start SSMS.
Right click on instance name.
Click Properties
Click tab Processors

On that tab you can change processors usage by SQL Server as well as watch current settings. There are separate settings for processors for calculation and for I/O.

By default it is automatic, but you can make the difference. For example, in my case I have 16 processors - 12 for calculations and 4 for I/O. It saves switches from calculations to I/O.

On another server processor #1 used for network operations by vm. I excluded it from usage by MS SQL Server. It reduces waits, related to multi-processing (before that all processors were waiting for #1 for completion).
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
This article outlines why you need to choose a backup solution that protects your entire environment – including your VMware ESXi and Microsoft Hyper-V virtualization hosts – not just your virtual machines.
This Micro Tutorial steps you through the configuration steps to configure your ESXi host Management Network settings and test the management network, ensure the host is recognized by the DNS Server, configure a new password, and the troubleshooting…
This video shows you how to use a vSphere client to connect to your ESX host as the root user. Demonstrates the basic connection of bypassing certification set up. Demonstrates how to access the traditional view to begin managing your virtual mac…

792 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