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

One row, describing the whole instance: what it is running on, what hardware is underneath it, and how long it has been up.

Understanding the Results

Microsoft’s reference covers sys.dm_os_sys_info in full.

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: Get CPU Topology and OS Configuration
• 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.