SQL Configuration Values

When working with SQL Server, understanding its configuration settings is crucial for optimal performance and troubleshooting. The sys.configurations system catalog view provides a wealth of information about all the server-wide configuration options. Here's a quick way to view all of them:

SELECT name
 ,value
 ,value_in_use
 ,minimum
 ,maximum
 ,[description]
 ,is_dynamic
 ,is_advanced
FROM sys.configurations WITH (NOLOCK)
ORDER BY name
OPTION (RECOMPILE);

This query gives you a comprehensive list, including the name of each configuration, its current value, the value actually in use, its minimum and maximum possible settings, a description, and whether it's dynamic (can be changed without restarting the server) or advanced (should only be changed by experienced administrators).

While reviewing all configurations is helpful, you'll often want to check specific settings that are critical for performance or common troubleshooting scenarios. Here are some examples of important configurations and their typical recommended values:

select * from  sys.configurations WITH (NOLOCK) where name like 'automatic soft-NUMA%'  --should be 0 in most cases
select * from  sys.configurations WITH (NOLOCK) where name like 'backup checksum%'  --should be 1
select * from  sys.configurations WITH (NOLOCK) where name like 'backup compression default%'  --should be 1 in most cases
select * from  sys.configurations WITH (NOLOCK) where name like 'clr enabled%'  --only enable if it is needed
select * from  sys.configurations WITH (NOLOCK) where name like 'cost threshold for parallelism%'  --(depends on your workload)
select * from  sys.configurations WITH (NOLOCK) where name like 'lightweight%'  --should be zero
select * from  sys.configurations WITH (NOLOCK) where name like 'max degree of parallelism%'  --depends on your workload and hardware
select * from  sys.configurations WITH (NOLOCK) where name like 'max server memory%'  --set to an appropriate value, not the default
select * from  sys.configurations WITH (NOLOCK) where name like 'optimize for ad hoc workloads'  --should be 1
select * from  sys.configurations WITH (NOLOCK) where name like 'priority boost%'  --should be zero
select * from  sys.configurations WITH (NOLOCK) where name like 'remote admin connections%'  --should be 1
select * from  sys.configurations WITH (NOLOCK) where name like 'tempdb metadata memory-optimized%'  --0 by default, some workloads may benefit by enabling

Remember that the "best" setting for some of these options, like cost threshold for parallelism and max degree of parallelism, can vary significantly depending on your specific workload and server hardware. Always test changes in a development environment before applying them to production.

For more in-depth details on each configuration option, you can always refer to the official Microsoft documentation for sys.configurations.