Solved

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

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

Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

628 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