Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-03-19
2
Medium Priority
?
749 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 1000 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 1000 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

Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

722 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