sys.dm_os_sys_info (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
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 Synapse Analytics or Analytics Platform System (PDW), use the name sys.dm_pdw_nodes_os_sys_info. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.
| Column name | Data type | Description and version-specific notes |
|---|---|---|
cpu_ticks |
bigint | Specifies the current CPU check count. CPU ticks are obtained from the processor's RDTSC counter. It's 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. In Azure SQL Database, this column might return the number of logical CPUs on the machine hosting the database or elastic pool. To determine the number of logical CPUs available to the database or elastic pool, use the cpu_limit column in sys.dm_user_db_resource_governance. |
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: SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x). Specifies the total amount of physical memory on the machine. Not nullable. |
physical_memory_kb |
bigint | Applies to: SQL Server 2012 (11.x) and later versions. Specifies the total amount of physical memory on the machine. Not nullable. In Azure SQL Database, this column returns the total amount of physical memory on the machine hosting the database or elastic pool. To determine the amount of physical memory available to the database or elastic pool, use the process_memory_limit_mb column in sys.dm_os_job_object. |
virtual_memory_in_bytes |
bigint | Applies to: SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x). Amount of virtual memory available to the process in user mode. This value can be used to determine whether SQL Server was started by using a 3-GB switch. |
virtual_memory_kb |
bigint | Applies to: SQL Server 2012 (11.x) and later versions. Specifies the total amount of virtual address space available to the process in user mode. Not nullable. |
bpool_committed |
int | Applies to: SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x). Represents the committed memory in kilobytes (KB) in the memory manager. Doesn't include reserved memory in the memory manager. Not nullable. |
committed_kb |
bigint | Applies to: SQL Server 2012 (11.x) and later versions. Represents the committed memory in kilobytes (KB) in the memory manager. Doesn't include reserved memory in the memory manager. Not nullable. |
bpool_commit_target |
int | Applies to: SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x). Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. |
committed_target_kb |
bigint | Applies to: SQL Server 2012 (11.x) and later versions. Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using several 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 tries to obtain more memory. If committed_target_kb is smaller than committed_kb, the memory manager tries to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory. Not nullable. |
bpool_visible |
int | Applies to: SQL Server 2008 (10.0.x) and SQL Server 2008 R2 (10.50.x). 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 obtains 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 so the visible amount is smaller than the committed amount. This value 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 |
bigint | Applies to: SQL Server 2012 (11.x) and later versions. 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 says SQL Server is starting at normal priority base (7).128 = High. Error log says 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 local system date and time SQL Server last started. Not nullable. Information in many other SQL Server DMVs only includes activity since the last database engine startup. Use this column to find the last SQL Server Database Engine startup time. |
affinity_type |
int | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. Specifies the type of server CPU process affinity currently in use. Not nullable. For more information, see ALTER SERVER CONFIGURATION (Transact-SQL). 1 = MANUAL2 = AUTO |
affinity_type_desc |
nvarchar(60) | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. Describes the affinity_type column. Not nullable.MANUAL = affinity was set for at least one CPU.AUTO = SQL Server can freely move threads between CPUs. |
process_kernel_time_ms |
bigint | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. 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 | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. 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 | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. Indicates the API that SQL Server is using to retrieve wall clock time. Not nullable. 0 = QUERY_PERFORMANCE_COUNTER1 = MULTIMEDIA_TIMER |
time_source_desc |
nvarchar(60) | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. 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: SQL Server 2008 R2 (10.50.x) and later versions. Indicates whether SQL Server is running in a virtualized environment. Not nullable. 0 = NONE1 = HYPERVISOR2 = OTHER |
virtual_machine_type_desc |
nvarchar(60) | Applies to: SQL Server 2008 R2 (10.50.x) and later versions. Describes the virtual_machine_type column. Not nullable.NONE = SQL Server isn't running inside a virtual machine.HYPERVISOR = SQL Server is running inside a virtual machine hosted by an OS running hypervisor (a host OS that employs hardware-assisted virtualization).OTHER = SQL Server is running inside a virtual machine hosted by an OS that doesn't employ hardware assistant such as Microsoft Virtual PC. |
softnuma_configuration |
int | Applies to: SQL Server 2016 (13.x) and later versions. Specifies the way NUMA nodes are configured. Not nullable. 0 = OFF indicates hardware default1 = Automated soft-NUMA2 = Manual soft-NUMA via registry |
softnuma_configuration_desc |
nvarchar(60) | Applies to: SQL Server 2016 (13.x) and later versions.OFF = Soft-NUMA feature is offON = SQL Server automatically determines the NUMA node sizes for Soft-NUMAMANUAL = Manually configured soft-NUMA |
process_physical_affinity |
nvarchar(3072) | Applies to: Starting with SQL Server 2017 (14.x). Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. |
sql_memory_model |
int | Applies to: SQL Server 2012 (11.x) SP4, SQL Server 2016 (13.x) SP1, and later versions. Specifies the memory model used by SQL Server to allocate memory. Not nullable. 1 = Conventional memory model2 = Lock pages in memory3 = Large pages in memory |
sql_memory_model_desc |
nvarchar(60) | Applies to: SQL Server 2012 (11.x) SP4, SQL Server 2016 (13.x) SP1, and later versions. Specifies the memory model used by SQL Server to allocate memory. Not nullable. CONVENTIONAL = SQL Server is using Conventional Memory model to allocate memory. This is default Database Engine memory model when SQL Server service account doesn't have Lock Pages in Memory privileges during startup.LOCK_PAGES = SQL Server is using Lock Pages in Memory to allocate memory. This is the default Database Engine memory manager when the SQL Server service account has the "Lock pages in memory" privilege during SQL Server startup.LARGE_PAGES = SQL Server is using Large Pages in Memory to allocate memory. SQL Server uses the Large Pages allocator to allocate memory only with Enterprise edition when SQL Server service account has the "Lock pages in memory" privilege during server startup, and when Trace Flag 834 is turned on. |
pdw_node_id |
int | Applies to: Azure Synapse Analytics, Analytics Platform System (PDW) The identifier for the node that this distribution is on. |
socket_count |
int | Applies to: SQL Server 2016 (13.x) SP2 and later versions. Specifies the number of processor sockets available on the system. |
cores_per_socket |
int | Applies to: SQL Server 2016 (13.x) SP2 and later versions. Specifies the number of processors per socket available on the system. |
numa_node_count |
int | Applies to: SQL Server 2016 (13.x) SP2 and later versions. Specifies the number of NUMA nodes available on the system. This column includes physical NUMA nodes and soft NUMA nodes. |
container_type |
int | Applies to: SQL Server 2017 (14.x) and later versions. Specifies the type of container SQL Server is running inside. Not nullable. 0 (default) = NONE1 = LINUX CONTAINER2 = WINDOWS SERVER CONTAINER3 = HYPER-V CONTAINER |
container_type_desc |
nvarchar(60) | Applies to: SQL Server 2017 (14.x) and later versions. Describes the container_type column. Not nullable.NONE = SQL Server isn't running in a container.LINUX CONTAINER = SQL Server is running in a Linux container.WINDOWS SERVER CONTAINER = SQL Server is running in a Windows Server container.HYPER-V CONTAINER = SQL Server is running in a Hyper-V container. |
Permissions
On SQL Server 2019 (15.x) and earlier versions, and SQL Managed Instance, you require VIEW SERVER STATE permission.
On SQL Server 2022 (16.x) and later versions, you require VIEW SERVER PERFORMANCE STATE permission on the server.
On Azure SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.