DBA Scripts: Get OS and Hardware Info

---

What Is This Server Actually Running On

Before you can reason about performance, capacity, or whether a setting makes sense, you need the basics: how much CPU and memory the box actually has, what OS it’s running, and how long SQL Server has been up. It’s a small set of facts, but it’s the context every other diagnostic script assumes you already know.

This script returns all of it in one row: SQL Server edition and version, Windows release, logical and physical CPU counts, physical memory, and uptime, everything you’d otherwise have to gather from three or four different places.

---

Why OS and Hardware Info Matters

• Logical vs physical CPU count tells you whether hyperthreading is in play, which matters when comparing this server’s core count against licensing or against another server’s specs
• Physical memory is the ceiling every other memory setting (max server memory, buffer pool) has to respect, you can’t reason about memory configuration without knowing the real number
• sql_committed_mb next to physical_memory_gb gives a quick sanity check on how much of the box’s memory SQL Server is actually using right now
• Uptime matters for more than trivia, a recent restart can explain a suddenly-empty plan cache, a cleared buffer pool, or wait statistics that look artificially low

---

When to Run This Script

• First thing when inheriting or reviewing an unfamiliar server
• Before making any memory or CPU-related configuration change, to know the real ceiling you’re working within
• When something looks different than expected and you want to rule out a recent restart as the explanation
• As part of a routine health check, alongside other inventory scripts in this series

---

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-OsAndHardwareInfo
Category    : configuration-and-environment
Purpose     : Show OS version, hardware specs (CPU, RAM), and SQL Server uptime in one row.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-os-and-hardware-info/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    SERVERPROPERTY('MachineName') AS machine_name,
    SERVERPROPERTY('ServerName') AS server_name,
    SERVERPROPERTY('Edition') AS sql_edition,
    SERVERPROPERTY('ProductVersion') AS sql_version,
    SERVERPROPERTY('ProductLevel') AS sql_product_level,
    SERVERPROPERTY('ProductUpdateLevel') AS sql_cu_level,
    SERVERPROPERTY('IsClustered') AS is_clustered,
    wni.windows_release AS os_release,
    wni.windows_service_pack_level AS os_service_pack,
    wni.windows_sku AS os_sku,
    osi.cpu_count AS logical_cpu_count,
    osi.hyperthread_ratio AS hyperthread_ratio,
    osi.cpu_count / osi.hyperthread_ratio AS physical_cpu_count,
    CAST(osi.physical_memory_kb / 1024.0 / 1024 AS DECIMAL(10,2)) AS physical_memory_gb,
    CAST(osi.committed_kb / 1024.0 AS DECIMAL(12,2)) AS sql_committed_mb,
    osi.sqlserver_start_time AS sql_start_time,
    DATEDIFF(DAY, osi.sqlserver_start_time, GETDATE()) AS uptime_days,
    DATEDIFF(HOUR, osi.sqlserver_start_time, GETDATE()) % 24 AS uptime_hours_remainder
FROM sys.dm_os_sys_info AS osi
CROSS JOIN sys.dm_os_windows_info AS wni;

Everything comes from two DMVs joined with no filters, sys.dm_os_sys_info for hardware and uptime, sys.dm_os_windows_info for the OS details, both return exactly one row.

---

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Get OS, hardware, and uptime in one row:
.\run.ps1 Get-OsAndHardwareInfo

# To run against a remote sql server:
.\run.ps1 Get-OsAndHardwareInfo -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/inventory/Get-OsAndHardwareInfo.sql
• powershell/wrappers/inventory/Get-OsAndHardwareInfo.ps1

---

Example Output

machine_name	sql_edition	sql_version	os_release	logical_cpu_count	physical_cpu_count	physical_memory_gb	uptime_days
PWSQL01	Enterprise Developer Edition (64-bit)	17.0.4045.5	10.0	8	8	7.78	4

---

Understanding the Results

• logical_cpu_count vs physical_cpu_count — equal on this lab instance (no hyperthreading in effect), a real gap between the two means hyperthreading is active, relevant for licensing and for comparing raw compute against another server
• physical_memory_gb — the real hardware ceiling. Compare against configured max server memory to see how much headroom (or lack of it) SQL Server has been given
• sql_committed_mb — how much SQL Server is actually using right now, on a small box like this one it can be a meaningful fraction of total physical memory, worth watching alongside Recent Error Log Entries if memory pressure is suspected
• uptime_days — a recent restart explains a lot: an empty plan cache, a cold buffer pool, wait statistics that look unusually low simply because they haven’t accumulated yet

---

Related Scripts

You may also find these scripts useful:

• Server Inventory (hub)
• Version and Edition
• Patch Level
• Instance Configuration Snapshot
• Database Snapshot Inventory
• Login and Job Inventory
• Database Inventory
• Database Summary
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Hardware and OS context is the baseline every other diagnostic assumes, memory settings, CPU-related waits, and uptime-sensitive findings all need to be read against these numbers, not in isolation.

Run this first when reviewing any unfamiliar server, and keep it in mind whenever uptime is short enough that other diagnostics might just be reflecting a cold start rather than a real trend.