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

Share the joy
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Tiny URL for this post:
 

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

WordPress Anti Spam by WP-SpamShield