Upit koji se koristi zavisi od verzije SQL servera koji se pita.
Upit UVEK treba da se radi nad “master” bazom, da bi se dobili podaci za ceo server :
sql-cpu1
sql-cpu2
U zavisnosti od toga koja je verzija SQL servera u pitanju, dobiće se različiti podaci iz sys.dm_os_sys_info (link) :
SQL 2005 :
2289_columns_2005
SQL 2008 (link) :
2289_columns_2008
Značenje pojedinih stavki može se naći ovde.
Neke meni važnije vrednosti su :
cpu_count – Specifies the number of logical CPUs on the system.
hyperthread_ratio – Specifies the ratio of the number of logical or physical cores that are exposed by one physical processor package.
physical_memory_in_bytes – Specifies the total amount of physical memory on the machine.
virtual_memory_in_bytes – Specifies the total amount of virtual address space available to the process in user mode.
bpool_commited – Represents the committed memory in the memory manager. Does not include reserved memory in the memory manager.
bpool_commit_target – Represents the amount of memory, that can be consumed by SQL Server memory manager.

a) Generalni upit koji daje sve podatke iz date sys.dm_os_sys_info tabele (za server SQL 2008) :
select * from sys.dm_os_sys_info;
b) I kako moj upit na kraju izgleda :
select
cpu_count,
hyperthread_ratio,
physical_memory_in_bytes,
virtual_memory_in_bytes,
bpool_committed,
bpool_commit_target
from sys.dm_os_sys_info;
c) A pošto je nezgodno čitati podatke u bajtovima :
select
cpu_count,
hyperthread_ratio,
[physical_memory_in_bytes]/1024/1024 AS [physical memory MB],
[virtual_memory_in_bytes]/1024/1024 AS [virtyal memory MB],
[bpool_committed]*8/1024 AS [bpool commited MB],
[bpool_commit_target]*8/1024 AS [bpool commited target MB]
from sys.dm_os_sys_info;

SQL 2012 (link) :
Column Name in 2008 -> Column Name in 2012
physical_memory_in_bytes -> physical_memory_kb
bpool_commit_target -> committed_target_kb
bpool_visible -> visible_target_kb
virtual_memory_in_bytes -> virtual_memory_kb
bpool_commited -> committed_kb

d) Kako dobiti odjednom sve podatke o verziji i sličnim podacima :
select @@version;
Važi za (bar koliko sam videla) sve verzije SQL servera.

By velda