Master of None

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


Tiny URL for this post:
 Copy