Solved

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

Posted on 2014-03-19
2
698 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
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…

809 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