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.
1: -- Get selected server properties (Query 3) (Server Properties)
2: SELECT SERVERPROPERTY('MachineName') AS [MachineName],
3: SERVERPROPERTY('ServerName') AS [ServerName],
4: SERVERPROPERTY('InstanceName') AS [Instance],
5: SERVERPROPERTY('IsClustered') AS [IsClustered],
6: SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS],
7: SERVERPROPERTY('Edition') AS [Edition],
8: SERVERPROPERTY('ProductLevel') AS [ProductLevel], -- What servicing branch (RTM/SP/CU)
9: SERVERPROPERTY('ProductUpdateLevel') AS [ProductUpdateLevel], -- Within a servicing branch, what CU# is applied
10: SERVERPROPERTY('ProductVersion') AS [ProductVersion],
11: SERVERPROPERTY('ProductMajorVersion') AS [ProductMajorVersion],
12: SERVERPROPERTY('ProductMinorVersion') AS [ProductMinorVersion],
13: SERVERPROPERTY('ProductBuild') AS [ProductBuild],
14: SERVERPROPERTY('ProductBuildType') AS [ProductBuildType], -- Is this a GDR or OD hotfix (NULL if on a CU build)
15: SERVERPROPERTY('ProductUpdateReference') AS [ProductUpdateReference], -- KB article number that is applicable for this build
16: SERVERPROPERTY('ProcessID') AS [ProcessID],
17: SERVERPROPERTY('Collation') AS [Collation],
18: SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
19: SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly],
20: SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
21: SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled],
22: SERVERPROPERTY('HadrManagerStatus') AS [HadrManagerStatus],
23: SERVERPROPERTY('IsXTPSupported') AS [IsXTPSupported],
24: SERVERPROPERTY('IsPolybaseInstalled') AS [IsPolybaseInstalled], -- New for SQL Server 2016
25: SERVERPROPERTY('InstanceDefaultDataPath') AS [InstanceDefaultDataPath],
26: SERVERPROPERTY('InstanceDefaultLogPath') AS [InstanceDefaultLogPath],
27: SERVERPROPERTY('BuildClrVersion') AS [Build CLR Version];
28:
29: -- This gives you a lot of useful information about your instance of SQL Server,
30: -- such as the ProcessID for SQL Server and your collation
31: -- Note: Some columns will be NULL on older SQL Server builds
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.
1: -- Get instance-level configuration values for instance (Query 4) (Configuration Values)
2: SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced
3: FROM sys.configurations WITH (NOLOCK)
4: ORDER BY name OPTION (RECOMPILE);
5:
6: -- Focus on these settings:
7: -- automatic soft-NUMA disabled (should be 0 in most cases)
8: -- backup checksum default (should be 1)
9: -- backup compression default (should be 1 in most cases)
10: -- clr enabled (only enable if it is needed)
11: -- cost threshold for parallelism (depends on your workload)
12: -- lightweight pooling (should be zero)
13: -- max degree of parallelism (depends on your workload and hardware)
14: -- max server memory (MB) (set to an appropriate value, not the default)
15: -- optimize for ad hoc workloads (should be 1)
16: -- priority boost (should be zero)
17: -- remote admin connections (should be 1)
18:
19: -- New options for SQL Server 2016
20: -- hadoop connectivity
21: -- polybase network encryption
22: -- remote data archive (to enable Stretch Databases)
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…