Get SQL Server Hardware Information using sys.dm_os_sys_info

If you do not have physical access to your Database but want to get the hardware information of your SQL Server, use this query:

SELECT cpu_count AS [Logical CPUs], 
hyperthread_ratio AS [Logical vs Physical cores Ratio], 
physical_memory_kb/1024 AS [Physical Memory in MB], 
committed_kb/1024 AS [Committed Memory in MB],
committed_target_kb/1024 AS [Committed Target Memory in MB],
max_workers_count AS [Max Workers Count], 
sqlserver_start_time AS [SQL Server Start Time], 
virtual_machine_type_desc AS [Virtual Machine]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

The sys.dm_os_sys_info is available since SQL Server 2005 and contains information about server resources. For eg: you can find out the following: 

• How many CPUs are there in the server 
• Total logical processors 
• Amount of physical memory available (physical_memory_kb - SQL Server 2012 onwards) 
• Amount of Virtual memory available (virtual_memory_kb - SQL Server 2012 onwards) 
• Last Date and time SQL Server service was started 
• Virtual Machine Type description (not reliable) (virtual_machine_type_desc – SQL 2008 R2 onwards)

The cpu_count returns the number of logical CPUs and hyperthread_ratio returns ratio between physical and logical CPUs. I find the hyperthread_ratio to be confusing as it does not tell whether these are the actual hyper threaded cores or the physical cores. So I can never find out which processor is the server using. The physical_memory_kb tells the amount of RAM in my server and how much of it is committed using committed_kb. 

The sqlserver_start_time is useful to find since what date and time SQL Server has been running.

When I run the query on my machine, this is what I get:

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook