SQL Configuration Values

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

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

Microsoft Help