sys.dm_os_sys_info - Usage & Examples

sys.dm_os_sys_info exposes internal SQL Server OS layer information gathered during instance startup and maintained by the SQL OS (SQLOS) component. The DMV queries the Host Extension Layer which interfaces between SQL Server and the underlying Windows operating system.

Key internal mechanisms expose different data points: the Resource Monitor tracks physical and virtual memory through Windows APIs, the Scheduler Manager reports logical CPU counts via NUMA topology detection, and the Service Control Manager provides process and service timing information. The sql_memory_model column reflects whether SQL Server operates under conventional memory management or Large Pages, determined during Buffer Pool initialization.

Version-specific behaviors include SQL Server 2017 introducing the host_platform and container_type columns to support Linux and Docker deployments. SQL Server 2017 also added Linux-specific NUMA node detection logic. SQL Server 2019 enhanced the virtual_machine_type detection to properly identify Azure VMs and hypervisor environments. SQL Server 2022 refined memory reporting for contained availability groups and introduced improved container resource limit detection.

The DMV reflects static configuration determined at startup, with only cpu_count potentially changing during online CPU hot-add operations on Enterprise Edition. Memory values remain constant unless the instance restarts or max server memory settings change, triggering Buffer Pool reconfiguration.

-- Core system configuration baseline
SELECT 
    cpu_count,
    hyperthread_ratio,
    physical_memory_kb / 1024 AS physical_memory_mb,
    virtual_memory_kb / 1024 AS virtual_memory_mb,
    committed_kb / 1024 AS committed_memory_mb,
    committed_target_kb / 1024 AS committed_target_mb,
    visible_target_kb / 1024 AS visible_target_mb,
    sql_memory_model_desc
FROM sys.dm_os_sys_info;

-- CPU and NUMA topology analysis
SELECT 
    cpu_count AS logical_cpus,
    hyperthread_ratio,
    cpu_count / hyperthread_ratio AS physical_cores,
    socket_count,
    cores_per_socket,
    numa_node_count
FROM sys.dm_os_sys_info;

-- Memory pressure and allocation analysis  
SELECT 
    physical_memory_kb / 1024 AS total_physical_mb,
    committed_kb / 1024 AS sql_committed_mb,
    committed_target_kb / 1024 AS sql_target_mb,
    CAST((committed_kb * 100.0 / committed_target_kb) AS decimal(5,2)) AS memory_utilization_pct,
    sql_memory_model_desc,
    virtual_machine_type_desc
FROM sys.dm_os_sys_info;

-- Service and process identification
-- host_platform and container_type_desc are available in SQL Server 2017+
SELECT 
    sqlserver_start_time,
    ms_ticks / 1000 AS uptime_seconds,
    process_id,
    host_platform,
    container_type_desc
FROM sys.dm_os_sys_info;

-- Cross-reference with max server memory settings
SELECT 
    si.physical_memory_kb / 1024 AS total_physical_mb,
    si.committed_target_kb / 1024 AS current_target_mb,
    CAST(c.value_in_use AS int) AS max_server_memory_mb,
    CASE 
        WHEN CAST(c.value_in_use AS int) = 2147483647 THEN 'Default (unlimited)'
        ELSE 'Configured'
    END AS max_memory_status
FROM sys.dm_os_sys_info si
CROSS JOIN sys.configurations c
WHERE c.name = 'max server memory (MB)';

-- Validate CPU configuration against best practices
-- Identifies potential MAXDOP and parallelism issues
DECLARE @RecommendedMAXDOP int;

SELECT 
    @RecommendedMAXDOP = CASE 
        WHEN cpu_count / hyperthread_ratio <= 8 THEN cpu_count / hyperthread_ratio
        ELSE 8 
    END
FROM sys.dm_os_sys_info;

SELECT 
    'Current MAXDOP: ' + CAST(value_in_use AS varchar(10)) + 
    ', Recommended: ' + CAST(@RecommendedMAXDOP AS varchar(10)) AS maxdop_analysis
FROM sys.configurations 
WHERE name = 'max degree of parallelism';

-- WARNING: Test MAXDOP changes during maintenance windows
-- Changing MAXDOP affects query execution plans immediately

-- Memory configuration validation and adjustment recommendations
-- Calculates optimal max server memory based on system resources
DECLARE @OptimalMaxMemory int;
DECLARE @CurrentMaxMemory int;

SELECT 
    @OptimalMaxMemory = CASE 
        -- Reserve 4GB for OS on systems with >16GB, 25% on smaller systems
        WHEN physical_memory_kb / 1024 > 16384 THEN (physical_memory_kb / 1024) - 4096
        ELSE (physical_memory_kb / 1024) * 0.75
    END,
    @CurrentMaxMemory = (SELECT CAST(value_in_use AS int) FROM sys.configurations WHERE name = 'max server memory (MB)')
FROM sys.dm_os_sys_info;

SELECT 
    'Current Max Memory: ' + CAST(@CurrentMaxMemory AS varchar(10)) + ' MB' +
    ', Recommended: ' + CAST(@OptimalMaxMemory AS varchar(10)) + ' MB' AS memory_analysis;

-- Uncomment to apply change after testing
-- EXEC sp_configure 'max server memory (MB)', @OptimalMaxMemory;
-- RECONFIGURE;

-- Virtual machine and container resource validation
-- Identifies potential resource constraint issues in virtualized environments
SELECT 
    CASE virtual_machine_type
        WHEN 1 THEN 'WARNING: Running in virtual machine - verify CPU and memory reservations'
        ELSE 'Physical hardware detected'
    END AS vm_warning,
    CASE container_type  
        WHEN 1 THEN 'Container detected - validate resource limits with docker stats or kubectl'
        ELSE 'Non-containerized deployment'
    END AS container_analysis,
    cpu_count,
    physical_memory_kb / 1024 AS available_memory_mb
FROM sys.dm_os_sys_info;

-- NUMA configuration analysis for multi-socket systems
-- Identifies NUMA-related performance optimization opportunities
SELECT 
    numa_node_count,
    socket_count,  
    cpu_count,
    CASE 
        WHEN numa_node_count > 1 AND socket_count > 1 THEN 
            'Multi-NUMA system - verify TCP port assignment and connection distribution'
        WHEN numa_node_count = 1 AND socket_count > 1 THEN
            'WARNING: Multiple sockets with single NUMA node may indicate configuration issue'
        ELSE 'Single NUMA node configuration'
    END AS numa_analysis
FROM sys.dm_os_sys_info;

-- Consider enabling NUMA node affinity for multi-socket systems
-- Review connection string configurations for NUMA-aware applications

Configure max server memory explicitly rather than accepting SQL Server's default unlimited setting. Calculate target memory as total physical RAM minus 4GB for OS overhead on servers with 16GB+ RAM, or 75% of total RAM on smaller systems. Monitor committed_target_kb regularly to ensure SQL Server operates within intended memory boundaries.

Implement automated monitoring of cpu_count changes on Enterprise Edition systems supporting CPU hot-add operations. CPU topology changes require MAXDOP recalculation and potentially different parallelism cost threshold settings. Document baseline cpu_count, socket_count, and numa_node_count values immediately after instance deployment.

For virtualized environments, establish resource monitoring comparing physical_memory_kb against VM memory allocations and cpu_count against vCPU assignments. Configure VM memory reservations to prevent host memory overcommit scenarios. Container deployments require memory limit validation through container orchestration platforms.

Monitor sql_memory_model changes indicating Large Pages configuration modifications. Large Pages require SQL Server service account Lock Pages in Memory privilege and appropriate startup parameter configuration. Track sqlserver_start_time to identify unexpected service restarts requiring root cause analysis.

Establish baseline documentation capturing initial sys.dm_os_sys_info output for capacity planning and performance troubleshooting reference. Include this DMV output in standard SQL Server deployment runbooks and disaster recovery documentation.