sql 2005 dmv: Query OS information

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.

select cpu_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


This entry was posted in SQL server 2005. Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s