SQL upit o CPU/RAM koje baza koristi

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.

This entry was posted in Windows and tagged , , . Bookmark the permalink.

Comments are closed.