Export (0) Print
Expand All
Expand Minimize

sys.dm_os_sys_info (Transact-SQL)

Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.

Column name

Data type

Description

cpu_ticks

bigint

Specifies the current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number. Not nullable.

ms_ticks

bigint

Specifies the number of milliseconds since the computer started. Not nullable.

cpu_count

int

Specifies the number of logical CPUs on the system. Not nullable.

hyperthread_ratio

int

Specifies the ratio of the number of logical or physical cores that are exposed by one physical processor package. Not nullable.

physical_memory_kb

bigint

Specifies the total amount of physical memory on the machine. Not nullable.

virtual_memory_kb

bigint

Specifies the total amount of virtual address space available to the process in user mode. Not nullable.

committed_kb

int

Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable.

committed_target_kb

int

Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:

  • the current state of the system including its load

  • the memory requested by current processes

  • the amount of memory installed on the computer

  • configuration parameters

If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory. Not nullable.

visible_target_kb

int

Is the same as committed_target_kb. Not nullable.

stack_size_in_bytes

int

Specifies the size of the call stack for each thread created by SQL Server. Not nullable.

os_quantum

bigint

Represents the Quantum for a non-preemptive task, measured in milliseconds. Quantum (in seconds) = os_quantum / CPU clock speed. Not nullable.

os_error_mode

int

Specifies the error mode for the SQL Server process. Not nullable.

os_priority_class

int

Specifies the priority class for the SQL Server process. Nullable.

32 = Normal (Error log will say SQL Server is starting at normal priority base (=7).)

128 = High (Error log will say SQL Server is running at high priority base. (=13).)

For more information, see Configure the priority boost Server Configuration Option.

max_workers_count

int

Represents the maximum number of workers that can be created. Not nullable.

scheduler_count

int

Represents the number of user schedulers configured in the SQL Server process. Not nullable.

scheduler_total_count

int

Represents the total number of schedulers in SQL Server. Not nullable.

deadlock_monitor_serial_number

int

Specifies the ID of the current deadlock monitor sequence. Not nullable.

sqlserver_start_time_ms_ticks

bigint

Represents the ms_tick number when SQL Server last started. Compare to the current ms_ticks column. Not nullable.

sqlserver_start_time

datetime

Specifies the date and time SQL Server last started. Not nullable.

affinity_type

int

Specifies the type of server CPU process affinity currently in use. Not nullable. For more information, see ALTER SERVER CONFIGURATION (Transact-SQL).

1 = MANUAL

2 = AUTO

affinity_type_desc

varchar(60)

Describes the affinity_type column. Not nullable.

MANUAL = affinity has been set for at least one CPU.

AUTO = SQL Server can freely move threads between CPUs.

process_kernel_time_ms

bigint

Total time in milliseconds spent by all SQL Server threads in kernel mode. This value can be larger than a single processor clock because it includes the time for all processors on the server. Not nullable.

process_user_time_ms

bigint

Total time in milliseconds spent by all SQL Server threads in user mode. This value can be larger than a single processor clock because it includes the time for all processors on the server. Not nullable.

time_source

int

Indicates the API that SQL Server is using to retrieve wall clock time. Not nullable.

0 = QUERY_PERFORMANCE_COUNTER

1 = MULTIMEDIA_TIMER

time_source_desc

nvarchar(60)

Describes the time_source column. Not nullable.

QUERY_PERFORMANCE_COUNTER = the QueryPerformanceCounter API retrieves wall clock time.

MULTIMEDIA_TIMER = The multimedia timer API that retrieves wall clock time.

virtual_machine_type

int

Indicates whether SQL Server is running in a virtualized environment. Not nullable.

0 = NONE

1 = HYPERVISOR

2 = OTHER

virtual_machine_type_desc

nvarchar(60)

Describes the virtual_machine_type column. Not nullable.

NO = SQL Server is not running inside a virtual machine.

HYPERVISOR = SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.

OTHER = SQL Server is running inside a virtual machine that does not employ hardware assistant such as Microsoft Virtual PC.

Requires VIEW SERVER STATE permission on the server.

In SQL Server 2012, the following columns have been renamed.

Previous Column Name

New Column Name

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

Community Additions

ADD
Show:
© 2014 Microsoft