Query #3 is Server Properties. This query simply makes multiple calls to the T-SQL SERVERPROPERTY metadata function to retrieve quite a bit of useful information about the current instance of SQL Server that you are connected to. Query #3 is shown in Figure 1.
Figure 1: Query #3 Server Properties
This metadata function was recently enhanced for SQL Server 2012 and SQL Server 2014 by adding quite a bit of information about the product level, product update level, build numbers, etc. Aaron Bertrand went into much more detail about this change here. One challenge with this T-SQL metadata function is that many, but not all of the possible valid values for input values are documented in Books Online (BOL). One example is SERVERPROPERTY(‘InstanceDefaultDataPath’) and SERVERPROPERTY(‘InstanceDefaultLogPath’), which I just recently discovered.
Query #4 is Configuration Values. This query retrieves information from the sys.configurations catalog view. It will return a different number of rows depending on which version of SQL Server you are using. For example, SQL Server 2014 SP1 CU4 returns 70 rows, while SQL Server 2016 CTP 3.2 returns 76 rows. Query #4 is shown in Figure 2.
Figure 2: Query #4 Configuration Values
Even though there are many rows that are returned from this view, I usually focus on just a small number of them, as detailed in the comments below the query. If you find any of these instance-level configuration values that you would like to change, you have two ways of doing it (at least for many of the values). One way is to use the SQL Server Management Studio (SSMS) GUI to change the value, and then either click Ok or script out the change (which is always a much better idea). The other way is to use the sp_configure system stored procedure.
What you want to set these values to depends on your workload and a number of other factors. One cautionary piece of verbiage I recently spotted in BOL was this:
“Advanced options should be changed only by an experienced database administrator or certified SQL Server technician.”
I would agree that you should probably not change these configuration values unless you know what you are doing, but I am not sure what they really mean about a certified SQL Server technician…