SQL Server Discovery

# re: Find System information using SQL Server

Thanks for the query.
BTW is there a way to find out exact OS name version using t-sql? select @@version does not return os name...
now I am using
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'xp_cmdshell', 1
reconfigure
exec xp_cmdshell 'systeminfo'
exec sp_configure 'xp_cmdshell', 0
reconfigure
exec sp_configure 'show advanced options', 0
reconfigure
though it is rather awkward and returns to much info (including the info your query provides)

# re: Find System information using SQL Server

Note that Logical vs Physical CPU count


SELECT cpu_count AS [Logical CPUs]
,cpu_count / hyperthread_ratio AS [Physical CPUs]
--,cpu_count
--,hyperthread_ratio
,physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

# re: Find System information using SQL Server

Thanks for the script. Very useful. Thanks Remote DBA and Jerry Hung for ur inputs too

# re: Find System information using SQL Server

Hi,
I guess if you have one physical processor and have hyperthreading enabled then the above query needs to be modified as:

SELECT cpu_count AS [Logical CPUs],
(case hyperthread_ratio
when 1 then
'1'
else
(cpu_count / hyperthread_ratio)
end)AS [Physical CPUs],
hyperthread_ratio
,physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
FROM sys.dm_os_sys_info

Otheriwse the divison of Logical Processors with hyperthreading value will give you a count of 2 which will eventually be as 2 physical processor.When Hyperthreading is enabled OS will see a single physical processor as 2 logical processor.
Please let me know if it makes sense.
Regards,
Zainudeen V K