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.