Before tuning, I want the server's identity and visible resources on one page. Server hardware facts provide that baseline without opening a separate system utility. They describe resources SQL Server can see, not proof that the workload has enough capacity.
Identify the Engine Before the Hardware
Record the instance, version, edition, and capture time together. A tuning discussion can involve several similarly named servers. Hardware measurements without that identity are easy to attach to the wrong environment. Keep the database context too, even though these particular resource views describe the server.
I capture the identity first and retain the result with the performance notes. That removes guesswork when somebody asks whether the sample came from production or a test instance. A screenshot labeled server facts is less useful when nobody remembers which server supplied it.
SELECT SYSDATETIMEOFFSET() AS CapturedAt,
@@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion,
SERVERPROPERTY('EngineEdition') AS EngineEdition;These properties describe the connected engine. They do not identify the underlying storage model or physical host. Developer edition also does not mean the machine has the same resources as another Developer installation. Treat engine identity and resource inventory as separate parts of the baseline.
Capture the Server Hardware Facts SQL Server Sees
Use sys.dm_os_sys_info for the visible logical processors, topology fields, and memory values. Socket_count and cores_per_socket require SQL Server 2016 SP2 or later. The query targets current supported SQL Server installations with those columns available.
SELECT cpu_count, hyperthread_ratio, socket_count,
cores_per_socket, physical_memory_kb,
committed_target_kb, committed_kb,
sqlserver_start_time, virtual_machine_type_desc
FROM sys.dm_os_sys_info;Cpu_count describes visible logical processors. Hyperthread_ratio is a topology field, not a universal recipe for deriving licensing or physical host details. Virtualized environments can present a topology chosen by their configuration. Do not multiply and divide these values into a licensing claim.
Physical_memory_kb describes memory visible to the environment. Committed_kb and committed_target_kb describe SQL Server memory-manager values, not all memory consumed by every Windows process. A target is also not a promise that the operating system can supply that amount immediately. Keep the original units in the saved record.
Inspect the Node Layout
NUMA layout affects where processors and memory are grouped. SQL Server exposes nodes through sys.dm_os_nodes. Their memory_node_id helps relate scheduler nodes to memory nodes. The internal node has a special identifier and should not be counted as a normal workload node.
SELECT node_id, node_state_desc, memory_node_id,
processor_group, cpu_count, online_scheduler_count
FROM sys.dm_os_nodes
WHERE node_id <> 64
ORDER BY node_id;
SELECT memory_node_id, COUNT(*) AS SchedulerNodes,
SUM(online_scheduler_count) AS OnlineSchedulers
FROM sys.dm_os_nodes
WHERE node_id <> 64 AND node_state_desc LIKE 'ONLINE%'
GROUP BY memory_node_id;Software NUMA can divide scheduler groups without creating additional physical memory nodes. Therefore the number of node rows is not automatically the number of physical NUMA nodes. Inspect the relationship rather than counting rows and calling it hardware topology.
I compare online schedulers with the visible CPU inventory before discussing processor use. Affinity settings, edition limits, and offline schedulers deserve separate review when the counts disagree. Do not change affinity based only on this inventory. A topology question needs workload and administrator context.
Save Server Hardware Facts in a Dated Table
A stored capture makes later comparisons possible. Create the following table in an approved administrative database, not master by accident. The example uses a fresh name and does not replace an existing inventory. Verify the destination before creating it.
CREATE TABLE dbo.ServerHardwareCapture
(
CaptureID bigint IDENTITY PRIMARY KEY,
CapturedAt datetimeoffset(7) NOT NULL,
ServerName nvarchar(128) NULL,
Edition nvarchar(128) NULL,
ProductVersion nvarchar(128) NULL,
CpuCount int NOT NULL,
HyperthreadRatio int NOT NULL,
SocketCount int NOT NULL,
CoresPerSocket int NOT NULL,
PhysicalMemoryKB bigint NOT NULL,
CommittedTargetKB bigint NOT NULL,
SqlStartTime datetime NOT NULL
);
INSERT dbo.ServerHardwareCapture
(CapturedAt,ServerName,Edition,ProductVersion,CpuCount,
HyperthreadRatio,SocketCount,CoresPerSocket,PhysicalMemoryKB,
CommittedTargetKB,SqlStartTime)
SELECT SYSDATETIMEOFFSET(),CONVERT(nvarchar(128),@@SERVERNAME),
CONVERT(nvarchar(128),SERVERPROPERTY('Edition')),
CONVERT(nvarchar(128),SERVERPROPERTY('ProductVersion')),
cpu_count,hyperthread_ratio,socket_count,cores_per_socket,
physical_memory_kb,committed_target_kb,sqlserver_start_time
FROM sys.dm_os_sys_info;Save node rows under the capture identifier in a companion table when topology history matters. Do not collapse them into one invented physical-node count. The capture time and SQL startup time belong together because many performance counters reset at startup.
Read Server Hardware Facts Without Overclaiming
Display memory in convenient units while retaining the raw kilobytes. Label the conversion explicitly. Dividing by 1024 twice yields gibibyte-scale values, even when a report casually calls them gigabytes. Consistent labels prevent small unit differences from becoming an apparent hardware change.
SELECT TOP (20) CapturedAt, ServerName, Edition,
CpuCount, SocketCount, CoresPerSocket,
CAST(PhysicalMemoryKB/1048576.0 AS decimal(18,2)) AS VisibleMemoryGiB,
CAST(CommittedTargetKB/1048576.0 AS decimal(18,2)) AS SqlTargetGiB,
SqlStartTime
FROM dbo.ServerHardwareCapture
ORDER BY CaptureID DESC;A virtual machine can conceal the host's total memory, processor model, and competing workload. The values describe the allocation and topology presented to the guest. Another workload on the host can affect performance without changing this snapshot. Obtain host evidence separately when that is the suspected bottleneck.
The same caution applies to storage. None of these columns measures read latency, write latency, throughput, or storage queueing. A larger processor count cannot answer a storage question. Add targeted measurements only after the workload points toward that resource.
Connect Capacity to the Workload
A resource inventory sets context for CPU pressure, memory grants, and concurrency. It does not establish the right MAXDOP, memory setting, or server size. Those decisions depend on workload shape, concurrent demand, and the rest of the environment.
Which observed symptom would an additional resource solve? That question keeps sizing recommendations tied to evidence. Compare waits, execution plans, resource utilization, and application demand over a representative period. One busy minute or one idle snapshot does not describe the entire business cycle.
Record planned resource changes beside the captures. Otherwise a processor increase or memory reallocation can appear as an unexplained tuning improvement. Keep configuration changes separate from hardware changes so later reviewers can understand which intervention caused the observed result.
Keep Inventory Collection Small
These queries are compact, but access still requires appropriate server-state permissions. On SQL Server 2022 and later, use the documented performance-state permissions for the relevant views. Coordinate collection with an administrator rather than granting broad privileges to every application login.
Capture at the beginning of a tuning session and after a confirmed resource change. Preserve previous rows instead of overwriting the latest values. That provides a useful timeline without collecting an unnecessary high-frequency stream. The goal is a trustworthy baseline that explains the environment when you return to a plan or counter report later.
Include the visible environment's allocation changes in the inventory notes. A virtual machine can be resized between captures, and the database engine can restart during that change. Compare startup times before comparing cumulative counters. If a report spans both environments, mark the boundary explicitly rather than interpreting a counter reset as a dramatic reduction in resource demand. Server hardware facts describe the visible environment at capture time. Keep server hardware facts beside the workload measurements rather than converting them directly into a sizing recommendation.
Related reading on this blog: sys.dm_os_sys_info and Lock Pages in Memory and Get CPU Details: SQL in Sixty Seconds #164.
A server hardware snapshot is not a sizing verdict, it is a dated baseline of the resources and topology SQL Server can see.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
