Get your server hardware information via DM_OS_SYS_INFO in SQL Server 2005/2008/Denali

DMV (Dynamic Management View) is very powerful tool given my Microsoft in SQL Server 2005+ version; you can get much crucial information from it. Today I am going to show you “sys.dm_os_sys_info” which will reveal some of the hardware information of the server which you are using for your SQL Server instance. 

SELECT

      cpu_count as Total_Logical_CPU_In_Server

      ,HyperThread_Ratio

      ,cpu_count/HyperThread_Ratio as physical_CPU_In_Server

      --converting bytes of physical memory to MB

      ,(physical_memory_in_bytes/1024)/1024 as Total_Memory_Of_Server_In_MB

      ,(Virtual_memory_in_bytes/1024)/1024 as Current_Usage_Of_Memory_SQLServer_In_MB

      --following fields will work only in SQL Server 2008+

      --if you are using SQL Server 2005, comment following field.

      ,sqlserver_start_time

     

FROM

      sys.dm_os_sys_info

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”