Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • Last Modified:

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

Hello there,

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

Please advise.

Thanks and Regards.
0
goprasad
Asked:
goprasad
2 Solutions
 
Sreedhar VengalaCommented:
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
 
Andrei FomitchevCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now