Host and Hardware
Overview
The facts written down at the start of every health check (what processor, how many sockets and cores, how much memory and how much of it SQL Server may take, which Windows, physical or virtual, which power plan, which service accounts) are scattered across several pages, and some are not shown anywhere else.
The Host and Hardware report reads them in one pass and shows them on three cards:
- CPU: processor model, sockets, physical cores, logical processors, hyperthreading, NUMA nodes, schedulers online, max degree of parallelism and cost threshold for parallelism,
- Memory: physical memory, max and min server memory, the percent of RAM allotted, lock pages in memory, the memory model and SQL Server’s memory in use,
- OS and Host: host name, operating system, architecture, OS language, virtual machine, power plan, when SQL Server started, edition, instant file initialization and one row per service with its account.
Each row has a status (OK, Warning, Problem, Info or Unavailable) and a one line reason. The same rows are in the grid below, and the page can be copied as Markdown or saved as HTML for a write up.
Where to find it
| Route | How |
|---|---|
| Server tree | Right-click the server → Instance Level Reports → Host and Hardware |
| Instance reports navigator | Configuration group |
| Related Links bar | From Failover Cluster, Windows Event Log, License and Edition Footprint and Parallelism Calibration |
| Failover Cluster | The Host and Hardware toolbar button |
| Report arrows | Previous is File Utilization, next is I/O by Database |
The toolbar
| Control | What it does |
|---|---|
| Refresh | Reads the instance again. Disabled while a read is running |
| Copy as Markdown | Copies every row as a Markdown table (Card, Item, Value, Status, Notes), with the time it was read |
| Export HTML | Saves the three cards as a web page of their own, then opens it. The file name defaults to “Host and Hardware – <server> – <date>.html” in Documents |
| Parallelism Calibration | Opens Parallelism Calibration for this instance |
| Memory | Opens the Memory report for this instance |
| Cancel | Stops a read in progress (shown while it runs). What was already on the page stays |
The read runs in the background with the progress bar, so the rest of the program stays usable.
Reading the page
The heading names the instance and the host it runs on, when it was read (server time) and, when your login is not sysadmin, that the registry rows need sysadmin. Under it a summary counts the problems, warnings and unavailable facts, for example “1 problem, 3 warnings; 2 facts unavailable.”
The cards show each row with a colored dot: green OK, amber Warning, red Problem, gray Info, light gray Unavailable. The value is in bold with the reason under it. Hover a row for its full reason and the report it links to; click it to select it in the grid. The cards scroll when they need more room than the page gives them; drag the bar under them to make them taller, and the height is remembered.
The grid lists Card, Item, Value, Status and Notes. The status is colored, and the value too for a Warning or Problem. Rows for schedulers, max degree of parallelism, cost threshold for parallelism, max server memory and percent of RAM allotted link to the report that fixes them: double-click the row, or right-click and choose Open <report>. The right-click menu also has Copy as Markdown, Copy Chart to Clipboard and Save Chart as PNG…, and the grid exports to CSV and Excel like every other grid.
The CPU card
| Row | Status rules |
|---|---|
| Processor | The model name from the registry. Unavailable without sysadmin or on Linux |
| Sockets, Physical cores, Logical processors | Info. Sockets and cores are reported from SQL Server 2016 SP2; before that they are Unknown |
| Hyperthreading | On with the threads per core when there are more logical processors than cores; Off or not exposed otherwise (on a virtual machine the host’s hyperthreading is not visible). When cores are not reported, the hyperthread ratio (logical processors per socket) is shown instead |
| NUMA nodes | Info, with whether soft-NUMA is on (automatic) or configured manually |
| Schedulers online | OK when every visible scheduler is online. Problem when any are offline, with the likely reason (see below). Links to SQL CPU Schedulers |
| Max degree of parallelism | Warning when it is 0 and a NUMA node has more than 8 logical processors (or, without NUMA information, the instance has more than 8), or when it is higher than the logical processors in one NUMA node. Info when it is 1 (parallelism off). Otherwise OK. Links to Parallelism Calibration |
| Cost threshold for parallelism | Warning at the default of 5 (25 to 50 is a common starting point), otherwise OK. Links to Parallelism Calibration |
Why schedulers are offline. If an affinity mask is set, it takes them offline on purpose. Otherwise the page names the edition’s limit: Express uses the lesser of 1 socket or 4 cores, Web the lesser of 4 sockets or 16 cores, Standard the lesser of 4 sockets or 16 cores (24 from SQL Server 2016, 32 from 2025), and Enterprise under Server+CAL licensing 20 cores per instance. On a virtual machine it adds that the vCPUs should be presented as fewer sockets with more cores each.
The Memory card
| Row | Status rules |
|---|---|
| Physical memory | Info |
| Max server memory | Warning when not set (the default) or at or above physical memory. OK otherwise, with how much it leaves for Windows. Links to Memory |
| Min server memory | Info; the note says when it equals max server memory, so SQL Server will not give memory back to Windows under pressure |
| Percent of RAM allotted | Max server memory as a percent of physical memory. Warning when uncapped or above 90% (4 GB or 10%, whichever is more, is a common floor for Windows). Info under 50%. Otherwise OK. Links to Memory |
| Lock pages in memory | OK when enabled (memory model LOCK_PAGES or LARGE_PAGES, or locked page allocations in use on builds without the memory model). Info when not enabled, with how to grant it. Not applicable on Linux |
| Memory model | CONVENTIONAL, LOCK_PAGES or LARGE_PAGES; reported from SQL Server 2016 SP1 |
| SQL Server memory in use | The process’s physical memory in use, and its percent of physical memory |
The OS and Host card
| Row | Status rules |
|---|---|
| Host name | The physical computer name. On a failover cluster instance, the node that owns it now |
| Operating system | From sys.dm_os_host_info (SQL Server 2017 and later, Windows and Linux) or sys.dm_os_windows_info before that. Warning for Windows before release 10.0 (Windows Server 2012 R2 and earlier are out of extended support) |
| Architecture, OS language | Info, when the instance reports them |
| Virtual machine | No, physical; Yes, hypervisor (CPU ready time, reservations and ballooning on the host are not visible from the guest); or Container |
| Power plan | OK for High performance or Ultimate performance. Warning for Balanced. Problem for Power saver. Info for a custom plan (check its minimum processor state). Not shown on Linux; Unavailable without sysadmin |
| SQL Server started | The start time and the uptime |
| Edition | The edition, version, build, service pack and cumulative update |
| Instant file initialization | OK when enabled. Warning when disabled (grant Perform volume maintenance tasks to the service account). Reported from SQL Server 2016 SP1 |
| One row per service | The service account. Warning when the database engine, or the Agent (except on Express), is not running or not set to start automatically, or when any service runs as LocalSystem |
Permissions and versions
- There is no version or permission gate on the menu. Every part of the read is caught on its own, so a login gets every fact it may see and the rest are marked Unavailable with the reason.
- The server properties and
sys.configurationsneed no permission beyond connecting. sys.dm_os_sys_info,sys.dm_os_host_info,sys.dm_os_windows_info,sys.dm_os_process_memory,sys.dm_os_schedulers,sys.dm_os_nodesandsys.dm_server_servicesneed VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later).- The processor model and power plan are read from the Windows registry with
xp_instance_regreadandxp_regread, which need sysadmin. Without it they are not asked for. On Linux there is no registry, so they are not read. - Columns that only newer builds have are read only when the instance has them, so an older version shows Unknown for those rows rather than failing.
sys.dm_server_servicesneeds SQL Server 2008 R2 SP1 or later. - The read times out after 60 seconds; if it fails outright (a lost connection or a timeout), the page says what the server said.