| title | sys.dm_os_sys_info (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 04/24/2018 | ||||
| ms.prod | sql | ||||
| ms.prod_service | database-engine, sql-data-warehouse, pdw | ||||
| ms.reviewer | |||||
| ms.suite | sql | ||||
| ms.technology | system-objects | ||||
| ms.tgt_pltfrm | |||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | 20f6bc9c-839a-4fa4-b3f3-a6c47d1b69af | ||||
| caps.latest.revision | 57 | ||||
| author | stevestein | ||||
| ms.author | sstein | ||||
| manager | craigg | ||||
| monikerRange | >= aps-pdw-2016 || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions |
[!INCLUDEtsql-appliesto-ss2008-xxxx-asdw-pdw-md]
Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by [!INCLUDEssNoVersion_md].
NOTE: To call this from [!INCLUDEssSDWfull] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_sys_info.
| Column name | Data type | Description and version-specific notes |
|---|---|---|
| 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_in_bytes | bigint | Applies to: [!INCLUDEssKatmai] through [!INCLUDEssKilimanjaro]. Specifies the total amount of physical memory on the machine. Not nullable. |
| physical_memory_kb | bigint | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. Specifies the total amount of physical memory on the machine. Not nullable. |
| virtual_memory_in_bytes | bigint | Applies to: [!INCLUDEssKatmai] through [!INCLUDEssKilimanjaro]. 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_kb | bigint | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. Specifies the total amount of virtual address space available to the process in user mode. Not nullable. |
| bpool_commited | int | Applies to: [!INCLUDEssKatmai] through [!INCLUDEssKilimanjaro]. Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable. |
| committed_kb | int | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable. |
| bpool_commit_target | int | Applies to: [!INCLUDEssKatmai] through [!INCLUDEssKilimanjaro]. Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. |
| committed_target_kb | int | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. 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_visible | int | Applies to: [!INCLUDEssKatmai] through [!INCLUDEssKilimanjaro]. 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_kb | int | Applies to: [!INCLUDEssSQL11] through [!INCLUDEssCurrent]. 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 [!INCLUDEssNoVersion]. 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 [!INCLUDEssNoVersion] process. Not nullable. |
| os_priority_class | int | Specifies the priority class for the [!INCLUDEssNoVersion] process. Nullable. 32 = Normal (Error log will say [!INCLUDEssNoVersion] is starting at normal priority base (=7).) 128 = High (Error log will say [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] process. Not nullable. |
| scheduler_total_count | int | Represents the total number of schedulers in [!INCLUDEssNoVersion]. 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 [!INCLUDEssNoVersion] last started. Compare to the current ms_ticks column. Not nullable. |
| sqlserver_start_time | datetime | Specifies the date and time [!INCLUDEssNoVersion] last started. Not nullable. |
| affinity_type | int | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. 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) | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. Describes the affinity_type column. Not nullable. MANUAL = affinity has been set for at least one CPU. AUTO = [!INCLUDEssNoVersion] can freely move threads between CPUs. |
| process_kernel_time_ms | bigint | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. Total time in milliseconds spent by all [!INCLUDEssNoVersion] 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 | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. Total time in milliseconds spent by all [!INCLUDEssNoVersion] 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 | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. Indicates the API that [!INCLUDEssNoVersion] is using to retrieve wall clock time. Not nullable. 0 = QUERY_PERFORMANCE_COUNTER 1 = MULTIMEDIA_TIMER |
| time_source_desc | nvarchar(60) | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. 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 | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. Indicates whether [!INCLUDEssNoVersion] is running in a virtualized environment. Not nullable. 0 = NONE 1 = HYPERVISOR 2 = OTHER |
| virtual_machine_type_desc | nvarchar(60) | Applies to: [!INCLUDEssKilimanjaro] through [!INCLUDEssCurrent]. Describes the virtual_machine_type column. Not nullable. NONE = [!INCLUDEssNoVersion] is not running inside a virtual machine. HYPERVISOR = [!INCLUDEssNoVersion] 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 = [!INCLUDEssNoVersion] is running inside a virtual machine that does not employ hardware assistant such as Microsoft Virtual PC. |
| softnuma_configuration | int | Applies to: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. Specifies the way NUMA nodes are configured. Not nullable. 0 = OFF indicates hardware default 1 = Automated soft-NUMA 2 = Manual soft-NUMA via registry |
| softnuma_configuration_desc | nvarchar(60) | Applies to: [!INCLUDEssSQL15] through [!INCLUDEssCurrent]. OFF = Soft-NUMA feature is OFF ON = [!INCLUDEssNoVersion] automatically determines the NUMA node sizes for Soft-NUMA MANUAL = Manually configured soft-NUMA |
| process_physical_affinity | nvarchar(3072) | Applies to: Starting with [!INCLUDEssSQL17]. Information yet to come. |
| sql_memory_model | int | Applies to: [!INCLUDEsssql11] SP4, [!INCLUDEssSQL15] SP1 through [!INCLUDEssCurrent]. Specifies the memory model used by [!INCLUDEssNoVersion] to allocate memory. Not nullable. 1 = Conventional Memory Model 2 = Lock Pages in Memory 3 = Large Pages in Memory |
| sql_memory_model_desc | nvarchar(120) | Applies to: [!INCLUDEsssql11] SP4, [!INCLUDEssSQL15] SP1 through [!INCLUDEssCurrent]. Specifies the memory model used by [!INCLUDEssNoVersion] to allocate memory. Not nullable. CONVENTIONAL = [!INCLUDEssNoVersion] is using Conventional Memory model to allocate memory. This is default sql memory model when [!INCLUDEssNoVersion] service account does not have Lock Pages in Memory privileges during startup. LOCK_PAGES = [!INCLUDEssNoVersion] is using Lock Pages in Memory to allocate memory. This is the default sql memory manager when SQL Server service account possess Lock Pages in Memory privilege during SQL Server startup. LARGE_PAGES = [!INCLUDEssNoVersion] is using Large Pages in Memory to allocate memory. SQL Server uses Large Pages allocator to allocate memory only with Enterprise edition when SQL Server service account possess Lock Pages in Memory privilege during server startup and when Trace Flag 834 is turned ON. |
| pdw_node_id | int | Applies to: [!INCLUDEssSDWfull], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
| socket_count | int | Applies to: [!INCLUDEssSQL15] SP2 through [!INCLUDEssCurrent]. Specifies the number of processor sockets available on the system. |
| cores_per_socket | int | Applies to: [!INCLUDEssSQL15] SP2 through [!INCLUDEssCurrent]. Specifies the number of processors per socket available on the system. |
| numa_node_count | int | Applies to: [!INCLUDEssSQL15] SP2 through [!INCLUDEssCurrent]. Specifies the number of numa nodes available on the system. This column includes physical numa nodes as well as soft numa nodes. |
On [!INCLUDEssNoVersion_md], requires VIEW SERVER STATE permission.
On [!INCLUDEssSDS_md], requires the VIEW DATABASE STATE permission in the database.
Dynamic Management Views and Functions (Transact-SQL)
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)