Get SQL Server Hardware Information using sys.dm_os_sys_info

Here is the useful information.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.

NOTE : to get the last time server was rebooted information you need run the command from run … net statistics server.

PS: This is not my code , appreciate handwork of fellow DBA.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s