sys.dm_os_sys_info (Transact-SQL)
Returns information about the computer on which SQL Server is installed including the resources available to and consumed by SQL Server.
Column name |
Data type |
Description |
|---|---|---|
cpu_ticks |
bigint |
Current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number. |
ms_ticks |
bigint |
Number of milliseconds since the computer was started. |
cpu_count |
int |
Number of logical CPUs on the system. |
hyperthread_ratio |
int |
Ratio of the number of logical or physical cores that are exposed by one physical processor package. |
physical_memory_in_bytes |
bigint |
Amount of physical memory available. |
virtual_memory_in_bytes |
bigint |
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. |
bpool_committed |
int |
Number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool. |
bpool_commit_target |
int |
Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink. |
bpool_visible |
int |
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. |
stack_size_in_bytes |
int |
Size of the call stack for each thread created by SQL Server. |
os_quantum |
bigint |
Quantum for a non-preemptive task, measured in milliseconds. Quantum (in seconds) = os_quantum / CPU clock speed. |
os_error_mode |
int |
Error mode for the SQL Server process. |
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 priority boost Option. |
max_workers_count |
int |
Maximum number of workers that can be created. |
scheduler_count |
int |
Number of user schedulers configured in the SQL Server process. |
scheduler_total_count |
int |
Total number of schedulers in SQL Server. |
deadlock_monitor_serial_number |
int |
ID of the current deadlock monitor sequence. |
sqlserver_start_time_ms_ticks |
bigint |
ms_tick number when SQL Server last started. Compare to the current ms_ticks column. |
sqlserver_start_time |
datetime |
Date and time that SQL Server last started. |
affinity_type |
int |
Describes the affinity type that SQL Server uses.
ValueDescription
1Manual
2Auto
|
affinity_type_description |
varchar(60) |
Describes the value in the affinity_type column. For more information, see ALTER SERVER CONFIGURATION (Transact-SQL).
ValueDescription
MANUALAffinity has been set for at least one CPU.
AUTOSQL Server moves threads between CPUs as required.
|
process_kernel_time_ms |
bigint |
Total time in milliseconds spent by all SQL Server threads in kernel mode. .gif "Note") Note
This value can be larger than a single processor clock because it includes the time for all processors on the server.
|
process_user_time_ms |
bigint |
Total time in milliseconds spent by all SQL Server threads in user mode. Note
This value can be larger than a single processor clock because it includes the time for all processors on the server.
|
time_source |
int |
Describes the API that SQL Server is using to retrieve wall clock time. |
time_source_desc |
nvarchar(60) |
Describes the value in the time_source column.
ValueDescription
QUERY_PERFORMANCE_COUNTERThe QueryPerformanceCounter API retrieves wall clock time.
MULTIMEDIA_TIMERThe multimedia timer API that retrieves wall clock time.
|
virtual_machine_type |
int |
Indicates whether SQL Server is running in a virtualized environment. Not nullable.
ValueDescription
0NONE
1HYPERVISOR
2Other
|
virtual_machine_type_desc |
nvarchar(60) |
Describes the virtual_machine_type column. Not nullable.
ValueDescription
NoneSQL Server is not running inside a virtual machine.
HYPERVISORSQL 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.
OtherSQL Server is running inside a virtual machine that does not employ hardware assistant such as Microsoft Virtual PC.
|
Permissions
Requires VIEW SERVER STATE permission on the server.