Querying the SQL Server Instance and OS Level Details

By | In Database Administration, SQL Server | November 10th, 2015

Let’s say you’re part of SQL Server DBA team that supports 100+ instances of SQL Server ranging from SQL Server 2005 through SQL Server 2014 with different editions, service packs, and authentication modes. You have been tasked with the critical mission to develop an Excel report to show all the instance level details such as instance name, version, edition, service pack, server authentication mode, if it’s clustered, the current node name, server collation, # of CPUs, and memory of the Operating System. These are the similar details you will see on the “Server Properties” of the instance.

Querying 1

What do you need to know to get the required report completed quickly? The solution and the required queries are simpler than you might think but there’s only one caveat.

Here are the required system functions, expression, and Dynamic Management View (DMV) that you will need and brief explanations of each:

  • @@VERSION: Returns system and build information for the instance of SQL Server.
  • SUBSTRING (): Returns part of a character or text. In this case, the @@VERSION function will return the SQL Server version in numeric form starting on the 22nd character, we will use the SUBSTRING () function to capture the next 4 characters starting on the 22nd character. It will show 2005, 2008, 2010, 2012, 2014, and so on.
  • SERVERPROPERTY (): Queries the property information about the server instance. In this case, we will use this system built-in function to query the instance name, product version, edition, product level, authentication mode, if it is clustered, the current node name, and SQL Server collation.
  • CASE EXPRESSION: Evaluates a list of conditions and returns one of the possible result expressions. In this case, we will use the simple CASE expression.
  • SYS.DM_OS_SYS_INFO: Returns a set of information about the computer and the resources available to the SQL Server instance(s) on the Windows Server.

What is the one caveat that we need to be aware of? It is on the SYS.DM_OS_SYS_INFO DMV and more specifically on the memory data column. In SQL Server 2005, 2008, and 2008 R2, the memory is returned in bytes. The data column name is physical_memory_in_bytes. But, in SQL Server 2012 and later, the memory is returned in kilobytes. The data column name is physical_memory_kb. The different data column names for memory for the SYS.DM_OS_SYS_INFO DMV has been taken into consideration in the following script and will dynamically return details for the instances running below SQL Server 2012 and also for the instances running SQL Server 2012 or above.

The following is an example of the output after executing on one of our demo SQL Server instances:

Querying 2

If you and your team need to query more than 100+ SQL Server instances, you will need to register all the instances into a group and then run a multi-server query using the script provided above.

This is probably the tip of the iceberg on what we can do on querying the SQL Server instance and Windows Server level details. Do you need more additional details from the instances or the Windows Servers? Do you have some unique scenarios or amazing scripts that you wish to share with our fellow SQL Server DBAs? Please feel free to leave a reply on the comment box below.

Contact Us
JP Chen
Practice Leader of SQL Server Services
JP has over 13 years of experience in the IT industry specializing in web development, data analysis, and database administration. He has worked on supporting enterprise level corporations in the financial, retailing, pharmaceutical, aerospace, fashion, and education industries as a DBA and Tech Lead. He has initiated, designed, and developed comprehensive solutions on database documentation, monitoring and alerting, stabilization, and continuous improvements with performance monitoring and tuning. He enjoys sharing his new findings, knowledge, and practical hands-on experiences on SQL Server via his two blogs: www.handsonsqlserver.com and www.handsontsql.com.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).