SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

Find System information using SQL Server

If you don't have local login rights on a Server and need to find system resources onboard. Here is a little TSQL script that will display useful information like CPU, memory and pagefile size:


USE [master];
GO
select 
    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 


Hope it helps!

Print | posted on Wednesday, February 11, 2009 6:31 PM |

Feedback

Gravatar

# 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)
2/12/2009 2:33 AM | Remote DBA
Gravatar

# 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
2/23/2009 12:35 PM | Jerry Hung
Gravatar

# re: Find System information using SQL Server

Thanks for the script. Very useful. Thanks Remote DBA and Jerry Hung for ur inputs too
2/24/2009 12:14 PM | Kekerode
Gravatar

# 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

9/20/2010 1:22 PM | Zainu
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET