Summary: Use this T-SQL script to extract critical hardware metadata from SQL Server, including CPU core counts, physical memory, NUMA configuration, and virtualization type.
Retrieve SQL Server Hardware and System Information
This query provides valuable hardware information about your database server, which is essential for licensing audits, performance tuning, and capacity planning.
Pro Tip: A 'HYPERVISOR' value for 'virtual_machine_type_desc' doesn't automatically confirm SQL Server is running inside a virtual machine. This merely indicates that a hypervisor is present on the host machine (which could also mean the host has Hyper-V enabled).
Hardware Configuration Script
SELECT
cpu_count AS [Logical CPU Count],
scheduler_count,
(socket_count * cores_per_socket) AS [Physical Core Count],
socket_count AS [Socket Count],
cores_per_socket,
numa_node_count,
physical_memory_kb / 1024 AS [Physical Memory (MB)],
max_workers_count AS [Max Workers Count],
affinity_type_desc AS [Affinity Type],
sqlserver_start_time AS [SQL Server Start Time],
DATEDIFF(hour, sqlserver_start_time, GETDATE()) AS [Up Time (hrs)],
virtual_machine_type_desc AS [Virtual Machine Type],
softnuma_configuration_desc AS [Soft-NUMA Config], -- SQL Server 2016+
sql_memory_model_desc, -- SQL Server 2016 SP1+
container_type_desc -- SQL Server 2019+
FROM sys.dm_os_sys_info WITH (NOLOCK)
OPTION (RECOMPILE);
Understanding the Results
By querying sys.dm_os_sys_info, you get a snapshot of the resources SQL Server "sees." This is particularly useful for identifying:
- Memory Model: Whether SQL Server is using conventional memory or "Locked Pages in Memory."
- Core Density: The ratio of physical cores to sockets.
- Soft-NUMA: Whether SQL Server is automatically managing NUMA nodes to improve performance on high-core-count machines.
Further Reading:
Need to tune your SQL Server performance? Check out our other scripts for monitoring CPU and memory usage!
