Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()
I needed a quick query to get some server information using sys.dm_os_sys_info and SERVERPROPERTY ( propertyname ) . It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide a high level look at you infrastructure. Tested on SQL 2005 and SQL 2008 R2.
The SQLOS Team has a neat article about changes in 2012 for the DMV.
SELECT SERVERPROPERTY('ServerName') [ServerName]
,SERVERPROPERTY('ComputerNamePhysicalNetBIOS')[Node]
,SERVERPROPERTY('Edition')[Edition]
,SERVERPROPERTY('ProductVersion')[Version]
,cpu_count
,hyperthread_ratio
,CEILING(CAST(physical_memory_in_bytes/ 1073741824.0E AS DECIMAL (10,2)))[Physical Memory]
,CASE WHEN SERVERPROPERTY('IsClustered')=1 Then 'yes' else'no' END [Cluster]
FROM Sys.dm_os_sys_info;
Results:
More OS fun for SQL 2008 R2 servers.
USE [master]
GO
-- Registry settings
SELECT * FROM sys.dm_server_registry;
GO
-- SQL Services information
SELECT * FROM sys.dm_server_services;
GO
--Windows OS information
SELECT * FROM sys.dm_os_windows_info
GO