SQL server 2005 exposes server states information by dynamic management views (DMV). In SQL 2000, it’s not easy to get server information. This can be easily done in SQL 2005 by means of an os DMV view: sys.dm_os_sys_info
The following query gives the logical and physical number of CPU, physical RAM in GB, user mode address space in GB and the CPU speed GHz.
selectcpu_count as LogicalCPUNumber, cpu_count/hyperthread_ratio as PhyicalCPUNumber, cast(physical_memory_in_bytes/1024.0/1024.0/1024.0 as numeric(10,2)) as PhysicalRAMinGB, cast(virtual_memory_in_bytes/1024.0/1024.0/1024.0 as numeric(10,2)) as VASinGB, CAST(cpu_ticks_in_ms/1000.0/1000.0 as numeric(10,2)) CPUGHz
From the user mode address space, we can know the /3GB switch is enabed or not in windows load.ini file. If it’s not enabled, in win32 system, the VASinGB is 2, otherwise is 3 if the switch is /3GB