sys.dm_os_sys_info (Transact-SQL)

 

Updated: August 1, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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

NOTE: To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_sys_info.

Column nameData typeDescription and version-specific notes
cpu_ticksbigintSpecifies the current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number. Not nullable.
ms_ticksbigintSpecifies the number of milliseconds since the computer started. Not nullable.
cpu_countintSpecifies the number of logical CPUs on the system. Not nullable.
hyperthread_ratiointSpecifies the ratio of the number of logical or physical cores that are exposed by one physical processor package. Not nullable.
physical_memory_in_bytesbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Specifies the total amount of physical memory on the machine. Not nullable.
physical_memory_kbbigintApplies to: SQL Server 2012 through SQL Server 2016.

Specifies the total amount of physical memory on the machine. Not nullable.
virtual_memory_in_bytesbigintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Amount of virtual memory available to the process in user mode. This can be used to determine whether SQL Server was started by using a 3-GB switch.
virtual_memory_kbbigintApplies to: SQL Server 2012 through SQL Server 2016.

Specifies the total amount of virtual address space available to the process in user mode. Not nullable.
bpool_commitedintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable.
committed_kbintApplies to: SQL Server 2012 through SQL Server 2016.

Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable.
bpool_commit_targetintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager.
committed_target_kbintApplies to: SQL Server 2012 through SQL Server 2016.

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.
bpool_visibleintApplies to: SQL Server 2008 through SQL Server 2008 R2.

Number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space. When not using the Address Windowing Extensions (AWE), when the buffer pool has obtained its memory target (bpool_committed = bpool_commit_target), the value of bpool_visible equals the value of bpool_committed.When using AWE on a 32-bit version of SQL Server, bpool_visible represents the size of the AWE mapping window used to access physical memory allocated by the buffer pool. The size of this mapping window is bound by the process address space and, therefore, the visible amount will be smaller than the committed amount, and can be further reduced by internal components consuming memory for purposes other than database pages. If the value of bpool_visible is too low, you might receive out of memory errors.
visible_target_kbintApplies to: SQL Server 2012 through SQL Server 2016.

Is the same as committed_target_kb. Not nullable.
stack_size_in_bytesintSpecifies the size of the call stack for each thread created by SQL Server. Not nullable.
os_quantumbigintRepresents the Quantum for a non-preemptive task, measured in milliseconds. Quantum (in seconds) = os_quantum / CPU clock speed. Not nullable.
os_error_modeintSpecifies the error mode for the SQL Server process. Not nullable.
os_priority_classintSpecifies 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_countintRepresents the maximum number of workers that can be created. Not nullable.
scheduler_countintRepresents the number of user schedulers configured in the SQL Server process. Not nullable.
scheduler_total_countintRepresents the total number of schedulers in SQL Server. Not nullable.
deadlock_monitor_serial_numberintSpecifies the ID of the current deadlock monitor sequence. Not nullable.
sqlserver_start_time_ms_ticksbigintRepresents the ms_tick number when SQL Server last started. Compare to the current ms_ticks column. Not nullable.
sqlserver_start_timedatetimeSpecifies the date and time SQL Server last started. Not nullable.
affinity_typeintApplies to: SQL Server 2008 R2 through SQL Server 2016.

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_descvarchar(60)Applies to: SQL Server 2008 R2 through SQL Server 2016.

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_msbigintApplies to: SQL Server 2008 R2 through SQL Server 2016.

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_msbigintApplies to: SQL Server 2008 R2 through SQL Server 2016.

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_sourceintApplies to: SQL Server 2008 R2 through SQL Server 2016.

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

0 = QUERY_PERFORMANCE_COUNTER

1 = MULTIMEDIA_TIMER
time_source_descnvarchar(60)Applies to: SQL Server 2008 R2 through SQL Server 2016.

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_typeintApplies to: SQL Server 2008 R2 through SQL Server 2016.

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

0 = NONE

1 = HYPERVISOR

2 = OTHER
virtual_machine_type_descnvarchar(60)Applies to: SQL Server 2008 R2 through SQL Server 2016.

Describes the virtual_machine_type column. Not nullable.

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

HYPERVISOR = SQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. When the Hyper_V role is installed, the hypervisor hosts the OS, so an instance running on the host OS is running in the hypervisor.

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

Specifies the way NUMA nodes are configured. Not nullable.

0 = OFF indicates hardware default

1 = Automated soft-NUMA

2 = Manual soft-NUMA via registry
soft_numa_descnvarchar(60)Applies to: SQL Server 2016 through SQL Server 2016.

OFF = Soft-NUMA feature is OFF

ON = SQL Server automatically determines the NUMA node sizes for Soft-NUMA

MANUAL = Manually configured soft-NUMA
pdw_node_idintApplies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

On SQL Server requires VIEW SERVER STATE permission on the server.

On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.

Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)

Community Additions

ADD
Show: