Feb
5
2012

T-SQL to find Physical and Logical Processors (CPU)

Today while searching for t-sql scripts to collect Physical and Logical CPU count for around 100+ SQL servers (all version SQL 2000, 2005 and 2008 R2) came across following articles and the useful scripts, read below for more details,

The basic limitation behing finding physical CPU count is you can’t find directly using t-sql in SQL Server 2000, from SQL 2005 onwards you can easily find it using DMV sys.dm_os_sys_info

Script provided at following links, this script will work only for SQL Server 2005 and above,
Link1
Link2

Script:

SELECT cpu_count AS ‘Logical CPU Count’, hyperthread_ratio AS ‘Hyperthread Ratio’,
cpu_count/hyperthread_ratio As ‘Physical CPU Count’,
physical_memory_in_bytes/1048576 AS ‘Physical Memory (MB)’
FROM sys.dm_os_sys_info

Now other way to find only logical CPU count and below script will run across all versions of SQL Server and collect following information,
Hostname, SQL Instance Name, SQL Server Name, No. of databases, Overall database size, Logical CPU and RAM

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name like ‘#SVer%’)      
DROP TABLE #SVer
Create Table #SVer(ID int, Name sysname, Internal_Value int, Value nvarchar(512))
insert #SVer exec master.dbo.xp_msver
select CONVERT(CHAR(50), SERVERPROPERTY(‘MachineName’)) AS Hostname,
isnull(CONVERT(CHAR(50), SERVERPROPERTY(‘InstanceName’)), ‘Default’) InstanceName,
CONVERT(CHAR(50), SERVERPROPERTY(‘servername’)) ServerName,
(select COUNT(name) AS MB from master..sysdatabases) AS N’No. Of DBs’,
(select SUM(size*8/1024) AS MB from master..sysaltfiles) AS N’Overall Database Size (MB)’,
(select Internal_Value from #SVer where Name = N’ProcessorCount’) AS ‘Logical CPU’ ,
(
selectInternal_Value from#SVer where Name = N’PhysicalMemory’) AS ‘Physical Memory (RAM) (MB)’

Download PDF

Related Posts

About the Author: Nitin Garg

Indian born, trekker, biker, photographer, lover of monsoons... I love to blog the topics I research and find useful for self or online community to save time and energy :) Everything you read on my blog is my own personal opinion!

Leave a comment

Subscribe to this blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 174 other subscribers

Translate this blog!

EnglishFrenchGermanItalianPortugueseRussianSpanish

Calender

May 2019
M T W T F S S
« Nov    
 12345
6789101112
13141516171819
20212223242526
2728293031  

View Post by Categories

%d bloggers like this: