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!
Legacy Comments
|
Remote DBA
2009-02-12 |
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) |
|
Jerry Hung
2009-02-23 |
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 |
|
Kekerode
2009-02-24 |
re: Find System information using SQL Server Thanks for the script. Very useful. Thanks Remote DBA and Jerry Hung for ur inputs too |
|
Zainu
2010-09-20 |
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 |