SQL Server Query: Querying the Instance and OS Level Details

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 that shows 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.

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.

declare @version varchar(4)
select @version = substring(@@version,22,4)

IF CONVERT(SMALLINT, @version) >= 2012
EXEC ('SELECT   
        SERVERPROPERTY(''ServerName'') AS [Instance Name],
        CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4) 
            WHEN ''11.0'' THEN ''SQL Server 2012''
            WHEN ''12.0'' THEN ''SQL Server 2014''
            ELSE ''Newer than SQL Server 2014''
        END AS [Version Build],
        SERVERPROPERTY (''Edition'') AS [Edition],
        SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
        CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'') 
            WHEN 0 THEN ''SQL Server and Windows Authentication mode''
            WHEN 1 THEN ''Windows Authentication mode''
        END AS [Server Authentication],
        CASE SERVERPROPERTY(''IsClustered'') 
            WHEN 0 THEN ''False''
            WHEN 1 THEN ''True''
        END AS [Is Clustered?],
        SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
        SERVERPROPERTY(''Collation'') AS [ SQL Collation],
        [cpu_count] AS [CPUs],
        [physical_memory_kb]/1024 AS [RAM (MB)]
    FROM    
        [sys].[dm_os_sys_info]')
ELSE IF CONVERT(SMALLINT, @version) >= 2005
EXEC ('SELECT   
        SERVERPROPERTY(''ServerName'') AS [Instance Name],
        CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4) 
            WHEN ''9.00'' THEN ''SQL Server 2005''
            WHEN ''10.0'' THEN ''SQL Server 2008''
            WHEN ''10.5'' THEN ''SQL Server 2008 R2''
        END AS [Version Build],
        SERVERPROPERTY (''Edition'') AS [Edition],
        SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
        CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'') 
            WHEN 0 THEN ''SQL Server and Windows Authentication mode''
            WHEN 1 THEN ''Windows Authentication mode''
        END AS [Server Authentication],
        CASE SERVERPROPERTY(''IsClustered'') 
            WHEN 0 THEN ''False''
            WHEN 1 THEN ''True''
        END AS [Is Clustered?],
        SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
        SERVERPROPERTY(''Collation'') AS [ SQL Collation],
        [cpu_count] AS [CPUs],
        [physical_memory_in_bytes]/1048576 AS [RAM (MB)]
    FROM    
        [sys].[dm_os_sys_info]')
ELSE 
SELECT 'This SQL Server instance is running SQL Server 2000 or lower! You will need alternative methods in getting the SQL Server instance level information.'

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

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.

Introduction to SQL Server Instance

A SQL Server instance is a single installation of the SQL Server database engine, serving as the core component of the SQL Server database system. Each instance can host its own set of databases, security settings, and configuration options, making it a versatile and powerful tool for managing data. SQL Server instances can be managed using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) commands, providing flexibility in how you interact with your databases.

Understanding SQL Server instances is crucial for optimizing database performance. These instances can host multiple databases, including those used for sales, employee management, and customer relationship management. They are responsible for managing database transactions, such as insert, update, and delete operations, ensuring data integrity and consistency.

SQL Server instances can be configured to use various storage options, including local disks, network-attached storage (NAS), and storage area networks (SANs). This flexibility allows you to tailor your storage solutions to meet the specific needs of your organization. By effectively managing SQL Server instances, you can create a robust foundation for your databases, ensuring they perform efficiently and reliably.

Understanding SQL Queries

SQL queries are the backbone of database management, used to retrieve and manipulate data within a database. SQL stands for Structured Query Language, the standard language for accessing and managing relational databases. With SQL queries, you can perform a wide range of operations, including selecting data, inserting data, updating data, and deleting data.

The SELECT statement is a fundamental component of SQL queries, allowing you to retrieve data from one or more tables. SQL queries can also retrieve data from multiple tables using inner joins, outer joins, and other join types, enabling you to combine and analyze data from different sources. Aggregate functions, such as SUM and AVG, are used in SQL queries to perform calculations on data, providing valuable insights into your datasets.

Optimizing SQL queries is essential for improving database performance. Indexes can significantly enhance query performance by providing a quick way to locate specific data in a table. Understanding SQL queries and their optimization is crucial for working with databases, including those used for sales, customer relationship management, and employee management. By mastering SQL queries, you can efficiently manage and analyze your data, driving better decision-making and operational efficiency.

Query Optimization

Query optimization is the process of improving the performance of SQL queries to retrieve data more quickly and efficiently. One of the primary methods for optimizing SQL queries is the use of indexes, which provide a quick way to locate specific data in a table. By indexing the columns frequently used in queries, you can significantly reduce the time it takes to retrieve data.

The SELECT statement can be optimized by using indexes and by reducing the amount of data retrieved. For example, selecting only the necessary columns and using WHERE clauses to filter data can improve query performance. Aggregate functions, such as SUM and AVG, can also be used to perform calculations on data, reducing the need to retrieve and process large datasets.

The GROUP BY clause is another powerful tool for query optimization, allowing you to group data based on one or more columns. This can be particularly useful for generating summary reports and performing data analysis. The HAVING clause can further optimize queries by filtering grouped data based on specific conditions.

SQL Server provides several tools for optimizing queries, including the Database Engine Tuning Advisor and the Query Store. These tools can help you identify performance bottlenecks and recommend optimizations to improve query performance. By focusing on query optimization, you can enhance the performance of your databases, ensuring they run efficiently and effectively.

Best Practices

Following best practices for working with SQL Server is essential for managing and optimizing databases effectively. One key best practice is to use indexes to optimize SQL queries and improve query performance. Indexes provide a quick way to locate specific data in a table, reducing the time it takes to retrieve data.

Another best practice is to use aggregate functions, such as SUM and AVG, to perform calculations on data. These functions can help you generate summary reports and analyze data more efficiently. The use of inner joins and outer joins can also improve query performance by combining data from multiple tables, reducing the need for complex queries.

SQL Server provides several tools for managing and optimizing databases, including SQL Server Management Studio (SSMS) and the sqlcmd command-line tool. These tools can help you manage your databases more effectively, ensuring they run smoothly and efficiently. Regularly backing up databases and testing database recovery procedures is another critical best practice, ensuring you can recover your data in the event of a failure.

Understanding SQL queries and query optimization is essential for following best practices and improving the performance of databases. By adhering to these best practices, database administrators can enhance the performance and reliability of their databases, including those used for sales, customer relationship management, and employee management.