{"id":275,"date":"2012-07-19T15:22:57","date_gmt":"2012-07-19T21:22:57","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=275"},"modified":"2015-12-28T16:40:16","modified_gmt":"2015-12-28T22:40:16","slug":"quick-server-info-sys-dm_os_sys_info-and-serverproperty","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=275","title":{"rendered":"Quick Server Info – sys.dm_os_sys_info and SERVERPROPERTY ()"},"content":{"rendered":"

I needed a quick query to get some server information using\u00a0 sys.dm_os_sys_info<\/a>\u00a0and SERVERPROPERTY ( propertyname )<\/a>\u00a0.\u00a0 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.<\/p>\n

The SQLOS Team <\/a>\u00a0has a neat article about\u00a0changes in 2012 for the DMV.<\/p>\n


\nSELECT\u00a0\u00a0SERVERPROPERTY('ServerName') [ServerName]
\n,SERVERPROPERTY('ComputerNamePhysicalNetBIOS')[Node]
\n,SERVERPROPERTY('Edition')[Edition]
\n,SERVERPROPERTY('ProductVersion')[Version]
\n,cpu_count
\n,hyperthread_ratio
\n,CEILING(CAST(physical_memory_in_bytes\/ 1073741824.0E AS DECIMAL (10,2)))[Physical Memory]
\n,CASE WHEN SERVERPROPERTY('IsClustered')=1 Then 'yes' else'no' END [Cluster]
\nFROM Sys.dm_os_sys_info;
\n<\/code>
\nResults:<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

More OS fun for SQL 2008 R2 servers.
\n
\nUSE [master]
\nGO
\n-- Registry settings
\nSELECT * FROM sys.dm_server_registry;
\nGO
\n-- SQL Services information
\nSELECT * FROM sys.dm_server_services;
\nGO
\n--Windows OS information
\nSELECT * FROM sys.dm_os_windows_info
\nGO
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

I needed a quick query to get some server information using\u00a0 sys.dm_os_sys_info\u00a0and SERVERPROPERTY ( propertyname )\u00a0.\u00a0 It is budget time so folks want to know some specifics about some of the current infrastructure. This is a nice way to provide …<\/p>\n

Read more »<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[273,266,22,351,5],"tags":[263,284,36,295,52,283,354,352,353,291],"class_list":["post-275","post","type-post","status-publish","format-standard","hentry","category-administration","category-dba","category-sql-server","category-ssms","category-t-sql","tag-dmv","tag-serverproperty","tag-sql","tag-sql-server","tag-ssms","tag-sys-dm_os_sys_info","tag-sys-dm_os_windows_info","tag-sys-dm_server_registry","tag-sys-dm_server_services","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/275","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=275"}],"version-history":[{"count":12,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/275\/revisions"}],"predecessor-version":[{"id":522,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/275\/revisions\/522"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=275"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=275"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=275"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}